Thread: Remove or alter the default access privileges of the public schema by the database owner

Hi everyone

I'm looking for a way to let a role which created a new database (is 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 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

Hello

Please accept my apologies for the double posting. The original mail was
held off by the Majordomo mailing list software until a mailing list
administrator would allow it to be delivered. Majordomo doesn't like the
string 'remove' within the subject.
Thereupon, I informed the mailing list admins that I will resend the
message with a new subject and that they can safely delete the original
message. Apparently, my notice got overlooked somehow.


Sorry again.
Christian


On 15.10.2013 12:28, Christian Affolter wrote:
> Hi everyone
>
> I'm looking for a way to let a role which created a new database (is 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.

[...]