| 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: | Whole Thread | Raw Message | 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 |