Alter default privileges vs new schemas

Started by Francisco Reyesover 9 years ago1 messagesgeneral
Jump to latest
#1Francisco Reyes
lists@natserv.net

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general