1

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 reference schema.


Question

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):

These questions are similar to mine, but haven't been answered:

These have some discussion of what I'm trying to do, but no solutions are offered:

1

You have researched well, and there is no way to get what you want.

So maybe you should change your requirements.

My suggestion is that all these users are members of a role (let's call it tableowner), and only that role has the CREATE privilege on the schemas.

So in order to create, alter or drop a table, the users first have to use SET ROLE tableowner.

Then all tables are owned by that role, and a single ALTER DEFAULT PRIVILEGES FOR ROLE tableowner ... will do the trick.

  • Do you have reason to believe what I want could not be built with a custom extension? – combinatorist Oct 2 at 17:12
  • Also, I like your idea (especially enforcing the role by removing other permissions). But, my biggest complaint is that it hides who actually created (and owns) each table. I think I'd rather write a create a new feature for postgres. – combinatorist Oct 2 at 17:14
  • 1
    I don't think this could be done with an extension, since it changes the way the permission system works. You'd have to modify PostgreSQL itself. I think that the chances of such a modification to be accepted are rather slim, but you are welcome to try. Maybe you should invest your time in writing an event trigger that records the information in a table. The session_user function will tell you the original login role. – Laurenz Albe Oct 3 at 5:53
  • Ok, interesting. Thanks for that thorough answer, @Laurenz! – combinatorist Oct 3 at 15:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.