Hi everyone
I'm looking for a way to let a role which created a new database (the
database owner) change (remove) the default access privileges of the
public schema, which allows everyone to use and create objects within
this schema. I do not want to give the role the SUPERUSER option.
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
Basically, I have an administrative role (<ADMIN-ROLE>) with CREATEROLE,
CREATEDB and NOSUPERUSER options set. This role creates databases and
grants minimal privileges to an unprivileged role (<USER-ROLE>) whereas
everyone else should have no access to objects within this database.
The administrative role needs to be able to to the following:
CREATE DATABASE "<DATABASE>" OWNER "<ADMIN-ROLE>" ENCODING...;
REVOKE ALL ON DATABASE "<DATABASE>" FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE "<DATABASE>" TO "<USER-ROLE>";
GRANT ALL ON SCHEMA public TO "<ADMIN-ROLE>";
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO "<USER-ROLE>";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE,
DELETE ON TABLES TO "<USER-ROLE>";
All the grants/revoks on the public schema fail because the role is not
the owner of the public schema.
To circumvent this I've tried the following:
Created a new template database (as a superuser) which the owner of the
public schema set to the <ADMIN-ROLE> and use this template for all
database creations. This solves the grant/revoke problem on the public
schema, but the role is unable to create databases with different
collation settings (new collation (...) is incompatible with the
collation of the template database). And there are a lot of different
collation settings needed.
Dropping the public schema beforehand on the template is also no option
as I have to use template0.
Is there a way to either let the owner of a database own the public
schema by default, or to ignore the collation settings on the template
database (it will never have any data preloaded, only the ownership of
the public schema changed)? Or maybe there is a complete other approach
to solve this problem.
Many thanks and best regards
Christian
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
