Alter default privileges vs new schemas

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

Browse pgsql-general by date

  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