the question comes basically down to this: how can i securely create new databases on postgres, meaning that by default nobody has any privileges on new databases and all access must be explicitly defined.
i tried various resources in the internet but can not get a clear answer. i tried this on fedora 17 linux with postgres 9.1, but eventually would use it on redhat 6.3 with postgres 8.4.
the process:
- install postgres, run initdb, start server
- connect locally with postgres super user
- create new user u1 with options createdb and createrole
- disconnect and reconnect as u1
- create database db1
- create user u2
- disconnect and reconnect as u2
- create table t1 in db1
i would not expect that last step to succeed. u2 has not been given any privileges on db1 and also no privileges show up when querying the database or schema. to my understanding u2 can create tables on db1, because of the public schema and the default privileges for the build-in public role. if i want to revoke them i can only do so on the database but not on the schema, because that is owned by postgres. so eventually i have to revoke those privileges with a super user for every new database i make. i can not delegate it to the owner of the database and also i must not forget to do it for every new database.
the default would allow every user to spam the public schema in any new database. this is dangerous because other users may by default work on the public schema and not check that tables are already there and just use the existing ones owned by some stranger.
how can i fix these insecure defaults?
