25

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?

Community
  • 1
  • 1
juliomalegria
  • 24,229
  • 14
  • 73
  • 89
  • I was trying to achieve this too but I think this could cause some kind of diamond problem. If a user is member of more than one group role, which default privileges should be granted? – Bastien Aug 16 '16 at 14:35
  • Trying very similar thing, but involving also SCHEMA and VIEWS, granting SELECT rights to the_reader , for future created VIEW(s) https://dba.stackexchange.com/questions/238766/postgresql-9-user-cannot-select-from-view-inside-a-schema-as-default-privile . In mine, I just use users (not groups) and I use a schema as "userspace", where a manager can write any view. – Gromish May 22 '19 at 18:23

1 Answers1

20

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

Patrick
  • 29,357
  • 6
  • 62
  • 90
  • 3
    Very helpful! Just to piggy back on this - the sticking point for me was this line: "You can change default privileges only for objects that will be created by yourself or by roles that you are a member of." So really, `ALTER DEFAULT PRIVILEGES` really means `ALTER DEFAULT PRIVILEGES --(on objects created by the current user)` – wils484 Jun 10 '15 at 18:49