PostgreSQL – Permissions for Runtime User on All Objects in Schema

permissionspostgresqlpostgresql-10schema

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?

Best Answer

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:

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

Related Question