From ca0a171be99bbec31575a8693331c3d10eed5066 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Mon, 13 Feb 2017 09:52:29 -0500 Subject: [PATCH 2/6] Add PUBLICATION privilege Adding a table to a publication requires the PUBLICATION privilege on the table. Before, the creator of the publication also had to be the table owner, which was less flexible. --- doc/src/sgml/logical-replication.sgml | 7 ++++++ doc/src/sgml/ref/create_publication.sgml | 7 +++++- doc/src/sgml/ref/grant.sgml | 15 ++++++++++-- src/backend/catalog/aclchk.c | 4 ++++ src/backend/commands/publicationcmds.c | 10 ++++---- src/backend/utils/adt/acl.c | 9 +++++++ src/bin/pg_dump/dumputils.c | 2 ++ src/bin/psql/tab-complete.c | 11 +++++---- src/include/nodes/parsenodes.h | 3 ++- src/include/utils/acl.h | 5 ++-- src/test/regress/expected/dependency.out | 22 ++++++++--------- src/test/regress/expected/privileges.out | 40 +++++++++++++++---------------- src/test/regress/expected/publication.out | 17 +++++++++++++ src/test/regress/expected/rowsecurity.out | 34 +++++++++++++------------- src/test/regress/sql/dependency.sql | 2 +- src/test/regress/sql/publication.sql | 21 ++++++++++++++++ 16 files changed, 145 insertions(+), 64 deletions(-) diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 7b351f2727..4889f3e591 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -308,6 +308,13 @@ Security + To add tables to a publication, the user must have + the PUBLICATION privilege on the table. To create a + publication that publishes all tables automatically, the user must be a + superuser. + + + To create a subscription, the user must be a superuser. diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml index 995f2bcf3c..c7691dedae 100644 --- a/doc/src/sgml/ref/create_publication.sgml +++ b/doc/src/sgml/ref/create_publication.sgml @@ -70,6 +70,11 @@ Parameters Specifies a list of tables to add to the publication. + + + Adding a table to a publication requires + the PUBLICATION privilege on the table. + @@ -144,7 +149,7 @@ Notes To add a table to a publication, the invoking user must have - SELECT privilege on given table. The + PUBLICATION privilege on given table. The FOR ALL TABLES clause requires superuser. diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index d8ca39f869..6b0fbb1ff4 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -21,7 +21,7 @@ -GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | PUBLICATION } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } @@ -276,6 +276,16 @@ GRANT on Database Objects + PUBLICATION + + + Allows the use of the specified table in a publication. (See the + statement.) + + + + + CREATE @@ -531,12 +541,13 @@ Notes D -- TRUNCATE x -- REFERENCES t -- TRIGGER + p -- PUBLICATION X -- EXECUTE U -- USAGE C -- CREATE c -- CONNECT T -- TEMPORARY - arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects) + arwdDxtp -- ALL PRIVILEGES (for tables, varies for other objects) * -- grant option for preceding privilege /yyyy -- role that granted this privilege diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 79b7fd5370..d8579e6a55 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3223,6 +3223,8 @@ string_to_privilege(const char *privname) return ACL_CREATE_TEMP; if (strcmp(privname, "connect") == 0) return ACL_CONNECT; + if (strcmp(privname, "publication") == 0) + return ACL_PUBLICATION; if (strcmp(privname, "rule") == 0) return 0; /* ignore old RULE privileges */ ereport(ERROR, @@ -3260,6 +3262,8 @@ privilege_to_string(AclMode privilege) return "TEMP"; case ACL_CONNECT: return "CONNECT"; + case ACL_PUBLICATION: + return "PUBLICATION"; default: elog(ERROR, "unrecognized privilege: %d", (int) privilege); } diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c index 0e4eb0726d..258f3d7ae5 100644 --- a/src/backend/commands/publicationcmds.c +++ b/src/backend/commands/publicationcmds.c @@ -605,12 +605,14 @@ PublicationAddTables(Oid pubid, List *rels, bool if_not_exists, foreach(lc, rels) { Relation rel = (Relation) lfirst(lc); + AclResult aclresult; ObjectAddress obj; - /* Must be owner of the table or superuser. */ - if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId())) - aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS, - RelationGetRelationName(rel)); + aclresult = pg_class_aclcheck(RelationGetRelid(rel), GetUserId(), ACL_PUBLICATION); + if (aclresult != ACLCHECK_OK) + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_CLASS, + RelationGetRelationName(rel)); obj = publication_add_relation(pubid, rel, if_not_exists); if (stmt) diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index 96ac1dfefd..079f9352fe 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -315,6 +315,9 @@ aclparse(const char *s, AclItem *aip) case ACL_CONNECT_CHR: read = ACL_CONNECT; break; + case ACL_PUBLICATION_CHR: + read = ACL_PUBLICATION; + break; case 'R': /* ignore old RULE privileges */ read = 0; break; @@ -1626,6 +1629,8 @@ convert_priv_string(text *priv_type_text) return ACL_CREATE_TEMP; if (pg_strcasecmp(priv_type, "CONNECT") == 0) return ACL_CONNECT; + if (pg_strcasecmp(priv_type, "PUBLICATION") == 0) + return ACL_PUBLICATION; if (pg_strcasecmp(priv_type, "RULE") == 0) return 0; /* ignore old RULE privileges */ @@ -1722,6 +1727,8 @@ convert_aclright_to_string(int aclright) return "TEMPORARY"; case ACL_CONNECT: return "CONNECT"; + case ACL_PUBLICATION: + return "PUBLICATION"; default: elog(ERROR, "unrecognized aclright: %d", aclright); return NULL; @@ -2033,6 +2040,8 @@ convert_table_priv_string(text *priv_type_text) {"REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES)}, {"TRIGGER", ACL_TRIGGER}, {"TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRIGGER)}, + {"PUBLICATION", ACL_PUBLICATION}, + {"PUBLICATION WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_PUBLICATION)}, {"RULE", 0}, /* ignore old RULE privileges */ {"RULE WITH GRANT OPTION", 0}, {NULL, 0} diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index b41f2b9125..75bb7f2c2c 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -502,6 +502,8 @@ do { \ /* table only */ CONVERT_PRIV('a', "INSERT"); CONVERT_PRIV('x', "REFERENCES"); + if (remoteVersion >= 100000) + CONVERT_PRIV('p', "PUBLICATION"); /* rest are not applicable to columns */ if (subname == NULL) { diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 94814c20d0..59519f068a 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -304,9 +304,9 @@ do { \ COMPLETE_WITH_LIST(list); \ } while (0) -#define COMPLETE_WITH_LIST10(s1, s2, s3, s4, s5, s6, s7, s8, s9, s10) \ +#define COMPLETE_WITH_LIST11(s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11) \ do { \ - static const char *const list[] = { s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, NULL }; \ + static const char *const list[] = { s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, NULL }; \ COMPLETE_WITH_LIST(list); \ } while (0) @@ -2641,8 +2641,8 @@ psql_completion(const char *text, int start, int end) * to grantable privileges (can't grant roles) */ if (HeadMatches3("ALTER","DEFAULT","PRIVILEGES")) - COMPLETE_WITH_LIST10("SELECT", "INSERT", "UPDATE", - "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", + COMPLETE_WITH_LIST11("SELECT", "INSERT", "UPDATE", + "DELETE", "TRUNCATE", "REFERENCES", "PUBLICATION", "TRIGGER", "EXECUTE", "USAGE", "ALL"); else COMPLETE_WITH_QUERY(Query_for_list_of_roles @@ -2652,6 +2652,7 @@ psql_completion(const char *text, int start, int end) " UNION SELECT 'DELETE'" " UNION SELECT 'TRUNCATE'" " UNION SELECT 'REFERENCES'" + " UNION SELECT 'PUBLICATION'" " UNION SELECT 'TRIGGER'" " UNION SELECT 'CREATE'" " UNION SELECT 'CONNECT'" @@ -2666,7 +2667,7 @@ psql_completion(const char *text, int start, int end) */ else if (TailMatches2("GRANT|REVOKE", MatchAny)) { - if (TailMatches1("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|ALL")) + if (TailMatches1("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|PUBLICATION|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|ALL")) COMPLETE_WITH_CONST("ON"); else if (TailMatches2("GRANT", MatchAny)) COMPLETE_WITH_CONST("TO"); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 5afc3ebea0..e0e94dd06b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -75,7 +75,8 @@ typedef uint32 AclMode; /* a bitmask of privilege bits */ #define ACL_CREATE (1<<9) /* for namespaces and databases */ #define ACL_CREATE_TEMP (1<<10) /* for databases */ #define ACL_CONNECT (1<<11) /* for databases */ -#define N_ACL_RIGHTS 12 /* 1 plus the last 1< 44); \dp - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------------------+----------+-------+---------------------------------------------+-------------------+-------------------------------------------- - regress_rls_schema | category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | - | | | =arwdDxt/regress_rls_alice | | - regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: + - | | | =arwdDxt/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv + - | | | | | FROM uaccount + - | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+ - | | | | | p2r (RESTRICTIVE): + - | | | | | (u): ((cid <> 44) AND (cid < 50)) + - | | | | | to: regress_rls_dave + - | | | | | p1r (RESTRICTIVE): + - | | | | | (u): (cid <> 44) + - | | | | | to: regress_rls_dave - regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | - | | | =r/regress_rls_alice | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------------------+----------+-------+----------------------------------------------+-------------------+-------------------------------------------- + regress_rls_schema | category | table | regress_rls_alice=arwdDxtp/regress_rls_alice+| | + | | | =arwdDxtp/regress_rls_alice | | + regress_rls_schema | document | table | regress_rls_alice=arwdDxtp/regress_rls_alice+| | p1: + + | | | =arwdDxtp/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv + + | | | | | FROM uaccount + + | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+ + | | | | | p2r (RESTRICTIVE): + + | | | | | (u): ((cid <> 44) AND (cid < 50)) + + | | | | | to: regress_rls_dave + + | | | | | p1r (RESTRICTIVE): + + | | | | | (u): (cid <> 44) + + | | | | | to: regress_rls_dave + regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxtp/regress_rls_alice+| | + | | | =r/regress_rls_alice | | (3 rows) \d document diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql index f5c45e4666..2bdd51532c 100644 --- a/src/test/regress/sql/dependency.sql +++ b/src/test/regress/sql/dependency.sql @@ -21,7 +21,7 @@ CREATE TABLE deptest (f1 serial primary key, f2 text); DROP GROUP regress_dep_group; -- can't drop the user if we revoke the privileges partially -REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user; +REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, PUBLICATION ON deptest FROM regress_dep_user; DROP USER regress_dep_user; -- now we are OK to drop him diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql index 4b22053b13..7b322bb7d9 100644 --- a/src/test/regress/sql/publication.sql +++ b/src/test/regress/sql/publication.sql @@ -69,6 +69,27 @@ CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; \d+ testpub_tbl1 +-- permissions +SET ROLE regress_publication_user2; +CREATE PUBLICATION testpub2; -- fail + +SET ROLE regress_publication_user; +GRANT CREATE ON DATABASE regression TO regress_publication_user2; +SET ROLE regress_publication_user2; +CREATE PUBLICATION testpub2; -- ok + +ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail + +SET ROLE regress_publication_user; +GRANT PUBLICATION ON testpub_tbl1 TO regress_publication_user2; +SET ROLE regress_publication_user2; +ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok + +DROP PUBLICATION testpub2; + +SET ROLE regress_publication_user; +REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; + DROP VIEW testpub_view; DROP TABLE testpub_tbl1; -- 2.11.1