On Wed, Nov 23, 2022 at 02:56:28PM -0500, Andrew Dunstan wrote: > I have committed the first couple of these to get them out of the way.
Thanks! > But I think we need a bit of cleanup in the next patch. > vacuum_is_relation_owner() looks like it's now rather misnamed. Maybe > vacuum_is_permitted_for_relation()? Also I think we need a more thorough > reworking of the comments around line 566. And I think we need a more > detailed explanation of why the change in vacuum_rel is ok, and if it is > OK we should adjust the head comment on the function. > > In any case I think this comment would be better English with "might" > instead of "may": > > /* user may have the ANALYZE privilege */ I've attempted to address all your feedback in v13. Please let me know if anything needs further reworking. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
>From 09e6cb01a6d89eb952653c9700b0384e3a040f7e Mon Sep 17 00:00:00 2001 From: Nathan Bossart <nathandboss...@gmail.com> Date: Sat, 3 Sep 2022 23:31:38 -0700 Subject: [PATCH v13 1/2] Allow granting VACUUM and ANALYZE privileges on relations. --- doc/src/sgml/ddl.sgml | 49 ++++++++--- doc/src/sgml/func.sgml | 3 +- .../sgml/ref/alter_default_privileges.sgml | 4 +- doc/src/sgml/ref/analyze.sgml | 3 +- doc/src/sgml/ref/grant.sgml | 4 +- doc/src/sgml/ref/revoke.sgml | 2 +- doc/src/sgml/ref/vacuum.sgml | 3 +- src/backend/catalog/aclchk.c | 8 ++ src/backend/commands/analyze.c | 13 ++- src/backend/commands/vacuum.c | 62 ++++++------- src/backend/parser/gram.y | 7 ++ src/backend/utils/adt/acl.c | 16 ++++ src/bin/pg_dump/dumputils.c | 2 + src/bin/pg_dump/t/002_pg_dump.pl | 2 +- src/bin/psql/tab-complete.c | 5 +- src/include/commands/vacuum.h | 4 +- src/include/nodes/parsenodes.h | 4 +- src/include/utils/acl.h | 6 +- src/test/regress/expected/dependency.out | 22 ++--- src/test/regress/expected/privileges.out | 86 ++++++++++++++----- src/test/regress/expected/rowsecurity.out | 34 ++++---- src/test/regress/expected/vacuum.out | 6 ++ src/test/regress/sql/dependency.sql | 2 +- src/test/regress/sql/privileges.sql | 40 +++++++++ 24 files changed, 274 insertions(+), 113 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 03c0193709..ed034a6b1d 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1691,8 +1691,9 @@ ALTER TABLE products RENAME TO items; <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, <literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>, <literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>, - <literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal> - and <literal>ALTER SYSTEM</literal>. + <literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>, + <literal>ALTER SYSTEM</literal>, <literal>VACUUM</literal>, and + <literal>ANALYZE</literal>. The privileges applicable to a particular object vary depending on the object's type (table, function, etc.). More detail about the meanings of these privileges appears below. @@ -1982,7 +1983,25 @@ REVOKE ALL ON accounts FROM PUBLIC; </para> </listitem> </varlistentry> - </variablelist> + + <varlistentry> + <term><literal>VACUUM</literal></term> + <listitem> + <para> + Allows <command>VACUUM</command> on a relation. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ANALYZE</literal></term> + <listitem> + <para> + Allows <command>ANALYZE</command> on a relation. + </para> + </listitem> + </varlistentry> + </variablelist> The privileges required by other commands are listed on the reference page of the respective command. @@ -2131,6 +2150,16 @@ REVOKE ALL ON accounts FROM PUBLIC; <entry><literal>A</literal></entry> <entry><literal>PARAMETER</literal></entry> </row> + <row> + <entry><literal>VACUUM</literal></entry> + <entry><literal>v</literal></entry> + <entry><literal>TABLE</literal></entry> + </row> + <row> + <entry><literal>ANALYZE</literal></entry> + <entry><literal>z</literal></entry> + <entry><literal>TABLE</literal></entry> + </row> </tbody> </tgroup> </table> @@ -2221,7 +2250,7 @@ REVOKE ALL ON accounts FROM PUBLIC; </row> <row> <entry><literal>TABLE</literal> (and table-like objects)</entry> - <entry><literal>arwdDxt</literal></entry> + <entry><literal>arwdDxtvz</literal></entry> <entry>none</entry> <entry><literal>\dp</literal></entry> </row> @@ -2279,12 +2308,12 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; would show: <programlisting> => \dp mytable - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------+-------+-----------------------+-----------------------+---------- - public | mytable | table | miriam=arwdDxt/miriam+| col1: +| - | | | =r/miriam +| miriam_rw=rw/miriam | - | | | admin=arw/miriam | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------+-------+-------------------------+-----------------------+---------- + public | mytable | table | miriam=arwdDxtvz/miriam+| col1: +| + | | | =r/miriam +| miriam_rw=rw/miriam | + | | | admin=arw/miriam | | (1 row) </programlisting> </para> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 82fba48d5f..68cd4297d2 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -22978,7 +22978,8 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); are <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, <literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, - and <literal>TRIGGER</literal>. + <literal>TRIGGER</literal>, <literal>VACUUM</literal> and + <literal>ANALYZE</literal>. </para></entry> </row> diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index f1d54f5aa3..0da295daff 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -28,7 +28,7 @@ ALTER DEFAULT PRIVILEGES <phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase> -GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] @@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ] REVOKE [ GRANT OPTION FOR ] - { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE } [, ...] | ALL [ PRIVILEGES ] } ON TABLES FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 2ba115d1ad..400ea30cd0 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -149,7 +149,8 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea <para> To analyze a table, one must ordinarily be the table's owner or a - superuser. However, database owners are allowed to + superuser or have the <literal>ANALYZE</literal> privilege on the table. + However, database owners are allowed to analyze all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide <command>ANALYZE</command> can only be performed by a superuser.) diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 5ae523f4b3..c3c585be7e 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...] | ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] } @@ -193,6 +193,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace <term><literal>USAGE</literal></term> <term><literal>SET</literal></term> <term><literal>ALTER SYSTEM</literal></term> + <term><literal>VACUUM</literal></term> + <term><literal>ANALYZE</literal></term> <listitem> <para> Specific types of privileges, as defined in <xref linkend="ddl-priv"/>. diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 2db66bbf37..e28d192fd3 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -22,7 +22,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> REVOKE [ GRANT OPTION FOR ] - { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...] | ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] } diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index c582021d29..70c0d81346 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -357,7 +357,8 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet <para> To vacuum a table, one must ordinarily be the table's owner or a - superuser. However, database owners are allowed to + superuser or have the <literal>VACUUM</literal> privilege on the table. + However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide <command>VACUUM</command> can only be performed by a superuser.) diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 3c9f8e60ad..3b5ea3c137 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -3420,6 +3420,10 @@ string_to_privilege(const char *privname) return ACL_SET; if (strcmp(privname, "alter system") == 0) return ACL_ALTER_SYSTEM; + if (strcmp(privname, "vacuum") == 0) + return ACL_VACUUM; + if (strcmp(privname, "analyze") == 0) + return ACL_ANALYZE; if (strcmp(privname, "rule") == 0) return 0; /* ignore old RULE privileges */ ereport(ERROR, @@ -3461,6 +3465,10 @@ privilege_to_string(AclMode privilege) return "SET"; case ACL_ALTER_SYSTEM: return "ALTER SYSTEM"; + case ACL_VACUUM: + return "VACUUM"; + case ACL_ANALYZE: + return "ANALYZE"; default: elog(ERROR, "unrecognized privilege: %d", (int) privilege); } diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c index bf0ec8b374..38bccafa05 100644 --- a/src/backend/commands/analyze.c +++ b/src/backend/commands/analyze.c @@ -159,16 +159,15 @@ analyze_rel(Oid relid, RangeVar *relation, return; /* - * Check if relation needs to be skipped based on ownership. This check + * Check if relation needs to be skipped based on privileges. This check * happens also when building the relation list to analyze for a manual * operation, and needs to be done additionally here as ANALYZE could - * happen across multiple transactions where relation ownership could have - * changed in-between. Make sure to generate only logs for ANALYZE in - * this case. + * happen across multiple transactions where privileges could have changed + * in-between. Make sure to generate only logs for ANALYZE in this case. */ - if (!vacuum_is_relation_owner(RelationGetRelid(onerel), - onerel->rd_rel, - params->options & VACOPT_ANALYZE)) + if (!vacuum_is_permitted_for_relation(RelationGetRelid(onerel), + onerel->rd_rel, + VACOPT_ANALYZE)) { relation_close(onerel, ShareUpdateExclusiveLock); return; diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 15163c80df..a6d5ed1f6b 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -547,32 +547,35 @@ vacuum(List *relations, VacuumParams *params, } /* - * Check if a given relation can be safely vacuumed or analyzed. If the - * user is not the relation owner, issue a WARNING log message and return - * false to let the caller decide what to do with this relation. This - * routine is used to decide if a relation can be processed for VACUUM or - * ANALYZE. + * Check if the current user has privileges to vacuum or analyze the relation. + * If not, issue a WARNING log message and return false to let the caller + * decide what to do with this relation. This routine is used to decide if a + * relation can be processed for VACUUM or ANALYZE. */ bool -vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options) +vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple, + bits32 options) { char *relname; + AclMode mode = 0; Assert((options & (VACOPT_VACUUM | VACOPT_ANALYZE)) != 0); /* - * Check permissions. - * - * We allow the user to vacuum or analyze a table if he is superuser, the - * table owner, or the database owner (but in the latter case, only if - * it's not a shared relation). object_ownercheck includes the - * superuser case. - * - * Note we choose to treat permissions failure as a WARNING and keep - * trying to vacuum or analyze the rest of the DB --- is this appropriate? + * A role has privileges to vacuum or analyze the relation if any of the + * following are true: + * - the role is a superuser + * - the role owns the relation + * - the role owns the current database and the relation is not shared + * - the role has been granted privileges to vacuum/analyze the relation */ + if (options & VACOPT_VACUUM) + mode |= ACL_VACUUM; + if (options & VACOPT_ANALYZE) + mode |= ACL_ANALYZE; if (object_ownercheck(RelationRelationId, relid, GetUserId()) || - (object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared)) + (object_ownercheck(DatabaseRelationId, MyDatabaseId, GetUserId()) && !reltuple->relisshared) || + pg_class_aclcheck(relid, GetUserId(), mode) == ACLCHECK_OK) return true; relname = NameStr(reltuple->relname); @@ -787,10 +790,10 @@ expand_vacuum_rel(VacuumRelation *vrel, int options) classForm = (Form_pg_class) GETSTRUCT(tuple); /* - * Make a returnable VacuumRelation for this rel if user is a proper - * owner. + * Make a returnable VacuumRelation for this rel if the user has the + * required privileges. */ - if (vacuum_is_relation_owner(relid, classForm, options)) + if (vacuum_is_permitted_for_relation(relid, classForm, options)) { oldcontext = MemoryContextSwitchTo(vac_context); vacrels = lappend(vacrels, makeVacuumRelation(vrel->relation, @@ -877,7 +880,7 @@ get_all_vacuum_rels(int options) Oid relid = classForm->oid; /* check permissions of relation */ - if (!vacuum_is_relation_owner(relid, classForm, options)) + if (!vacuum_is_permitted_for_relation(relid, classForm, options)) continue; /* @@ -1797,7 +1800,9 @@ vac_truncate_clog(TransactionId frozenXID, * be stale. * * Returns true if it's okay to proceed with a requested ANALYZE - * operation on this table. + * operation on this table. Note that if vacuuming fails because the user + * does not have the required privileges, this function returns true since + * the user might have been granted privileges to ANALYZE the relation. * * Doing one heap at a time incurs extra overhead, since we need to * check that the heap exists again just before we vacuum it. The @@ -1889,21 +1894,20 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params) } /* - * Check if relation needs to be skipped based on ownership. This check + * Check if relation needs to be skipped based on privileges. This check * happens also when building the relation list to vacuum for a manual * operation, and needs to be done additionally here as VACUUM could - * happen across multiple transactions where relation ownership could have - * changed in-between. Make sure to only generate logs for VACUUM in this - * case. + * happen across multiple transactions where privileges could have changed + * in-between. Make sure to only generate logs for VACUUM in this case. */ - if (!vacuum_is_relation_owner(RelationGetRelid(rel), - rel->rd_rel, - params->options & VACOPT_VACUUM)) + if (!vacuum_is_permitted_for_relation(RelationGetRelid(rel), + rel->rd_rel, + VACOPT_VACUUM)) { relation_close(rel, lmode); PopActiveSnapshot(); CommitTransactionCommand(); - return false; + return true; /* user might have the ANALYZE privilege */ } /* diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 9384214942..b1ae5f834c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -7482,6 +7482,13 @@ privilege: SELECT opt_column_list n->cols = NIL; $$ = n; } + | analyze_keyword + { + AccessPriv *n = makeNode(AccessPriv); + n->priv_name = pstrdup("analyze"); + n->cols = NIL; + $$ = n; + } | ColId opt_column_list { AccessPriv *n = makeNode(AccessPriv); diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index d4d68f9724..8ee2ebadc5 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -321,6 +321,12 @@ aclparse(const char *s, AclItem *aip) case ACL_ALTER_SYSTEM_CHR: read = ACL_ALTER_SYSTEM; break; + case ACL_VACUUM_CHR: + read = ACL_VACUUM; + break; + case ACL_ANALYZE_CHR: + read = ACL_ANALYZE; + break; case 'R': /* ignore old RULE privileges */ read = 0; break; @@ -1595,6 +1601,8 @@ makeaclitem(PG_FUNCTION_ARGS) {"CONNECT", ACL_CONNECT}, {"SET", ACL_SET}, {"ALTER SYSTEM", ACL_ALTER_SYSTEM}, + {"VACUUM", ACL_VACUUM}, + {"ANALYZE", ACL_ANALYZE}, {"RULE", 0}, /* ignore old RULE privileges */ {NULL, 0} }; @@ -1703,6 +1711,10 @@ convert_aclright_to_string(int aclright) return "SET"; case ACL_ALTER_SYSTEM: return "ALTER SYSTEM"; + case ACL_VACUUM: + return "VACUUM"; + case ACL_ANALYZE: + return "ANALYZE"; default: elog(ERROR, "unrecognized aclright: %d", aclright); return NULL; @@ -2012,6 +2024,10 @@ 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)}, + {"VACUUM", ACL_VACUUM}, + {"VACUUM WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_VACUUM)}, + {"ANALYZE", ACL_ANALYZE}, + {"ANALYZE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_ANALYZE)}, {"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 6e501a5413..9311417f18 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -457,6 +457,8 @@ do { \ CONVERT_PRIV('d', "DELETE"); CONVERT_PRIV('t', "TRIGGER"); CONVERT_PRIV('D', "TRUNCATE"); + CONVERT_PRIV('v', "VACUUM"); + CONVERT_PRIV('z', "ANALYZE"); } } diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 8dc1f0eccb..fe53ed0f89 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -566,7 +566,7 @@ my %tests = ( \QREVOKE ALL ON TABLES FROM regress_dump_test_role;\E\n \QALTER DEFAULT PRIVILEGES \E \QFOR ROLE regress_dump_test_role \E - \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLES TO regress_dump_test_role;\E + \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,VACUUM,ANALYZE,UPDATE ON TABLES TO regress_dump_test_role;\E /xm, like => { %full_runs, section_post_data => 1, }, unlike => { no_privs => 1, }, diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 13014f074f..89e7317c23 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1147,7 +1147,7 @@ static const SchemaQuery Query_for_trigger_of_table = { #define Privilege_options_of_grant_and_revoke \ "SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", \ "CREATE", "CONNECT", "TEMPORARY", "EXECUTE", "USAGE", "SET", "ALTER SYSTEM", \ -"ALL" +"VACUUM", "ANALYZE", "ALL" /* * These object types were introduced later than our support cutoff of @@ -3782,7 +3782,8 @@ psql_completion(const char *text, int start, int end) if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES")) COMPLETE_WITH("SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", - "CREATE", "EXECUTE", "USAGE", "ALL"); + "CREATE", "EXECUTE", "USAGE", "VACUUM", "ANALYZE", + "ALL"); else if (TailMatches("GRANT")) COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles, Privilege_options_of_grant_and_revoke); diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index b63751c468..4e4bc26a8b 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -295,8 +295,8 @@ extern bool vacuum_xid_failsafe_check(TransactionId relfrozenxid, MultiXactId relminmxid); extern void vac_update_datfrozenxid(void); extern void vacuum_delay_point(void); -extern bool vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, - bits32 options); +extern bool vacuum_is_permitted_for_relation(Oid relid, Form_pg_class reltuple, + bits32 options); extern Relation vacuum_open_relation(Oid relid, RangeVar *relation, bits32 options, bool verbose, LOCKMODE lmode); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index f4ed9bbff9..6112cd85c8 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -95,7 +95,9 @@ typedef uint64 AclMode; /* a bitmask of privilege bits */ #define ACL_CONNECT (1<<11) /* for databases */ #define ACL_SET (1<<12) /* for configuration parameters */ #define ACL_ALTER_SYSTEM (1<<13) /* for configuration parameters */ -#define N_ACL_RIGHTS 14 /* 1 plus the last 1<<x */ +#define ACL_VACUUM (1<<14) /* for relations */ +#define ACL_ANALYZE (1<<15) /* for relations */ +#define N_ACL_RIGHTS 16 /* 1 plus the last 1<<x */ #define ACL_NO_RIGHTS 0 /* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */ #define ACL_SELECT_FOR_UPDATE ACL_UPDATE diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h index 406071037e..e566ff0c73 100644 --- a/src/include/utils/acl.h +++ b/src/include/utils/acl.h @@ -148,15 +148,17 @@ typedef struct ArrayType Acl; #define ACL_CONNECT_CHR 'c' #define ACL_SET_CHR 's' #define ACL_ALTER_SYSTEM_CHR 'A' +#define ACL_VACUUM_CHR 'v' +#define ACL_ANALYZE_CHR 'z' /* string holding all privilege code chars, in order by bitmask position */ -#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsA" +#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsAvz" /* * Bitmasks defining "all rights" for each supported object type */ #define ACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES) -#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER) +#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER|ACL_VACUUM|ACL_ANALYZE) #define ACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE) #define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT) #define ACL_ALL_RIGHTS_FDW (ACL_USAGE) diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out index 8232795148..81d8376509 100644 --- a/src/test/regress/expected/dependency.out +++ b/src/test/regress/expected/dependency.out @@ -19,7 +19,7 @@ DETAIL: privileges for table deptest REVOKE SELECT ON deptest FROM GROUP regress_dep_group; 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, VACUUM, ANALYZE ON deptest FROM regress_dep_user; DROP USER regress_dep_user; ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it DETAIL: privileges for table deptest @@ -63,21 +63,21 @@ CREATE TABLE deptest (a serial primary key, b text); GRANT ALL ON deptest1 TO regress_dep_user2; RESET SESSION AUTHORIZATION; \z deptest1 - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+----------+-------+----------------------------------------------------+-------------------+---------- - public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 +| | - | | | regress_dep_user1=a*r*w*d*D*x*t*/regress_dep_user0+| | - | | | regress_dep_user2=arwdDxt/regress_dep_user1 | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+----------+-------+--------------------------------------------------------+-------------------+---------- + public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 +| | + | | | regress_dep_user1=a*r*w*d*D*x*t*v*z*/regress_dep_user0+| | + | | | regress_dep_user2=arwdDxtvz/regress_dep_user1 | | (1 row) DROP OWNED BY regress_dep_user1; -- all grants revoked \z deptest1 - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+----------+-------+---------------------------------------------+-------------------+---------- - public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+----------+-------+-----------------------------------------------+-------------------+---------- + public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 | | (1 row) -- table was dropped diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 4045bcafb5..a2d9572179 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -2570,39 +2570,39 @@ grant select on dep_priv_test to regress_priv_user4 with grant option; set session role regress_priv_user4; grant select on dep_priv_test to regress_priv_user5; \dp dep_priv_test - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-----------------------------------------------+-------------------+---------- - public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 +| | - | | | regress_priv_user4=r*/regress_priv_user2 +| | - | | | regress_priv_user4=r*/regress_priv_user3 +| | - | | | regress_priv_user5=r/regress_priv_user4 | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------------+-------+-------------------------------------------------+-------------------+---------- + public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| | + | | | regress_priv_user2=r*/regress_priv_user1 +| | + | | | regress_priv_user3=r*/regress_priv_user1 +| | + | | | regress_priv_user4=r*/regress_priv_user2 +| | + | | | regress_priv_user4=r*/regress_priv_user3 +| | + | | | regress_priv_user5=r/regress_priv_user4 | | (1 row) set session role regress_priv_user2; revoke select on dep_priv_test from regress_priv_user4 cascade; \dp dep_priv_test - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-----------------------------------------------+-------------------+---------- - public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 +| | - | | | regress_priv_user4=r*/regress_priv_user3 +| | - | | | regress_priv_user5=r/regress_priv_user4 | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------------+-------+-------------------------------------------------+-------------------+---------- + public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| | + | | | regress_priv_user2=r*/regress_priv_user1 +| | + | | | regress_priv_user3=r*/regress_priv_user1 +| | + | | | regress_priv_user4=r*/regress_priv_user3 +| | + | | | regress_priv_user5=r/regress_priv_user4 | | (1 row) set session role regress_priv_user3; revoke select on dep_priv_test from regress_priv_user4 cascade; \dp dep_priv_test - Access privileges - Schema | Name | Type | Access privileges | Column privileges | Policies ---------+---------------+-------+-----------------------------------------------+-------------------+---------- - public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | - | | | regress_priv_user2=r*/regress_priv_user1 +| | - | | | regress_priv_user3=r*/regress_priv_user1 | | + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------------+-------+-------------------------------------------------+-------------------+---------- + public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| | + | | | regress_priv_user2=r*/regress_priv_user1 +| | + | | | regress_priv_user3=r*/regress_priv_user1 | | (1 row) set session role regress_priv_user1; @@ -2849,3 +2849,43 @@ DROP SCHEMA regress_roleoption; DROP ROLE regress_roleoption_protagonist; DROP ROLE regress_roleoption_donor; DROP ROLE regress_roleoption_recipient; +-- VACUUM and ANALYZE +CREATE ROLE regress_no_priv; +CREATE ROLE regress_only_vacuum; +CREATE ROLE regress_only_analyze; +CREATE ROLE regress_both; +CREATE TABLE vacanalyze_test (a INT); +GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both; +GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both; +SET ROLE regress_no_priv; +VACUUM vacanalyze_test; +WARNING: permission denied to vacuum "vacanalyze_test", skipping it +ANALYZE vacanalyze_test; +WARNING: permission denied to analyze "vacanalyze_test", skipping it +VACUUM (ANALYZE) vacanalyze_test; +WARNING: permission denied to vacuum "vacanalyze_test", skipping it +RESET ROLE; +SET ROLE regress_only_vacuum; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +WARNING: permission denied to analyze "vacanalyze_test", skipping it +VACUUM (ANALYZE) vacanalyze_test; +WARNING: permission denied to analyze "vacanalyze_test", skipping it +RESET ROLE; +SET ROLE regress_only_analyze; +VACUUM vacanalyze_test; +WARNING: permission denied to vacuum "vacanalyze_test", skipping it +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +WARNING: permission denied to vacuum "vacanalyze_test", skipping it +RESET ROLE; +SET ROLE regress_both; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; +DROP TABLE vacanalyze_test; +DROP ROLE regress_no_priv; +DROP ROLE regress_only_vacuum; +DROP ROLE regress_only_analyze; +DROP ROLE regress_both; diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out index b5f6eecba1..ac21a11330 100644 --- a/src/test/regress/expected/rowsecurity.out +++ b/src/test/regress/expected/rowsecurity.out @@ -93,23 +93,23 @@ CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave USING (cid <> 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=arwdDxtvz/regress_rls_alice+| | + | | | =arwdDxtvz/regress_rls_alice | | + regress_rls_schema | document | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| | p1: + + | | | =arwdDxtvz/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=arwdDxtvz/regress_rls_alice+| | + | | | =r/regress_rls_alice | | (3 rows) \d document diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out index 0035d158b7..e0fb21b36e 100644 --- a/src/test/regress/expected/vacuum.out +++ b/src/test/regress/expected/vacuum.out @@ -336,7 +336,9 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_parted; WARNING: permission denied to vacuum "vacowned_parted", skipping it WARNING: permission denied to vacuum "vacowned_part1", skipping it +WARNING: permission denied to analyze "vacowned_part1", skipping it WARNING: permission denied to vacuum "vacowned_part2", skipping it +WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_part1; WARNING: permission denied to vacuum "vacowned_part1", skipping it VACUUM (ANALYZE) vacowned_part2; @@ -358,6 +360,7 @@ ANALYZE vacowned_part2; WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_parted; WARNING: permission denied to vacuum "vacowned_part2", skipping it +WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_part1; VACUUM (ANALYZE) vacowned_part2; WARNING: permission denied to vacuum "vacowned_part2", skipping it @@ -380,6 +383,7 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_parted; WARNING: permission denied to vacuum "vacowned_parted", skipping it WARNING: permission denied to vacuum "vacowned_part2", skipping it +WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_part1; VACUUM (ANALYZE) vacowned_part2; WARNING: permission denied to vacuum "vacowned_part2", skipping it @@ -404,7 +408,9 @@ ANALYZE vacowned_part2; WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_parted; WARNING: permission denied to vacuum "vacowned_part1", skipping it +WARNING: permission denied to analyze "vacowned_part1", skipping it WARNING: permission denied to vacuum "vacowned_part2", skipping it +WARNING: permission denied to analyze "vacowned_part2", skipping it VACUUM (ANALYZE) vacowned_part1; WARNING: permission denied to vacuum "vacowned_part1", skipping it VACUUM (ANALYZE) vacowned_part2; diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql index 2559c62d0b..99b905a938 100644 --- a/src/test/regress/sql/dependency.sql +++ b/src/test/regress/sql/dependency.sql @@ -21,7 +21,7 @@ REVOKE SELECT ON deptest FROM GROUP regress_dep_group; 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, VACUUM, ANALYZE 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/privileges.sql b/src/test/regress/sql/privileges.sql index 92c000cf00..dd65c3264e 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -1852,3 +1852,43 @@ DROP SCHEMA regress_roleoption; DROP ROLE regress_roleoption_protagonist; DROP ROLE regress_roleoption_donor; DROP ROLE regress_roleoption_recipient; + +-- VACUUM and ANALYZE +CREATE ROLE regress_no_priv; +CREATE ROLE regress_only_vacuum; +CREATE ROLE regress_only_analyze; +CREATE ROLE regress_both; + +CREATE TABLE vacanalyze_test (a INT); +GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both; +GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both; + +SET ROLE regress_no_priv; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + +SET ROLE regress_only_vacuum; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + +SET ROLE regress_only_analyze; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + +SET ROLE regress_both; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + +DROP TABLE vacanalyze_test; +DROP ROLE regress_no_priv; +DROP ROLE regress_only_vacuum; +DROP ROLE regress_only_analyze; +DROP ROLE regress_both; -- 2.25.1
>From 7f5f733062ac7be7b8d1f85742dee0adc11327e4 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <nathandboss...@gmail.com> Date: Tue, 6 Sep 2022 10:32:11 -0700 Subject: [PATCH v13 2/2] Add pg_vacuum_all_tables and pg_analyze_all_tables roles. --- doc/src/sgml/ref/analyze.sgml | 10 +++++++--- doc/src/sgml/ref/vacuum.sgml | 10 +++++++--- doc/src/sgml/user-manag.sgml | 12 ++++++++++++ src/backend/catalog/aclchk.c | 20 +++++++++++++++++++ src/include/catalog/pg_authid.dat | 10 ++++++++++ src/test/regress/expected/privileges.out | 25 ++++++++++++++++++++++++ src/test/regress/sql/privileges.sql | 24 +++++++++++++++++++++++ 7 files changed, 105 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index 400ea30cd0..16c0b886fd 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -148,12 +148,16 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea <title>Notes</title> <para> - To analyze a table, one must ordinarily be the table's owner or a - superuser or have the <literal>ANALYZE</literal> privilege on the table. + To analyze a table, one must ordinarily have the <literal>ANALYZE</literal> + privilege on the table or be the table's owner, a superuser, or a role with + privileges of the + <link linkend="predefined-roles-table"><literal>pg_analyze_all_tables</literal></link> + role. However, database owners are allowed to analyze all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide - <command>ANALYZE</command> can only be performed by a superuser.) + <command>ANALYZE</command> can only be performed by superusers and roles + with privileges of <literal>pg_analyze_all_tables</literal>.) <command>ANALYZE</command> will skip over any tables that the calling user does not have permission to analyze. </para> diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index 70c0d81346..9cd880ea34 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -356,12 +356,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet <title>Notes</title> <para> - To vacuum a table, one must ordinarily be the table's owner or a - superuser or have the <literal>VACUUM</literal> privilege on the table. + To vacuum a table, one must ordinarily have the <literal>VACUUM</literal> + privilege on the table or be the table's owner, a superuser, or a role with + privileges of the + <link linkend="predefined-roles-table"><literal>pg_vacuum_all_tables</literal></link> + role. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide - <command>VACUUM</command> can only be performed by a superuser.) + <command>VACUUM</command> can only be performed by superusers and roles + with privileges of <literal>pg_vacuum_all_tables</literal>.) <command>VACUUM</command> will skip over any tables that the calling user does not have permission to vacuum. </para> diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml index 601fff3e6b..2bff4e47d0 100644 --- a/doc/src/sgml/user-manag.sgml +++ b/doc/src/sgml/user-manag.sgml @@ -635,6 +635,18 @@ DROP ROLE doomed_role; the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link> command.</entry> </row> + <row> + <entry>pg_vacuum_all_tables</entry> + <entry>Allow executing the + <link linkend="sql-vacuum"><command>VACUUM</command></link> command on + all tables.</entry> + </row> + <row> + <entry>pg_analyze_all_tables</entry> + <entry>Allow executing the + <link linkend="sql-analyze"><command>ANALYZE</command></link> command on + all tables.</entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 3b5ea3c137..bd967eaa78 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -4202,6 +4202,26 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask, has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA)) result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE)); + /* + * Check if ACL_VACUUM is being checked and, if so, and not already set as + * part of the result, then check if the user is a member of the + * pg_vacuum_all_tables role, which allows VACUUM on all relations. + */ + if (mask & ACL_VACUUM && + !(result & ACL_VACUUM) && + has_privs_of_role(roleid, ROLE_PG_VACUUM_ALL_TABLES)) + result |= ACL_VACUUM; + + /* + * Check if ACL_ANALYZE is being checked and, if so, and not already set as + * part of the result, then check if the user is a member of the + * pg_analyze_all_tables role, which allows ANALYZE on all relations. + */ + if (mask & ACL_ANALYZE && + !(result & ACL_ANALYZE) && + has_privs_of_role(roleid, ROLE_PG_ANALYZE_ALL_TABLES)) + result |= ACL_ANALYZE; + return result; } diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat index 3343a69ddb..2574e2906d 100644 --- a/src/include/catalog/pg_authid.dat +++ b/src/include/catalog/pg_authid.dat @@ -84,5 +84,15 @@ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', rolpassword => '_null_', rolvaliduntil => '_null_' }, +{ oid => '4549', oid_symbol => 'ROLE_PG_VACUUM_ALL_TABLES', + rolname => 'pg_vacuum_all_tables', rolsuper => 'f', rolinherit => 't', + rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', + rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', + rolpassword => '_null_', rolvaliduntil => '_null_' }, +{ oid => '4550', oid_symbol => 'ROLE_PG_ANALYZE_ALL_TABLES', + rolname => 'pg_analyze_all_tables', rolsuper => 'f', rolinherit => 't', + rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f', + rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1', + rolpassword => '_null_', rolvaliduntil => '_null_' }, ] diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index a2d9572179..7933314fd3 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -2854,6 +2854,9 @@ CREATE ROLE regress_no_priv; CREATE ROLE regress_only_vacuum; CREATE ROLE regress_only_analyze; CREATE ROLE regress_both; +CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables; +CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables; +CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables; CREATE TABLE vacanalyze_test (a INT); GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both; GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both; @@ -2884,8 +2887,30 @@ VACUUM vacanalyze_test; ANALYZE vacanalyze_test; VACUUM (ANALYZE) vacanalyze_test; RESET ROLE; +SET ROLE regress_only_vacuum_all; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +WARNING: permission denied to analyze "vacanalyze_test", skipping it +VACUUM (ANALYZE) vacanalyze_test; +WARNING: permission denied to analyze "vacanalyze_test", skipping it +RESET ROLE; +SET ROLE regress_only_analyze_all; +VACUUM vacanalyze_test; +WARNING: permission denied to vacuum "vacanalyze_test", skipping it +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +WARNING: permission denied to vacuum "vacanalyze_test", skipping it +RESET ROLE; +SET ROLE regress_both_all; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; DROP TABLE vacanalyze_test; DROP ROLE regress_no_priv; DROP ROLE regress_only_vacuum; DROP ROLE regress_only_analyze; DROP ROLE regress_both; +DROP ROLE regress_only_vacuum_all; +DROP ROLE regress_only_analyze_all; +DROP ROLE regress_both_all; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index dd65c3264e..1bcaaba4eb 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -1858,6 +1858,9 @@ CREATE ROLE regress_no_priv; CREATE ROLE regress_only_vacuum; CREATE ROLE regress_only_analyze; CREATE ROLE regress_both; +CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables; +CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables; +CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables; CREATE TABLE vacanalyze_test (a INT); GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both; @@ -1887,8 +1890,29 @@ ANALYZE vacanalyze_test; VACUUM (ANALYZE) vacanalyze_test; RESET ROLE; +SET ROLE regress_only_vacuum_all; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + +SET ROLE regress_only_analyze_all; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + +SET ROLE regress_both_all; +VACUUM vacanalyze_test; +ANALYZE vacanalyze_test; +VACUUM (ANALYZE) vacanalyze_test; +RESET ROLE; + DROP TABLE vacanalyze_test; DROP ROLE regress_no_priv; DROP ROLE regress_only_vacuum; DROP ROLE regress_only_analyze; DROP ROLE regress_both; +DROP ROLE regress_only_vacuum_all; +DROP ROLE regress_only_analyze_all; +DROP ROLE regress_both_all; -- 2.25.1