PostgreSQL uses table VACUUM and ANALYZE commands to optimize the database. The VACUUM command reclaims storage space and makes it available for re-use. It also updates the visibility map, which helps the query planner to quickly identify which parts of the table have live rows. The ANALYZE command collects statistics about the contents of tables, which the query planner uses to determine the most efficient execution plans.
Table of Contents
Currently a table can only be vacuumed or analyzed by its owner or a superuser. This can now be extended to any user by means of an appropriate GRANT thanks to Nathan Bossart's patch.
Also Nathan implemented another patch which provides two new predefined roles: "pg_vacuum_all_tables" and "pg_analyze_all_tables".
Roles which have been granted these roles' privileges can perform VACUUM or ANALYZE 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.
Both patches were committed by Andrew Dunstan and reviewed by Bharath Rupireddy, Kyotaro Horiguchi, Stephen Frost, Robert Haas, Mark Dilger, Tom Lane, Corey Huinker, David G. Johnston, and Michael Paquier.
Let's try it out!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
$ sudo sudo -u postgres psql test psql (16devel) Type 'help' for help. test=> SHOW server_version; server_version ---------------- 16devel (1 row) test=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} test=# create role stepan; CREATE ROLE test=# du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} stepan | Cannot login | {} test=# create table foo(); CREATE TABLE test=# dt foo List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | foo | table | postgres (1 row) test=# vacuum foo; VACUUM test=# set role stepan; SET test=> vacuum foo; WARNING: permission denied to vacuum 'foo', skipping it VACUUM test=> reset role; RESET test=# grant vacuum on table foo to stepan; GRANT test=# set role stepan; SET test=> vacuum foo; VACUUM test=> reset role; RESET test=# create role petro; CREATE ROLE test=# grant pg_vacuum_all_tables to petro; GRANT ROLE test=# set role petro; SET test=> vacuum foo; VACUUM |
Here's what the above SQL code is doing:
"test"."postgres" user create a new role called "stepan" and a new table called "foo"."foo" as "stepan". The output shows that "stepan" can't vacuum "foo" until we grant vacuum privilege on "foo" to "stepan"."postgres" role create a new role called "petro"."pg_vacuum_all_tables" role to "petro"."petro" can vacuum "foo" (and every other table in all databases!) even though we didn't grant vacuum on "foo" to "petro" role. The reason "petro" can vacuum "foo" is because "petro" is a member of the "pg_vacuum_all_tables" role.VACUUM and ANALYZE are PostgreSQL commands used to optimize the database. GRANT VACUUM, ANALYZE can be used to extend the permission to vacuum and analyze tables to any user. This also can be done by granting the user a predefined role like "pg_vacuum_all_tables" or "pg_analyze_all_tables". This removes the need to grant superuser privilege to roles just so they can perform vacuum and/or analyze.
Check our other blog posts about vacuuming and analyzing tables.
Hi,
These roles where replaced by the pg_maintain and MAINTAIN privilege.
https://www.postgresql.org/message-id/20221210204109.GA197392%40nathanxps13