https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html
Have a client where their development teams use extensive use of
schemas. We use default privileges like this
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT ON
tables TO ro_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT ON
sequences TO ro_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT, UPDATE,
DELETE, INSERT ON tables TO rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT,
USAGE ON sequences TO rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT
EXECUTE ON functions TO rw_group;
But I can't find a way to grant usage on new schemas. At DB creation I do
grant usage on schema public to rw_group;
grant usage on schema public to ro_group;
Because we revoke everything from public we have to explicitly grant
connect to DB and usage on schemas.
Currently having to manually grant usage when a new schema is created
and wondering if there is a better way.
Postgresql 9.3
