insecure defaults with postgres public schema?

Question

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:

1. install postgres, run initdb, start server
2. connect locally with postgres super user
3. create new user u1 with options createdb and createrole
4. disconnect and reconnect as u1
5. create database db1
6. create user u2
7. disconnect and reconnect as u2
8. 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?

Answer 1

You can revoke the public schema "create" permission in the template1 database, and this will be propagated to any newly-created database by default: databases are created by copying template1 (or some other database marked as a template, but template1 is the default).

Actually, you might want to simply drop the public schema altogether in this situation, and delegate the job if creating it (if desired) to the database owner.

Answer 2

I used this article to advantage when I needed to a secure database:

Total security in a PostgreSQL database