GRANT VACUUM, ANALYZE in PostgreSQL 16

What is GRANT VACUUM, ANALYZE?

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

Why GRANT VACUUM, ANALYZE?

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.

How to GRANT VACUUM, ANALYZE?

Let's try it out!

$ 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

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:

Connect to a new database called "test".
As "postgres" user create a new role called "stepan" and a new table called "foo".
Try to vacuum "foo" as "stepan". The output shows that "stepan" can't vacuum "foo" until we grant vacuum privilege on "foo" to "stepan".
As "postgres" role create a new role called "petro".
Grant "pg_vacuum_all_tables" role to "petro".
Now we can see that "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.

Summary

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.