ALTER DEFAULT PRIVILEGES in PostgreSQL

Versions: PostreSQL 11

At first glance, managing users access in PostgreSQL is easy, you simply execute a CREATE USER, give him some grants, assign a role, and often that's all. However, after some time "permission denied" errors can appear as new objects are created and not owned by the user. To mitigate the maintenance burden for that case, PostgreSQL proposes ALTER DEFAULT privileges operator.

To see the problem, let's start by executing the PostgreSQL locally:

# run the container
docker run --name alter_default_test -e POSTGRES_PASSWORD=root -e POSTGRES_USER=root -e POSTGRES_DB=test_alter_default -d postgres:11

# access the container
docker exec -ti ff76b7cad1fbe45c4b7dfaa47f47d419d8a6170c9c584e394bd552eeba0ff139 psql -d test_alter_default

Context setup

Inside the container I start by adding some test objects:

test_alter_default=# CREATE SCHEMA test_permissions;
CREATE SCHEMA
test_alter_default=# CREATE USER user_a;
CREATE ROLE
test_alter_default=# CREATE USER user_b;
CREATE ROLE
test_alter_default=# CREATE USER user_c;
CREATE ROLE
test_alter_default=#

The first thing is the disabled access to test_permissions schema:

test_alter_default=# CREATE TABLE test_permissions.root_table (id INT);
CREATE TABLE
test_alter_default=# SET SESSION AUTHORIZATION 'user_a';
SET
test_alter_default=> SELECT * FROM test_permissions.root_table;
ERROR:  permission denied for schema test_permissions
LINE 1: SELECT * FROM test_permissions.root_table;

As you can see, the users don't have access to the schema. That's why I will grant it to them here:

test_alter_default=> SET SESSION AUTHORIZATION 'root';
SET
test_alter_default=# GRANT USAGE ON SCHEMA test_permissions TO user_a;
GRANT
test_alter_default=# GRANT USAGE ON SCHEMA test_permissions TO user_b;
GRANT
test_alter_default=# GRANT USAGE ON SCHEMA test_permissions TO user_c;
GRANT
test_alter_default=# SET SESSION AUTHORIZATION 'user_a';
SET
test_alter_default=> SELECT * FROM test_permissions.root_table;
ERROR:  permission denied for table root_table
test_alter_default=>

It still doesn't work but this time the problem is the lack of SELECT permissions for the tables. This issue can be easily fixed with these GRANT commands:

test_alter_default=> SET SESSION AUTHORIZATION 'root';
SET
test_alter_default=# GRANT SELECT ON ALL TABLES IN SCHEMA test_permissions TO user_a;
GRANT
test_alter_default=# SET SESSION AUTHORIZATION 'user_a';
SET
test_alter_default=> SELECT * FROM test_permissions.root_table;
 id
----
(0 rows)
test_alter_default=> SET SESSION AUTHORIZATION 'root';
SET
test_alter_default=# GRANT SELECT ON ALL TABLES IN SCHEMA test_permissions TO user_b;
GRANT
test_alter_default=# GRANT SELECT ON ALL TABLES IN SCHEMA test_permissions TO user_c;
GRANT

And since I will also create new tables with my 3 users, I will also allow them to do so with:

test_alter_default=# GRANT CREATE ON SCHEMA test_permissions TO user_a;
GRANT
test_alter_default=# GRANT CREATE ON SCHEMA test_permissions TO user_b;
GRANT
test_alter_default=# GRANT CREATE ON SCHEMA test_permissions TO user_c;
GRANT

Test new objects

Let's move now in the test scenario and create another table, but this time with user_a. Since I GRANTED access, I would expect that the other users can read this table without problems:

test_alter_default=# SET SESSION AUTHORIZATION 'user_a';
SET
test_alter_default=> CREATE TABLE test_permissions.user_a_table_1 (id INT);
CREATE TABLE
test_alter_default=> SET SESSION AUTHORIZATION 'user_b';
SET
test_alter_default=> SELECT * FROM test_permissions.user_a_table_1;
ERROR:  permission denied for table user_a_table_1
test_alter_default=>

But unfortunately, this table is not exposed to the rest of users. Actually, it's normal because the GRANT we executed before applies only to all created objects at a given moment. For any new objects, it's the object's author who will need to set the permissions explicitly. Unless we use ALTER DEFAULT PRIVILEGES command:

test_alter_default=> SET SESSION AUTHORIZATION 'user_a';
SET
test_alter_default=> ALTER DEFAULT PRIVILEGES IN SCHEMA test_permissions GRANT SELECT ON TABLES TO user_b;
ALTER DEFAULT PRIVILEGES
test_alter_default=> CREATE TABLE test_permissions.user_a_table_2 (id INT);
CREATE TABLE
test_alter_default=> SET SESSION AUTHORIZATION 'user_b';
SET
test_alter_default=> SELECT * FROM test_permissions.user_a_table_2;
 id
----
(0 rows)

In the above example you can see that user_a explicitly grants SELECT privileges on all the tables and views that will be created to the user_b. That's why user_b is able to issue SELECT query without any extra GRANT execution. However, the code from previous snippet has a small trap. To see it, let's create new tables as user_c and try to query them as user_b:

test_alter_default=> SET SESSION AUTHORIZATION 'user_c';
SET
test_alter_default=> CREATE TABLE test_permissions.user_c_table_1 (id INT);
CREATE TABLE
test_alter_default=> SET SESSION AUTHORIZATION 'user_b';
SET
test_alter_default=> SELECT * FROM test_permissions.user_c_table_1;
ERROR:  permission denied for table user_c_table_1

Wait! You didn't say that user_b can access everything in the schema? Indeed but I didn't mention that ALTER DEFAULT PRIVILEGES applies only to the objects created by the user executing this query! So when user_a executed it, it allowed user_b to see all tables and views he'll create in the future. To solve that issue, we can add FOR USER in our alter command to say that this applies for all objects of a particular user:

test_alter_default=> SET SESSION AUTHORIZATION 'root';
SET
test_alter_default=# ALTER DEFAULT PRIVILEGES FOR USER user_c IN SCHEMA test_permissions GRANT SELECT ON TABLES TO user_b;
ALTER DEFAULT PRIVILEGES

Here we granted access to user_b for all tables and views created by user_c. Let's see if it works by creating a new table and reading it without executing any extra grant:

test_alter_default=# SET SESSION AUTHORIZATION 'user_c';
SET
test_alter_default=> CREATE TABLE test_permissions.user_c_table_2 (id INT);
CREATE TABLE
test_alter_default=> SET SESSION AUTHORIZATION 'user_b';
SET
test_alter_default=> SELECT * FROM test_permissions.user_c_table_2;
 id
----
(0 rows)

This behavior is pretty clearly highlighted in the documentation:

You can change default privileges only for objects that will be created by yourself or by roles that you are a member of. The privileges can be set globally (i.e., for all objects created in the current database), or just for objects created in specified schemas. Default privileges that are specified per-schema are added to whatever the global default privileges are for the particular object type.

And that's all for this short lesson about privileges. If you'll ever use the concept of output invalidation pattern where the data is written to partitioned tables and exposed through a view, you may need to ALTER DEFAULT PRIVILEGES in order to let your users to query views without problems.