CODEWITHSUNDEEP
postgresqldatabase-permissions
Fred
Fred

Reputation: 4076

How to give access to a database to only specific users?

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.

Upvotes: 1

Views: 60

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246033

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.

Upvotes: 0

Related Questions