Inherit default privileges: every table created by *any member of* role A is readable by role B

Question

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

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

• Alter default privileges for role
• PostgreSQL Role Configuration

Answer 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.

Answer 2

You may use an Event Trigger: First create the function:

CREATE FUNCTION public.on_create_table_funct()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF SECURITY DEFINER
AS $BODY$

DECLARE
    sch text;
BEGIN
FOR sch IN SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg%' AND nspname NOT LIKE 'information%'
    LOOP
    EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO user_name $$, sch);
    EXECUTE format($$ ... other grants ...  $$, sch);
    END LOOP;
END;
$BODY$;

ALTER FUNCTION public.on_create_table_funct()
    OWNER TO postgres;

GRANT EXECUTE ON FUNCTION public.on_create_table_funct() TO postgres;
GRANT EXECUTE ON FUNCTION public.on_create_table_funct() TO PUBLIC;

Then create the trigger:

CREATE EVENT TRIGGER set_grants_on_table_creation ON DDL_COMMAND_END
    WHEN TAG IN ('CREATE TABLE')
    EXECUTE PROCEDURE public.on_create_table_funct();

ALTER EVENT TRIGGER set_grants_on_table_creation 
    OWNER TO postgres;