Postgres Role Default Permissions
Aug 2, 2018 · 125 words · 1 minute read
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.