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:

CategoryTechnote PostgreSQLPrivileges (last edited 2020-02-24 17:51:03 by sam)

© Sam Morris <>.
Content may be distributed and modified providing this notice is preserved.