If I do this ...
ALTER DEFAULT PRIVILEGES FOR ROLE engineering IN SCHEMA reference GRANT SELECT ON TABLES to analytics;
I get ...
This syntax runs and has a valid meaning. The way it is implemented, any time the
engineering role creates a table (in the
reference schema), then any member of the
reporting role will automatically have read access to it. But for that to work, you must be logged in or impersonating (
set session authorization,
set role etc) as the
engineering role when you create the table.
I understand the rationale behind the way postgres implemented this. They want each role/user to set their own defaults. The default is a convenient shorthand so the user doesn't have to set permissions on every table they create. Postgres even allows someone to set the defaults for any role they can impersonate (and hence super users can set defaults for everyone). This is pretty powerful, but postgres didn't want to make it too powerful. They still wanted people to have to issue a separate command for each user's defaults. They wanted people to think hard about every user to whom they are giving this convenience.
But I want ...
But, I've already thought hard about it and I do want to set up those conveniences for a whole team. I want a single command where every user in the engineering team inherits these default privileges. Then, every time one of them creates a table, it would automatically be readable by every member of reporting. In fact, that's how I interpreted the syntax when I first found out I could include the
FOR ROLE engineering clause. But defaults are not inherited.
Alternatively, I would also be interested in a solution where no matter who (no matter what team they are in) creates a table in the
reference schema, it will be readable by all the members of the
reporting role. So in this variation, I can still open up the schema to
reporting even if I can't (directly) control who gives those permissions (and why would I want to?). If I have this solution, I can still effectively limit it to the
engineering team members if they are the only ones with create privileges in the
Am I missing something (such as an alternate syntax)? Or is there some other work-around?
I am open to any solution (even building a custom extension) so long as it does not fall under one of the two categories below:
The solution doesn't really set a "default"
i.e. every time a member of the
engineering team signs in or creates a table, they have to do some additional command to handle the permissions (such as change to the engineering role when they start the session)
The solution does not really inherit to the whole team
i.e. you have to set the default privileges for every team member in separate commands (and remember to do it whenever a new member is added
I've done my homework
None of the following questions (which Stack Exchange suggested based on the text of my question) has an answer for my nuanced need.
These questions are basic (didn't know about default permissions or how to use them as intended):
- How to GRANT SELECT by default to B, on tables created by A?
- How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?
These questions are similar to mine, but haven't been answered:
- Default privileges granted only if table created by one superuser
- How can I grant permissions on tables created in the future to members of a particular role?
These have some discussion of what I'm trying to do, but no solutions are offered: