New predefined roles for PostgreSQL 14

Time is moving fast and PostgreSQL 14 is already in beta 2. PostgreSQL 14 will ship with a lot of new features and in this post will look at a smaller one: There are three new predefined roles: pg_read_all_data, pg_write_all_data and pg_database_owner. While it seems to be obvious what the first two roles are about, the third one might sound strange at the beginning. Let’s have a look.

We’ll start with a sample setup, as always:

postgres=# create user u1 with login password 'u1';
CREATE ROLE
postgres=# create user u2 with login password 'u2';
CREATE ROLE
postgres=# create user u3 with login password 'u3';
CREATE ROLE
postgres=# create schema s1;
CREATE SCHEMA
postgres=# create schema s2;
CREATE SCHEMA
postgres=# alter schema s1 owner to u1;
ALTER SCHEMA
postgres=# alter schema s2 owner to u2;
ALTER SCHEMA
postgres=# c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create table s1.t1 ( a int, b text, c date);
CREATE TABLE
postgres=> insert into s1.t1 select i,i::text,now() from generate_series(1,1000000) i;
INSERT 0 1000000
ostgres=> c postgres u2
You are now connected to database "postgres" as user "u2".
postgres=> create table s2.t1 ( a int, b text, c date);
CREATE TABLE
postgres=> insert into s2.t1 select i,i::text,now() from generate_series(1,1000000) i;
INSERT 0 1000000
postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".

We’ve created three users (u1,u2,u3) and two schemas (s1,s2). The s1 schema is owned by u1 and the s2 schema is owned by u2. Both users (u1,u2) have a table with some data in their schemas. The u3 user currently does not have anything.

When we connect as user u3 we of course do not have any permissions on the schemas we created above:

postgres=# c postgres u3
You are now connected to database "postgres" as user "u3".
postgres=> select count(*) from s1.t1;
ERROR:  permission denied for schema s1
LINE 1: select count(*) from s1.t1;
                             ^
postgres=> select count(*) from s2.t1;
ERROR:  permission denied for schema s2
LINE 1: select count(*) from s2.t1;
                             ^
postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".

Granting pg_read_all_data to u3 gives as read access to all objects:

postgres=# grant pg_read_all_data to u3;
GRANT ROLE
postgres=# c postgres u3
You are now connected to database "postgres" as user "u3".
postgres=> select count(*) from s1.t1;
  count  
---------
 1000000
(1 row)

postgres=> select count(*) from s2.t1;
  count  
---------
 1000000
(1 row)

Writing data or creating object is not yet possible:

postgres=> select current_user;
 current_user 
--------------
 u3
(1 row)

postgres=> create table s1.t2(a int);
ERROR:  permission denied for schema s1
LINE 1: create table s1.t2(a int);
                     ^
postgres=> create table s2.t2(a int);
ERROR:  permission denied for schema s2
LINE 1: create table s2.t2(a int);

postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".

This is what pg_write_all_data seems to be about:

postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant pg_write_all_data to u3;
GRANT ROLE
postgres=# c postgres u3
You are now connected to database "postgres" as user "u3".
postgres=> create table s1.t2(a int);
ERROR:  permission denied for schema s1
LINE 1: create table s1.t2(a int);
                     ^
postgres=> create table s2.t2(a int);
ERROR:  permission denied for schema s2
LINE 1: create table s2.t2(a int);

This role does not grant permissions to create new objects but it grant permission to write data into existing relations:

postgres=> select current_user;
 current_user 
--------------
 u3
(1 row)

postgres=> insert into s1.t1 values (-1,'xxx',now() );
INSERT 0 1
postgres=> insert into s2.t1 values (-1,'xxx',now() );
INSERT 0 1

As roles are global objects that works for all databases in a cluster:

postgres=> c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# create database db1;
CREATE DATABASE
postgres=# c db1
You are now connected to database "db1" as user "postgres".
db1=# create schema s1;
CREATE SCHEMA
db1=# create table s1.t1(a int);
CREATE TABLE
db1=# insert into s1.t1 values(1);
INSERT 0 1
db1=# c db1 u3
You are now connected to database "db1" as user "u3".
db1=> select * from s1.t1;
 a 
---
 1
(1 row)

db1=> insert into s1.t1 values(2);
INSERT 0 1

This is really nice if you want to give a specific user or role read and/or write permissions for all objects in all databases.

Finally the third predefined role: pg_database_owner. What is this about? Let’s grant that role to the u3 user:

db1=> c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# grant pg_database_owner to u3;
ERROR:  role "pg_database_owner" cannot have explicit members

A new predefined role that can not be granted explicitly? Looking at the role definition it can not do much:

postgres=# x
Expanded display is on.
postgres=# select * from pg_roles where rolname = 'pg_database_owner';
-[ RECORD 1 ]--+------------------
rolname        | pg_database_owner
rolsuper       | f
rolinherit     | t
rolcreaterole  | f
rolcreatedb    | f
rolcanlogin    | f
rolreplication | f
rolconnlimit   | -1
rolpassword    | ********
rolvaliduntil  | 
rolbypassrls   | f
rolconfig      | 
oid            | 6171

What is it there for then? The answer is here.