Re: ROLE based DEFAULT PRIVILEGES

[Stephen Frost <sfrost@xxxxxxxxxxx>]

* James Sewell (james.sewell@xxxxxxxxxxxx) 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

Attachment: signature.asc
Description: Digital signature