ROLE based DEFAULT PRIVILEGES

[James Sewell]

Hello,

I have a database in which I want to grant permission so that when ANY
table is created by a member of role_a SELECT access is granted on it to
ALL members of role_b.

I had thought that:

ALTER DEFAULT PRIVILEGES FOR ROLE role_a GRANT SELECT ON TABLES TO role_b;

or

ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO role_b;

Would achieve this, but it seems that the first creates default permission
records ONLY for tables created by role_a (not members of role_a) and the
second creates default permission records for tables created by the
Postgres user (or whoever you are connected to psql as).

Reading the documentation again, this is actually what is stated.

Is there a way to achieve this? I want role_a to maintain this level of
access as new tables are created (all creators will be in role_b).

James Sewell
Solutions Architect
_____________________________________

[image:
Loading Image...]

Level 2, 50 Queen St,
Melbourne, VIC, 3000

P: 03 8370 8000 F: 03 8370 8099 W: www.lisasoft.com

[Stephen Frost]

Post by James Sewell
Is there a way to achieve this? I want role_a to maintain this level of
access as new tables are created (all creators will be in role_b).

Sadly, no. It's exactly what I was originally hoping for with the
DEFAULT PRIVILEGES capability, but it's a non-trivial problem (what do
you do when there are conflicting sets of default privileges for a given
login role because they belong to multiple other roles...?).

What you'll need to do is grant the defauly privileges explicitly for
the account which is logging in / creating the tables. You can specify
the user using:

ALTER DEFAULT PRIVILEGES FOR myuser IN SCHEMA myschema GRANT SELECT ON
TABLES TO role_a;

Or similar, but you'll need to do that for every role in role_b and
update the default privs as you add/remove users from role_b.

Thanks,

Stephen