Re: ROLE based DEFAULT PRIVILEGES

* James Sewell (james(dot)sewell(at)lisasoft(dot)com) wrote:
> 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