Skip to content

Navigation Menu

Sign in

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
Appearance settings

Commit 4441fc7

Browse filesBrowse files
adunstanadunstan
committed
Provide non-superuser predefined roles for vacuum and analyze
This provides two new predefined roles: pg_vacuum_all_tables and pg_analyze_all_tables. Roles which have been granted these roles can perform vacuum or analyse respectively on any or all tables as if they were a superuser. This removes the need to grant superuser privilege to roles just so they can perform vacuum and/or analyze. Nathan Bossart Reviewed by: Bharath Rupireddy, Kyotaro Horiguchi, Stephen Frost, Robert Haas, Mark Dilger, Tom Lane, Corey Huinker, David G. Johnston, Michael Paquier. Discussion: https://postgr.es/m/20220722203735.GB3996698@nathanxps13
1 parent b5d6382 commit 4441fc7
Copy full SHA for 4441fc7

File tree

8 files changed

+106
-7
lines changed
Filter options

8 files changed

+106
-7
lines changed

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/analyze.sgml
+7-3Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -148,12 +148,16 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
148148
<title>Notes</title>
149149

150150
<para>
151-
To analyze a table, one must ordinarily be the table's owner or a
152-
superuser or have the <literal>ANALYZE</literal> privilege on the table.
151+
To analyze a table, one must ordinarily have the <literal>ANALYZE</literal>
152+
privilege on the table or be the table's owner, a superuser, or a role with
153+
privileges of the
154+
<link linkend="predefined-roles-table"><literal>pg_analyze_all_tables</literal></link>
155+
role.
153156
However, database owners are allowed to
154157
analyze all tables in their databases, except shared catalogs.
155158
(The restriction for shared catalogs means that a true database-wide
156-
<command>ANALYZE</command> can only be performed by a superuser.)
159+
<command>ANALYZE</command> can only be performed by superusers and roles
160+
with privileges of <literal>pg_analyze_all_tables</literal>.)
157161
<command>ANALYZE</command> will skip over any tables that the calling user
158162
does not have permission to analyze.
159163
</para>

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/vacuum.sgml
+7-3Lines changed: 7 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -356,12 +356,16 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
356356
<title>Notes</title>
357357

358358
<para>
359-
To vacuum a table, one must ordinarily be the table's owner or a
360-
superuser or have the <literal>VACUUM</literal> privilege on the table.
359+
To vacuum a table, one must ordinarily have the <literal>VACUUM</literal>
360+
privilege on the table or be the table's owner, a superuser, or a role with
361+
privileges of the
362+
<link linkend="predefined-roles-table"><literal>pg_vacuum_all_tables</literal></link>
363+
role.
361364
However, database owners are allowed to
362365
vacuum all tables in their databases, except shared catalogs.
363366
(The restriction for shared catalogs means that a true database-wide
364-
<command>VACUUM</command> can only be performed by a superuser.)
367+
<command>VACUUM</command> can only be performed by superusers and roles
368+
with privileges of <literal>pg_vacuum_all_tables</literal>.)
365369
<command>VACUUM</command> will skip over any tables that the calling user
366370
does not have permission to vacuum.
367371
</para>

‎doc/src/sgml/user-manag.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/user-manag.sgml
+12Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -635,6 +635,18 @@ DROP ROLE doomed_role;
635635
the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link>
636636
command.</entry>
637637
</row>
638+
<row>
639+
<entry>pg_vacuum_all_tables</entry>
640+
<entry>Allow executing the
641+
<link linkend="sql-vacuum"><command>VACUUM</command></link> command on
642+
all tables.</entry>
643+
</row>
644+
<row>
645+
<entry>pg_analyze_all_tables</entry>
646+
<entry>Allow executing the
647+
<link linkend="sql-analyze"><command>ANALYZE</command></link> command on
648+
all tables.</entry>
649+
</row>
638650
</tbody>
639651
</tgroup>
640652
</table>

‎src/backend/catalog/aclchk.c

Copy file name to clipboardExpand all lines: src/backend/catalog/aclchk.c
+20Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4202,6 +4202,26 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
42024202
has_privs_of_role(roleid, ROLE_PG_WRITE_ALL_DATA))
42034203
result |= (mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE));
42044204

4205+
/*
4206+
* Check if ACL_VACUUM is being checked and, if so, and not already set as
4207+
* part of the result, then check if the user is a member of the
4208+
* pg_vacuum_all_tables role, which allows VACUUM on all relations.
4209+
*/
4210+
if (mask & ACL_VACUUM &&
4211+
!(result & ACL_VACUUM) &&
4212+
has_privs_of_role(roleid, ROLE_PG_VACUUM_ALL_TABLES))
4213+
result |= ACL_VACUUM;
4214+
4215+
/*
4216+
* Check if ACL_ANALYZE is being checked and, if so, and not already set as
4217+
* part of the result, then check if the user is a member of the
4218+
* pg_analyze_all_tables role, which allows ANALYZE on all relations.
4219+
*/
4220+
if (mask & ACL_ANALYZE &&
4221+
!(result & ACL_ANALYZE) &&
4222+
has_privs_of_role(roleid, ROLE_PG_ANALYZE_ALL_TABLES))
4223+
result |= ACL_ANALYZE;
4224+
42054225
return result;
42064226
}
42074227

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/* yyyymmddN */
60-
#define CATALOG_VERSION_NO 202211241
60+
#define CATALOG_VERSION_NO 202211281
6161

