Skip to content

Commit e76db00

Browse files
peterepetere
committed
Add more documentation and tests for publications
Add/correct documentation and add some tests related to how access control around adding tables to publications works.
1 parent b5dd50f commit e76db00

File tree

4 files changed

+51
-5
lines changed

4 files changed

+51
-5
lines changed

‎doc/src/sgml/logical-replication.sgml

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -307,6 +307,12 @@
307307
privilege in the database.
308308
</para>
309309

310+
<para>
311+
To add tables to a publication, the user must have ownership rights on the
312+
table. To create a publication that publishes all tables automatically,
313+
the user must be a superuser.
314+
</para>
315+
310316
<para>
311317
To create a subscription, the user must be a superuser.
312318
</para>

‎doc/src/sgml/ref/create_publication.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -143,9 +143,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
143143
</para>
144144

145145
<para>
146-
To add a table to a publication, the invoking user must have
147-
<command>SELECT</command> privilege on given table. The
148-
<command>FOR ALL TABLES</command> clause requires superuser.
146+
To add a table to a publication, the invoking user must have ownership
147+
rights on the table. The <command>FOR ALL TABLES</command> clause requires
148+
the invoking user to be a superuser.
149149
</para>
150150

151151
<para>

‎src/test/regress/expected/publication.out

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
-- PUBLICATION
33
--
44
CREATE ROLE regress_publication_user LOGIN SUPERUSER;
5+
CREATE ROLE regress_publication_user2;
56
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
67
SET SESSION AUTHORIZATION 'regress_publication_user';
78
CREATE PUBLICATION testpub_default;
@@ -140,6 +141,23 @@ Publications:
140141
"testpib_ins_trunct"
141142
"testpub_fortbl"
142143

144+
-- permissions
145+
SET ROLE regress_publication_user2;
146+
CREATE PUBLICATION testpub2; -- fail
147+
ERROR: permission denied for database regression
148+
SET ROLE regress_publication_user;
149+
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
150+
SET ROLE regress_publication_user2;
151+
CREATE PUBLICATION testpub2; -- ok
152+
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
153+
ERROR: must be owner of relation testpub_tbl1
154+
SET ROLE regress_publication_user;
155+
GRANT regress_publication_user TO regress_publication_user2;
156+
SET ROLE regress_publication_user2;
157+
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok
158+
DROP PUBLICATION testpub2;
159+
SET ROLE regress_publication_user;
160+
REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
143161
DROP VIEW testpub_view;
144162
DROP TABLE testpub_tbl1;
145163
\dRp+ testpub_default
@@ -168,5 +186,5 @@ DROP PUBLICATION testpub_fortbl;
168186
DROP SCHEMA pub_test CASCADE;
169187
NOTICE: drop cascades to table pub_test.testpub_nopk
170188
RESET SESSION AUTHORIZATION;
171-
DROP ROLE regress_publication_user;
189+
DROP ROLE regress_publication_user, regress_publication_user2;
172190
DROP ROLE regress_publication_user_dummy;

‎src/test/regress/sql/publication.sql

Lines changed: 23 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22
-- PUBLICATION
33
--
44
CREATE ROLE regress_publication_user LOGIN SUPERUSER;
5+
CREATE ROLE regress_publication_user2;
56
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
67
SET SESSION AUTHORIZATION 'regress_publication_user';
78

@@ -69,6 +70,27 @@ ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk;
6970

7071
\d+ testpub_tbl1
7172

73+
-- permissions
74+
SET ROLE regress_publication_user2;
75+
CREATE PUBLICATION testpub2; -- fail
76+
77+
SET ROLE regress_publication_user;
78+
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
79+
SET ROLE regress_publication_user2;
80+
CREATE PUBLICATION testpub2; -- ok
81+
82+
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
83+
84+
SET ROLE regress_publication_user;
85+
GRANT regress_publication_user TO regress_publication_user2;
86+
SET ROLE regress_publication_user2;
87+
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok
88+
89+
DROP PUBLICATION testpub2;
90+
91+
SET ROLE regress_publication_user;
92+
REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
93+
7294
DROP VIEW testpub_view;
7395
DROP TABLE testpub_tbl1;
7496

@@ -90,5 +112,5 @@ DROP PUBLICATION testpub_fortbl;
90112
DROP SCHEMA pub_test CASCADE;
91113

92114
RESET SESSION AUTHORIZATION;
93-
DROP ROLE regress_publication_user;
115+
DROP ROLE regress_publication_user, regress_publication_user2;
94116
DROP ROLE regress_publication_user_dummy;

0 commit comments

Comments
 (0)