Search Postgresql Archives

Alter the default access privileges of the public schema by the db owner

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux