Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb5d6382

Browse files
committed
Provide per-table permissions for vacuum and analyze.
Currently a table can only be vacuumed or analyzed by its owner ora superuser. This can now be extended to any user by means of anappropriate GRANT.Nathan BossartReviewed by: Bharath Rupireddy, Kyotaro Horiguchi, Stephen Frost, RobertHaas, Mark Dilger, Tom Lane, Corey Huinker, David G. Johnston, MichaelPaquier.Discussion:https://postgr.es/m/20220722203735.GB3996698@nathanxps13
1 parent1f059a4 commitb5d6382

File tree

24 files changed

+274
-113
lines changed

24 files changed

+274
-113
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 39 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1691,8 +1691,9 @@ ALTER TABLE products RENAME TO items;
16911691
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
16921692
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
16931693
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
1694-
<literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>
1695-
and <literal>ALTER SYSTEM</literal>.
1694+
<literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>,
1695+
<literal>ALTER SYSTEM</literal>, <literal>VACUUM</literal>, and
1696+
<literal>ANALYZE</literal>.
16961697
The privileges applicable to a particular
16971698
object vary depending on the object's type (table, function, etc.).
16981699
More detail about the meanings of these privileges appears below.
@@ -1982,7 +1983,25 @@ REVOKE ALL ON accounts FROM PUBLIC;
19821983
</para>
19831984
</listitem>
19841985
</varlistentry>
1985-
</variablelist>
1986+
1987+
<varlistentry>
1988+
<term><literal>VACUUM</literal></term>
1989+
<listitem>
1990+
<para>
1991+
Allows <command>VACUUM</command> on a relation.
1992+
</para>
1993+
</listitem>
1994+
</varlistentry>
1995+
1996+
<varlistentry>
1997+
<term><literal>ANALYZE</literal></term>
1998+
<listitem>
1999+
<para>
2000+
Allows <command>ANALYZE</command> on a relation.
2001+
</para>
2002+
</listitem>
2003+
</varlistentry>
2004+
</variablelist>
19862005

19872006
The privileges required by other commands are listed on the
19882007
reference page of the respective command.
@@ -2131,6 +2150,16 @@ REVOKE ALL ON accounts FROM PUBLIC;
21312150
<entry><literal>A</literal></entry>
21322151
<entry><literal>PARAMETER</literal></entry>
21332152
</row>
2153+
<row>
2154+
<entry><literal>VACUUM</literal></entry>
2155+
<entry><literal>v</literal></entry>
2156+
<entry><literal>TABLE</literal></entry>
2157+
</row>
2158+
<row>
2159+
<entry><literal>ANALYZE</literal></entry>
2160+
<entry><literal>z</literal></entry>
2161+
<entry><literal>TABLE</literal></entry>
2162+
</row>
21342163
</tbody>
21352164
</tgroup>
21362165
</table>
@@ -2221,7 +2250,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
22212250
</row>
22222251
<row>
22232252
<entry><literal>TABLE</literal> (and table-like objects)</entry>
2224-
<entry><literal>arwdDxt</literal></entry>
2253+
<entry><literal>arwdDxtvz</literal></entry>
22252254
<entry>none</entry>
22262255
<entry><literal>\dp</literal></entry>
22272256
</row>
@@ -2279,12 +2308,12 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
22792308
would show:
22802309
<programlisting>
22812310
=&gt; \dp mytable
2282-
Access privileges
2283-
Schema | Name | Type | Access privileges | Column privileges | Policies
2284-
--------+---------+-------+-----------------------+-----------------------+----------
2285-
public | mytable | table | miriam=arwdDxt/miriam+| col1: +|
2286-
| | | =r/miriam +| miriam_rw=rw/miriam |
2287-
| | | admin=arw/miriam | |
2311+
Access privileges
2312+
Schema | Name | Type |Access privileges | Column privileges | Policies
2313+
--------+---------+-------+-------------------------+-----------------------+----------
2314+
public | mytable | table | miriam=arwdDxtvz/miriam+| col1: +|
2315+
| | | =r/miriam+| miriam_rw=rw/miriam |
2316+
| | | admin=arw/miriam| |
22882317
(1 row)
22892318
</programlisting>
22902319
</para>

‎doc/src/sgml/func.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22978,7 +22978,8 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
2297822978
are <literal>SELECT</literal>, <literal>INSERT</literal>,
2297922979
<literal>UPDATE</literal>, <literal>DELETE</literal>,
2298022980
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>,
22981-
and <literal>TRIGGER</literal>.
22981+
<literal>TRIGGER</literal>, <literal>VACUUM</literal> and
22982+
<literal>ANALYZE</literal>.
2298222983
</para></entry>
2298322984
</row>
2298422985

‎doc/src/sgml/ref/alter_default_privileges.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -28,7 +28,7 @@ ALTER DEFAULT PRIVILEGES
2828

2929
<phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
3030

31-
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
31+
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER| VACUUM | ANALYZE}
3232
[, ...] | ALL [ PRIVILEGES ] }
3333
ON TABLES
3434
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
5151
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
5252

5353
REVOKE [ GRANT OPTION FOR ]
54-
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
54+
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER| VACUUM | ANALYZE}
5555
[, ...] | ALL [ PRIVILEGES ] }
5656
ON TABLES
5757
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]

‎doc/src/sgml/ref/analyze.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -149,7 +149,8 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
149149

150150
<para>
151151
To analyze a table, one must ordinarily be the table's owner or a
152-
superuser. However, database owners are allowed to
152+
superuser or have the <literal>ANALYZE</literal> privilege on the table.
153+
However, database owners are allowed to
153154
analyze all tables in their databases, except shared catalogs.
154155
(The restriction for shared catalogs means that a true database-wide
155156
<command>ANALYZE</command> can only be performed by a superuser.)

‎doc/src/sgml/ref/grant.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
24+
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER| VACUUM | ANALYZE}
2525
[, ...] | ALL [ PRIVILEGES ] }
2626
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
2727
| ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
@@ -193,6 +193,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
193193
<term><literal>USAGE</literal></term>
194194
<term><literal>SET</literal></term>
195195
<term><literal>ALTER SYSTEM</literal></term>
196+
<term><literal>VACUUM</literal></term>
197+
<term><literal>ANALYZE</literal></term>
196198
<listitem>
197199
<para>
198200
Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.

‎doc/src/sgml/ref/revoke.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
REVOKE [ GRANT OPTION FOR ]
25-
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
25+
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER| VACUUM | ANALYZE}
2626
[, ...] | ALL [ PRIVILEGES ] }
2727
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
2828
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }

‎doc/src/sgml/ref/vacuum.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -357,7 +357,8 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
357357

358358
<para>
359359
To vacuum a table, one must ordinarily be the table's owner or a
360-
superuser. However, database owners are allowed to
360+
superuser or have the <literal>VACUUM</literal> privilege on the table.
361+
However, database owners are allowed to
361362
vacuum all tables in their databases, except shared catalogs.
362363
(The restriction for shared catalogs means that a true database-wide
363364
<command>VACUUM</command> can only be performed by a superuser.)

‎src/backend/catalog/aclchk.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3420,6 +3420,10 @@ string_to_privilege(const char *privname)
34203420
returnACL_SET;
34213421
if (strcmp(privname,"alter system")==0)
34223422
returnACL_ALTER_SYSTEM;
3423+
if (strcmp(privname,"vacuum")==0)
3424+
returnACL_VACUUM;
3425+
if (strcmp(privname,"analyze")==0)
3426+
returnACL_ANALYZE;
34233427
if (strcmp(privname,"rule")==0)
34243428
return0;/* ignore old RULE privileges */
34253429
ereport(ERROR,
@@ -3461,6 +3465,10 @@ privilege_to_string(AclMode privilege)
34613465
return"SET";
34623466
caseACL_ALTER_SYSTEM:
34633467
return"ALTER SYSTEM";
3468+
caseACL_VACUUM:
3469+
return"VACUUM";
3470+
caseACL_ANALYZE:
3471+
return"ANALYZE";
34643472
default:
34653473
elog(ERROR,"unrecognized privilege: %d", (int)privilege);
34663474
}

