Permissions for runtime user on all objects in schema (including future objects)

Question

In PostgreSQL 10 - when using an "owner user" (login role owning a schema and all tables, not used by the application at runtime) and a "runtime" user I can GRANT Select/Update/Delete permissions on all existing tables in the schema to the runtime user:

GRANT SELECT ON ALL TABLES IN SCHEMA owner TO runtime;

But this only applies to existing objects and not new tables which might get created later on.

Is there a way to avoid granting on all new objects by way of inheriting roles or schema permissions or similar?

Answer 1

Yes. The key word is DEFAULT PRIVILEGES.

ALTER DEFAULT PRIVILEGES FOR ROLE owner_user IN SCHEMA owner 
GRANT SELECT ON TABLES TO runtime;

Grants the specifies privileges for all specified objects created in the future, by the specified role to the other specified role.

I specified the target_role (owner_user) explicitly to avoid ambiguity. Else, quoting the manual:

If FOR ROLE is omitted, the current role is assumed.

Related:

• Grant all on a specific schema in the db to a group role in PostgreSQL
• How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?

And don't forget access to sequences if you have any serial columns. The first linked answer has instructions.

• eckes: Thanks thats what I was looking for. from the documentaiotn lionk I can see that I could also skip "FOR owner_user" and it would apply to all entries in the schema.
• Erwin Brandstetter: @eckes: That's a misunderstanding. See clarification above.
• Erwin Brandstetter: @eckes: BTW, pgAdmin III has a bug, displaying the FOR target_user clause incorrectly. Details: postgresql.org/message-id/flat/… You are not the first to be confused by this. :)