6262
#endif

‎src/include/catalog/pg_authid.dat

Copy file name to clipboardExpand all lines: src/include/catalog/pg_authid.dat
+10Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -84,5 +84,15 @@
8484
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
8585
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
8686
rolpassword => '_null_', rolvaliduntil => '_null_' },
87+
{ oid => '4549', oid_symbol => 'ROLE_PG_VACUUM_ALL_TABLES',
88+
rolname => 'pg_vacuum_all_tables', rolsuper => 'f', rolinherit => 't',
89+
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
90+
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
91+
rolpassword => '_null_', rolvaliduntil => '_null_' },
92+
{ oid => '4550', oid_symbol => 'ROLE_PG_ANALYZE_ALL_TABLES',
93+
rolname => 'pg_analyze_all_tables', rolsuper => 'f', rolinherit => 't',
94+
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
95+
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
96+
rolpassword => '_null_', rolvaliduntil => '_null_' },
8797

8898
]

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/privileges.out
+25Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2854,6 +2854,9 @@ CREATE ROLE regress_no_priv;
28542854
CREATE ROLE regress_only_vacuum;
28552855
CREATE ROLE regress_only_analyze;
28562856
CREATE ROLE regress_both;
2857+
CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables;
2858+
CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables;
2859+
CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables;
28572860
CREATE TABLE vacanalyze_test (a INT);
28582861
GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;
28592862
GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both;
@@ -2884,8 +2887,30 @@ VACUUM vacanalyze_test;
28842887
ANALYZE vacanalyze_test;
28852888
VACUUM (ANALYZE) vacanalyze_test;
28862889
RESET ROLE;
2890+
SET ROLE regress_only_vacuum_all;
2891+
VACUUM vacanalyze_test;
2892+
ANALYZE vacanalyze_test;
2893+
WARNING: permission denied to analyze "vacanalyze_test", skipping it
2894+
VACUUM (ANALYZE) vacanalyze_test;
2895+
WARNING: permission denied to analyze "vacanalyze_test", skipping it
2896+
RESET ROLE;
2897+
SET ROLE regress_only_analyze_all;
2898+
VACUUM vacanalyze_test;
2899+
WARNING: permission denied to vacuum "vacanalyze_test", skipping it
2900+
ANALYZE vacanalyze_test;
2901+
VACUUM (ANALYZE) vacanalyze_test;
2902+
WARNING: permission denied to vacuum "vacanalyze_test", skipping it
2903+
RESET ROLE;
2904+
SET ROLE regress_both_all;
2905+
VACUUM vacanalyze_test;
2906+
ANALYZE vacanalyze_test;
2907+
VACUUM (ANALYZE) vacanalyze_test;
2908+
RESET ROLE;
28872909
DROP TABLE vacanalyze_test;
28882910
DROP ROLE regress_no_priv;
28892911
DROP ROLE regress_only_vacuum;
28902912
DROP ROLE regress_only_analyze;
28912913
DROP ROLE regress_both;
2914+
DROP ROLE regress_only_vacuum_all;
2915+
DROP ROLE regress_only_analyze_all;
2916+
DROP ROLE regress_both_all;

‎src/test/regress/sql/privileges.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/privileges.sql
+24Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1858,6 +1858,9 @@ CREATE ROLE regress_no_priv;
18581858
CREATE ROLE regress_only_vacuum;
18591859
CREATE ROLE regress_only_analyze;
18601860
CREATE ROLE regress_both;
1861+
CREATE ROLE regress_only_vacuum_all IN ROLE pg_vacuum_all_tables;
1862+
CREATE ROLE regress_only_analyze_all IN ROLE pg_analyze_all_tables;
1863+
CREATE ROLE regress_both_all IN ROLE pg_vacuum_all_tables, pg_analyze_all_tables;
18611864

18621865
CREATE TABLE vacanalyze_test (a INT);
18631866
GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;
@@ -1887,8 +1890,29 @@ ANALYZE vacanalyze_test;
18871890
VACUUM (ANALYZE) vacanalyze_test;
18881891
RESET ROLE;
18891892

1893+
SET ROLE regress_only_vacuum_all;
1894+
VACUUM vacanalyze_test;
1895+
ANALYZE vacanalyze_test;
1896+
VACUUM (ANALYZE) vacanalyze_test;
1897+
RESET ROLE;
1898+
1899+
SET ROLE regress_only_analyze_all;
1900+
VACUUM vacanalyze_test;
1901+
ANALYZE vacanalyze_test;
1902+
VACUUM (ANALYZE) vacanalyze_test;
1903+
RESET ROLE;
1904+
1905+
SET ROLE regress_both_all;
1906+
VACUUM vacanalyze_test;
1907+
ANALYZE vacanalyze_test;
1908+
VACUUM (ANALYZE) vacanalyze_test;
1909+
RESET ROLE;
1910+
18901911
DROP TABLE vacanalyze_test;
18911912
DROP ROLE regress_no_priv;
18921913
DROP ROLE regress_only_vacuum;
18931914
DROP ROLE regress_only_analyze;
18941915
DROP ROLE regress_both;
1916+
DROP ROLE regress_only_vacuum_all;
1917+
DROP ROLE regress_only_analyze_all;
1918+
DROP ROLE regress_both_all;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.