A wonderful PostgreSQL feature: default privileges

Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the user which owns the objects creates new objects? Then you will need to grant those to the second user as well. In PostgreSQL there is an easier solution. Lets go …

Again we start by creating two users each with its own schema:

postgres=# create user a with login password 'a';
CREATE ROLE
postgres=# create schema a authorization a;
CREATE SCHEMA
postgres=# alter user a set search_path=a;
ALTER ROLE
postgres=# create user b with login password 'b';
CREATE ROLE
postgres=# create schema b authorization b;
CREATE SCHEMA
postgres=# alter user b set search_path=b;
ALTER ROLE
postgres=# du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 a         |                                                            | {}
 b         |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# dn
  List of schemas
  Name  |  Owner   
--------+----------
 a      | a
 b      | b
 public | postgres
(3 rows)

User “a” shall be the one owning the objects:

postgres=# c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t1 ( a int );
CREATE TABLE
postgres=> create table t2 ( a int );
CREATE TABLE
postgres=> insert into t1 (a) values (1);
INSERT 0 1
postgres=> insert into t2 (a) values (2);
INSERT 0 1
postgres=> d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 a      | t1   | table | a
 a      | t2   | table | a
(2 rows)

When you want to give user “b” access to these tables you could do:

postgres=> grant select on table t1 to b;
GRANT
postgres=> grant select on table t2 to b;
GRANT

From now on user “b” should be able to select from the two tables owned by user “a”, right?:

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

This is not how it works in PostgreSQL. What you need to do is this:

postgres=> c postgres a
You are now connected to database "postgres" as user "a".
postgres=> grant usage on schema a to b;
GRANT

This allows user “b” access to the schema “a” (remember that a user and a schema are different things in PostgreSQL):

postgres=> c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t1;
 count 
-------
     1
(1 row)

postgres=> select count(*) from a.t2;
 count 
-------
     1
(1 row)

What happens now when user “a” creates another object:

postgres=> c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t3 as select * from t1;
SELECT 1
postgres=> d
       List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 a      | t1   | table | a
 a      | t2   | table | a
 a      | t3   | table | a
(3 rows)

Will user “b” be able to select data from it?

postgres=> c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t3;
ERROR:  permission denied for relation t3

Of course not. The “usage” on a schema grants only access to that schema but not access to the objects in the schema. When we want user “b” being able to select from all tables in schema “a” even when user “a” creates new objects then we can modify the default privileges:

postgres=# c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# alter default privileges in schema a grant select on tables to b;
ALTER DEFAULT PRIVILEGES

Should user “b” now be able to select from the “t3” table in schema “a”?

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

postgres=> select count(*) from a.t3;
ERROR:  permission denied for relation t3
postgres=> 

No. When you modify the default privileges this will affect only objects created after your modification. Lets create a new table with user “a” in schema “a”:

postgres=> c postgres a
You are now connected to database "postgres" as user "a".
postgres=> create table t4 as select from t1;
SELECT 1

As this table was created after the modification to the default privileges user “b” is allowed to select from it automatically:

postgres=> c postgres b
You are now connected to database "postgres" as user "b".
postgres=> select count(*) from a.t4;
 count 
-------
     1
(1 row)

When you check the link to the documentation above you’ll notice that you can not only grant select on tables but much more. Hope this helps …