From: | Francisco Reyes <lists(at)natserv(dot)net> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Alter default privileges vs new schemas |
Date: | 2016-08-24 22:19:26 |
Message-ID: | 598bc8e4-2a51-11bb-5219-04c6923ba98f@natserv.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Theron Luhn | 2016-08-24 22:47:16 | Understanding Postgres Memory Usage |
Previous Message | David G. Johnston | 2016-08-24 20:39:43 | Re: Forward declaration of table |