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