‎src/backend/commands/analyze.c

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -159,16 +159,15 @@ analyze_rel(Oid relid, RangeVar *relation,
159159
return;
160160

161161
/*
162-
* Check if relation needs to be skipped based onownership. This check
162+
* Check if relation needs to be skipped based onprivileges. This check
163163
* happens also when building the relation list to analyze for a manual
164164
* operation, and needs to be done additionally here as ANALYZE could
165-
* happen across multiple transactions where relation ownership could have
166-
* changed in-between. Make sure to generate only logs for ANALYZE in
167-
* this case.
165+
* happen across multiple transactions where privileges could have changed
166+
* in-between. Make sure to generate only logs for ANALYZE in this case.
168167
*/
169-
if (!vacuum_is_relation_owner(RelationGetRelid(onerel),
170-
onerel->rd_rel,
171-
params->options&VACOPT_ANALYZE))
168+
if (!vacuum_is_permitted_for_relation(RelationGetRelid(onerel),
169+
onerel->rd_rel,
170+
VACOPT_ANALYZE))
172171
{
173172
relation_close(onerel,ShareUpdateExclusiveLock);
174173
return;

‎src/backend/commands/vacuum.c

Lines changed: 33 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -547,32 +547,35 @@ vacuum(List *relations, VacuumParams *params,
547547
}
548548

549549
/*
550-
* Check if a given relation can be safely vacuumed or analyzed. If the
551-
* user is not the relation owner, issue a WARNING log message and return
552-
* false to let the caller decide what to do with this relation. This
553-
* routine is used to decide if a relation can be processed for VACUUM or
554-
* ANALYZE.
550+
* Check if the current user has privileges to vacuum or analyze the relation.
551+
* If not, issue a WARNING log message and return false to let the caller
552+
* decide what to do with this relation. This routine is used to decide if a
553+
* relation can be processed for VACUUM or ANALYZE.
555554
*/
556555
bool
557-
vacuum_is_relation_owner(Oidrelid,Form_pg_classreltuple,bits32options)
556+
vacuum_is_permitted_for_relation(Oidrelid,Form_pg_classreltuple,
557+
bits32options)
558558
{
559559
char*relname;
560+
AclModemode=0;
560561

561562
Assert((options& (VACOPT_VACUUM |VACOPT_ANALYZE))!=0);
562563

563564
/*
564-
* Check permissions.
565-
*
566-
* We allow the user to vacuum or analyze a table if he is superuser, the
567-
* table owner, or the database owner (but in the latter case, only if
568-
* it's not a shared relation). object_ownercheck includes the
569-
* superuser case.
570-
*
571-
* Note we choose to treat permissions failure as a WARNING and keep
572-
* trying to vacuum or analyze the rest of the DB --- is this appropriate?
565+
* A role has privileges to vacuum or analyze the relation if any of the
566+
* following are true:
567+
* - the role is a superuser
568+
* - the role owns the relation
569+
* - the role owns the current database and the relation is not shared
570+
* - the role has been granted privileges to vacuum/analyze the relation
573571
*/
572+
if (options&VACOPT_VACUUM)
573+
mode |=ACL_VACUUM;
574+
if (options&VACOPT_ANALYZE)
575+
mode |=ACL_ANALYZE;
574576
if (object_ownercheck(RelationRelationId,relid,GetUserId())||
575-
(object_ownercheck(DatabaseRelationId,MyDatabaseId,GetUserId())&& !reltuple->relisshared))
577+
(object_ownercheck(DatabaseRelationId,MyDatabaseId,GetUserId())&& !reltuple->relisshared)||
578+
pg_class_aclcheck(relid,GetUserId(),mode)==ACLCHECK_OK)
576579
return true;
577580

578581
relname=NameStr(reltuple->relname);
@@ -787,10 +790,10 @@ expand_vacuum_rel(VacuumRelation *vrel, int options)
787790
classForm= (Form_pg_class)GETSTRUCT(tuple);
788791

789792
/*
790-
* Make a returnable VacuumRelation for this rel if useris a proper
791-
*owner.
793+
* Make a returnable VacuumRelation for this rel iftheuserhas the
794+
*required privileges.
792795
*/
793-
if (vacuum_is_relation_owner(relid,classForm,options))
796+
if (vacuum_is_permitted_for_relation(relid,classForm,options))
794797
{
795798
oldcontext=MemoryContextSwitchTo(vac_context);
796799
vacrels=lappend(vacrels,makeVacuumRelation(vrel->relation,
@@ -877,7 +880,7 @@ get_all_vacuum_rels(int options)
877880
Oidrelid=classForm->oid;
878881

879882
/* check permissions of relation */
880-
if (!vacuum_is_relation_owner(relid,classForm,options))
883+
if (!vacuum_is_permitted_for_relation(relid,classForm,options))
881884
continue;
882885

883886
/*
@@ -1797,7 +1800,9 @@ vac_truncate_clog(TransactionId frozenXID,
17971800
*be stale.
17981801
*
17991802
*Returns true if it's okay to proceed with a requested ANALYZE
1800-
*operation on this table.
1803+
*operation on this table. Note that if vacuuming fails because the user
1804+
*does not have the required privileges, this function returns true since
1805+
*the user might have been granted privileges to ANALYZE the relation.
18011806
*
18021807
*Doing one heap at a time incurs extra overhead, since we need to
18031808
*check that the heap exists again just before we vacuum it. The
@@ -1889,21 +1894,20 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
18891894
}
18901895

18911896
/*
1892-
* Check if relation needs to be skipped based onownership. This check
1897+
* Check if relation needs to be skipped based onprivileges. This check
18931898
* happens also when building the relation list to vacuum for a manual
18941899
* operation, and needs to be done additionally here as VACUUM could
1895-
* happen across multiple transactions where relation ownership could have
1896-
* changed in-between. Make sure to only generate logs for VACUUM in this
1897-
* case.
1900+
* happen across multiple transactions where privileges could have changed
1901+
* in-between. Make sure to only generate logs for VACUUM in this case.
18981902
*/
1899-
if (!vacuum_is_relation_owner(RelationGetRelid(rel),
1900-
rel->rd_rel,
1901-
params->options&VACOPT_VACUUM))
1903+
if (!vacuum_is_permitted_for_relation(RelationGetRelid(rel),
1904+
rel->rd_rel,
1905+
VACOPT_VACUUM))
19021906
{
19031907
relation_close(rel,lmode);
19041908
PopActiveSnapshot();
19051909
CommitTransactionCommand();
1906-
returnfalse;
1910+
returntrue;/* user might have the ANALYZE privilege */
19071911
}
19081912

19091913
/*

‎src/backend/parser/gram.y

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7482,6 +7482,13 @@ privilege:SELECT opt_column_list
74827482
n->cols = NIL;
74837483
$$ = n;
74847484
}
7485+
|analyze_keyword
7486+
{
7487+
AccessPriv *n = makeNode(AccessPriv);
7488+
n->priv_name = pstrdup("analyze");
7489+
n->cols = NIL;
7490+
$$ = n;
7491+
}
74857492
|ColIdopt_column_list
74867493
{
74877494
AccessPriv *n = makeNode(AccessPriv);

‎src/backend/utils/adt/acl.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -321,6 +321,12 @@ aclparse(const char *s, AclItem *aip)
321321
caseACL_ALTER_SYSTEM_CHR:
322322
read=ACL_ALTER_SYSTEM;
323323
break;
324+
caseACL_VACUUM_CHR:
325+
read=ACL_VACUUM;
326+
break;
327+
caseACL_ANALYZE_CHR:
328+
read=ACL_ANALYZE;
329+
break;
324330
case'R':/* ignore old RULE privileges */
325331
read=0;
326332
break;
@@ -1595,6 +1601,8 @@ makeaclitem(PG_FUNCTION_ARGS)
15951601
{"CONNECT",ACL_CONNECT},
15961602
{"SET",ACL_SET},
15971603
{"ALTER SYSTEM",ACL_ALTER_SYSTEM},
1604+
{"VACUUM",ACL_VACUUM},
1605+
{"ANALYZE",ACL_ANALYZE},
15981606
{"RULE",0},/* ignore old RULE privileges */
15991607
{NULL,0}
16001608
};
@@ -1703,6 +1711,10 @@ convert_aclright_to_string(int aclright)
17031711
return"SET";
17041712
caseACL_ALTER_SYSTEM:
17051713
return"ALTER SYSTEM";
1714+
caseACL_VACUUM:
1715+
return"VACUUM";
1716+
caseACL_ANALYZE:
1717+
return"ANALYZE";
17061718
default:
17071719
elog(ERROR,"unrecognized aclright: %d",aclright);
17081720
returnNULL;
@@ -2012,6 +2024,10 @@ convert_table_priv_string(text *priv_type_text)
20122024
{"REFERENCES WITH GRANT OPTION",ACL_GRANT_OPTION_FOR(ACL_REFERENCES)},
20132025
{"TRIGGER",ACL_TRIGGER},
20142026
{"TRIGGER WITH GRANT OPTION",ACL_GRANT_OPTION_FOR(ACL_TRIGGER)},
2027+
{"VACUUM",ACL_VACUUM},
2028+
{"VACUUM WITH GRANT OPTION",ACL_GRANT_OPTION_FOR(ACL_VACUUM)},
2029+
{"ANALYZE",ACL_ANALYZE},
2030+
{"ANALYZE WITH GRANT OPTION",ACL_GRANT_OPTION_FOR(ACL_ANALYZE)},
20152031
{"RULE",0},/* ignore old RULE privileges */
20162032
{"RULE WITH GRANT OPTION",0},
20172033
{NULL,0}

‎src/bin/pg_dump/dumputils.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -457,6 +457,8 @@ do { \
457457
CONVERT_PRIV('d',"DELETE");
458458
CONVERT_PRIV('t',"TRIGGER");
459459
CONVERT_PRIV('D',"TRUNCATE");
460+
CONVERT_PRIV('v',"VACUUM");
461+
CONVERT_PRIV('z',"ANALYZE");
460462
}
461463
}
462464

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -566,7 +566,7 @@
566566
\QREVOKE ALL ON TABLES FROM regress_dump_test_role;\E\n
567567
\QALTER DEFAULT PRIVILEGES\E
568568
\QFOR ROLE regress_dump_test_role\E
569-
\QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLES TO regress_dump_test_role;\E
569+
\QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,VACUUM,ANALYZE,UPDATE ON TABLES TO regress_dump_test_role;\E
570570
/xm,
571571
like=> {%full_runs,section_post_data=> 1, },
572572
unlike=> {no_privs=> 1, },

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp