Postgres Role Default Permissions

Aug 2, 2018 · 125 words · 1 minute read databases • postgres

If you’re using roles and schemas in Postgres you might encounter a problem when adding new objects to the schema with one role and trying to access them from another. For example if you create new tables with an admin role but want to read from them with a more restricted role.

You can run into this problem even if you’ve run something like

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;

because this command only applies to the objects (in this case tables) in the schema that have already been created.

The trick is to also run

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name>
GRANT SELECT ON TABLES TO <role_name>;

so that the role’s default permissions are the same as you’ve granted them.