Hey all,
I'm a bit confused about the way databases are created in postgresql,
and the permissions that are set for new databases.
First of all I was surprised, that the default database template
(template1) has privileges for the public schema by default. That seems
a bit too permissive to me, hence I revoked the public privileges from
the public schema in temlate1. Further CONNECT and TEMPORARY is granted
to anyone on each new database. Can that behavior be disabled?
The other problem I came across is that after creating a database from
any template, only the database owner is changed, e.g., databases
created from the default template contain the schema public and the
owner is still the original owner. In the default case the public schema
is owned by postgres and the database owner can't do anything against it:
test=> ALTER SCHEMA public OWNER TO newuser;
ERROR: must be owner of schema public
test=> GRANT ALL ON SCHEMA public TO newuser;
ERROR: permission denied for schema public
(newuser is database owner)
Is there any way the database owner can take the ownership of schemas?
If not there seems to be no point in creating a (not completely empty)
database as a non superuser. I think the best would be if the template
owner is replaced for all nested elements on database creation and not
only for the database itself.
In my opinion the defaults are very unintuitive: having public
privileges as default can be dangerous, as it can be overlooked easily.
The command line client is not even showing the default privileges
(=Tc/test) before they have been altered.
Is all the above correct or are there any solutions?
Janis