By default, PostgreSQL grants a little bit too much to users of the database cluster by default.
Here's how to tighten up the privileges of a newly-created cluster.
- drop schema public -- from postgres database
This database only exists so that users who want to create or drop databases have something to connect to. There shouldn't be anything created in this database, so drop the public schema that exists by default.
- revoke all on database postgres from public
Users with createdb can be granted explicit connect privileges instead.
- drop schema public -- from template1 database
A database created for user alice (and owned by them) will have a public schema owned by postgres. This schema allows all users to create objects within it. But the default privileges mean that only postgres can drop the schema. This feels a bit odd, after all it's alice's database! If alice wants to use the public schema then change its owner accordingly--but it's better to drop the schema and get alice to create their own schemas for their applications. This can't be done to template0 because it doesn't allow connections (and bypassing that restriction would probably confuse things that expect to find the public schema in databases cloned from template0).
- revoke all on database foo from public -- on non-default databases
Users can be granted explicit connect privileges instead. This has to be done for each newly-created database (I don't know how to set the default acl of newly-created databases).
- revoke all on database template0 from public
By default, PUBLIC is granted CONNECT (although datallowconn is f so in practice connections can't succeed).
- revoke all on database template1 from public
By default, public is granted CONNECT.
Useful references:
DDL Privileges reference - in particular the Summary of Access Privileges table for the hardcoded default privileges for each type of object, which are in effect if psql shows nothing in the Access privileges column