1

This is probably a silly question and I'm sure it's a problem with my mental model.

Ultimately I want to set privileges on a role such that any other roles in that role have CRUD access to all the current tables and automatically all of the future tables in perpetuity for said database. but ONLY those roles that have been explicitly added to said 'group role'.

It's not clear how to do this.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
Fred
  • 3,786
  • 7
  • 41
  • 52

1 Answers1

0

There is no way to get exactly what you want, but you can get close enough.

Dealing with existing tables is simple enough: just revoke all privileges that have been granted on the tables and grant access to your group role. The command that makes this easy is

GRANT/REVOKE ALL
   ON ALL TABLES IN SCHEMA ... TO/FROM ...;

To deal with future tables, you'll have to restrict the circle of users that may create tables to a few, and for each of them run

ALTER DEFAULT PRIVILEGES FOR ROLE creating_user
   GRANT ALL ON TABLES TO ...;

If you cannot enumerate the users that can create tables, an event trigger running at the end of each CREATE statement may be an alternative.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • If I'm understanding you correctly there's no way to 'set it and forget it' with respect to privileges on tables in a DB, so when creating a new table I'll need to re-add the CRUD privileges to the role for each new table? – Fred Jan 07 '19 at 13:36
  • No, `ALTER DEFAULT PRIVILEGES` takes care of all tables created in the future. **But** you have to run it for each user that can may a table in the future. So if you cannot reasonably limit the circle of database users that create tables, it won't work like you want. An alternative could then be an event trigger that runs at the end of `CREATE TABLE` statements. – Laurenz Albe Jan 07 '19 at 16:13