Alter default privileges for a group role in PostgreSQL

Question

I have created two group roles in Postgres 9.2: one is called admins and the other is called readers.

The idea is very simple: admins create tables and readers have read access to these tables.

After granting privileges to both group roles everything worked as expected for exisintg objects. But now what about new objects?

So after reading this post I altered the default privileges to grant SELECT privileges to readers for any new table that admins create:

ALTER DEFAULT PRIVILEGES FOR ROLE admins IN SCHEMA public GRANT SELECT ON TABLES TO readers;
ALTER DEFAULT PRIVILEGES FOR ROLE admins IN SCHEMA public GRANT SELECT ON SEQUENCES TO readers;

But apparently, ALTER DEFAULT PRIVILEGES only affects the role itself but not the members of the role. Let me show you.

If I login as userX (a member of admins) and create a new table, no default privileges are granted (and therefore, readers cannot access this table):

test=# CREATE TABLE table1 (name VARCHAR(10)); -- Creating table as userX
test=# \dp table1
                           Access privileges
 Schema |  Name  | Type  | Access privileges | Column access privileges 
--------+--------+-------+-------------------+--------------------------
 public | table1 | table |                   | 

However, the default privileges are granted if I create the table as admins (readers can access this table):

test=# SET ROLE admins;
test=# CREATE TABLE table2 (name VARCHAR(10)); -- Creating table as admins
test=# \dp table2
                             Access privileges
 Schema |  Name  | Type  |   Access privileges   | Column access privileges 
--------+--------+-------+-----------------------+--------------------------
 public | table2 | table | readers=r/admins     +| 
        |        |       | admins=arwdDxt/admins | 

Is there a way to alter the default privileges for ALL members of a group role? Or should I just alter default privileges for each user?

---

UPDATE: In this PostgreSQL forum someone asked a very similar question and the answer was:

Unfortunately I can't see a way to achieve what you want without granting default privileges to everybody involved.

However this question was asked 2 years ago. Is there a solution now?

Answer 1

If a user creates a table then this user becomes the owner of the table. So in your case any default privileges for userX apply, not those of admins. the solution is to SET ROLE admins before creating your table:

SET ROLE admins;
CREATE TABLE ... -- This now applies default privileges of admins
;
RESET ROLE;

More in general, you would want to do this always: Create all tables and views through a group role or some other role not used in daily operations and grant access to the relations to another group role whose privileges are inherited by regular login roles (users). This greatly facilitates security management.

Cheers, Patrick