If I do this ...

    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.


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:

  • I just realized that the [ IN SCHEMA schema_name [, ...] ] portion of the syntax is optional so that you set default privileges on all relations you create. That helps my use case where want some teams to read everything, but won't work for some other use cases. It still doesn't solve the default inheritance problem either. postgresql.org/docs/14/sql-alterdefaultprivileges.html Commented Oct 29, 2021 at 3:03

2 Answers 2


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? Commented Oct 2, 2019 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. Commented Oct 2, 2019 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. Commented Oct 3, 2019 at 5:53
  • Ok, interesting. Thanks for that thorough answer, @Laurenz! Commented Oct 3, 2019 at 15:02

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

CREATE FUNCTION public.on_create_table_funct()
    RETURNS event_trigger
    LANGUAGE 'plpgsql'
    COST 100

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

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
    EXECUTE PROCEDURE public.on_create_table_funct();

ALTER EVENT TRIGGER set_grants_on_table_creation 
    OWNER TO postgres;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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