Update 14-Dec-2022: Please see this post, the content here is not anymore up to date.

While PostgreSQL 15.1 was released some weeks ago PostgreSQL 16 is already under active development. Two days ago two interesting features have been committed which give you more flexibility when it comes to vacuum and analyze. Before we dive into that lets have a look at how that works currently in PostgreSQL 15 and before.

To start with, we need to users, one of them having a simple table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
postgres=# select version();
                                                       version                                                       
----------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
 
postgres=# create user u1 with login password 'u1';
CREATE ROLE
postgres=# create user u2 with login password 'u2';
CREATE ROLE
postgres=# grant all on schema public to u1;
GRANT
postgres=# \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create table t1 ( a int );
CREATE TABLE
postgres=>

When it comes to vacuum and analyze who is allowed to perform these operations on the t1 table, which is owned by u1? Of course the owner itself:

1
2
3
4
5
6
7
8
9
10
11
postgres=> select current_user;
 current_user
--------------
 u1
(1 row)
 
postgres=> analyze t1;
ANALYZE
postgres=> vacuum t1;
VACUUM
postgres=>

The only other user who is allowed to do that is the superuser, but not u2 or any other user which is not a superuser:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=> \c postgres u2
You are now connected to database "postgres" as user "u2".
postgres=> analyze t1;
WARNING:  skipping "t1" --- only table or database owner can analyze it
ANALYZE
postgres=> vacuum t1;
WARNING:  skipping "t1" --- only table or database owner can vacuum it
VACUUM
postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# analyze t1;
ANALYZE
postgres=# vacuum t1;
VACUUM

That’s all the options you have currently. PostgreSQL 16 will give you more flexibility with this, so lets do the same setup in the current development version of PostgreSQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# select version();
                                               version                                               
------------------------------------------------------------------------------------------------------
 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)
postgres=# create user u1 with login password 'u1';
CREATE ROLE
postgres=# create user u2 with login password 'u2';
CREATE ROLE
postgres=# grant all on schema public to u1;
GRANT
postgres=# \c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create table t1 ( a int );
CREATE TABLE
postgres=>

By default, the behavior is exactly the same as in PostgreSQL 15. Only the owner of the table and a superuser are allowed to vacuum and analyze the t1 table. But what you can do starting with PostgreSQL 16 is, to grant these operations to another user:

1
2
3
4
5
6
7
8
9
10
11
postgres=> grant analyze on t1 to u2;
GRANT
postgres=> grant vacuum on t1 to u2;
GRANT
postgres=> \c postgres u2
You are now connected to database "postgres" as user "u2".
postgres=> analyze t1;
ANALYZE
postgres=> vacuum t1;
VACUUM
postgres=>

Of course this does not grant the right to see the data nor does it give any other permissions:

1
2
3
postgres=> select * from t1;
ERROR:  permission denied for table t1
postgres=>

In addition two this, PostgreSQL 16 will come with two additional predefined roles:

1
2
3
4
5
6
postgres=> select * from pg_roles where rolname in ('pg_vacuum_all_tables','pg_analyze_all_tables');;
        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid 
-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
 pg_vacuum_all_tables  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4549
 pg_analyze_all_tables | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4550
(2 rows)

These roles, when granted to someone, also give the permissions for the vacuum and analyze operations.

Post Views: 4,340
Thumbnail [60x60]
by
Daniel Westermann