Be careful with “public” in PostgreSQL

PostgreSQL comes with the “public” schema by default and we always recommend to drop it for PostgreSQL versions up to 14. The reason is, that everybody who is able to connect to a database, by default, can create objects in the public schema. This will not anymore be possible starting with PostgreSQL 15. You can check the release notes for that change, it is this one: “Remove PUBLIC creation permission on the public schema (Noah Misch)”.

In addition to the “public” schema, there is also the special role “public”. This role can be used to grant something to all other roles, but this also comes with some misunderstanding of what is possible by default. By default, the public role has this permissions:

 username | object_type |    object_name     |            array            
----------+-------------+--------------------+-----------------------------
 public   | DATABASE    | postgres           | {CONNECT,TEMPORARY}
 public   | DATABASE    | template1          | {CONNECT}
 public   | LANGUAGE    | internal           | {USAGE}
 public   | LANGUAGE    | c                  | {USAGE}
 public   | LANGUAGE    | sql                | {USAGE}
 public   | LANGUAGE    | plpgsql            | {USAGE}
 public   | SCHEMA      | pg_catalog         | {USAGE}
 public   | SCHEMA      | public             | {USAGE}
 public   | SCHEMA      | information_schema | {USAGE}
(9 rows)

There are some “usage” privileges, which seem to be fine, but there is also the “connect” privilege against the “postgres” and the “template1” databases (if you are wondering how to get these privileges out of the system, then you should check the various “has_***” functions in the documentation).

Last week we’ve been contacted by a customer who ran into this:

postgres=# create database d;
CREATE DATABASE
postgres=# create user u with login password 'u';
CREATE ROLE
postgres=# revoke connect on database d from u;
REVOKE

Simple setup: One database, one user and a revoke of the connect privilege from that database from the user. Will that user be able to connect to the database? Simple test from outside the database host:

dwe@ltdwe:~$ psql -h 192.168.100.241 -p 5432 -U u d
psql (14.5, server 16devel)
WARNING: psql major version 14, server major version 16.
         Some psql features might not work.
Type "help" for help.

d=> 

Works just fine, even if the connect privilege was revoked (of course I’ve modified pg_hba.conf before). Why is that? The reason is the “public” role. If we check the privileges of that role once more, we can see this:

 username | object_type |    object_name     |            array
----------+-------------+--------------------+-----------------------------
 public   | DATABASE    | postgres           | {CONNECT,TEMPORARY}
 public   | DATABASE    | template1          | {CONNECT}
 public   | DATABASE    | d                  | {CONNECT,TEMPORARY}
 public   | LANGUAGE    | internal           | {USAGE}
 public   | LANGUAGE    | c                  | {USAGE}
 public   | LANGUAGE    | sql                | {USAGE}
 public   | LANGUAGE    | plpgsql            | {USAGE}
 public   | SCHEMA      | pg_catalog         | {USAGE}
 public   | SCHEMA      | public             | {USAGE}
 public   | SCHEMA      | information_schema | {USAGE}
(10 rows)

As the “public” role has the connect privilege by default, this is automatically available to all other users, and this is the reason why it still works. What you should do, is this:

postgres=# revoke connect on database d from public;
REVOKE

Once connect is revoked from public, the user is not anymore able to connect:

dwe@ltdwe:~$ psql -h 192.168.100.241 -p 5432 -U u d
psql: error: connection to server at "192.168.100.241", port 5432 failed: FATAL:  permission denied for database "d"
DETAIL:  User does not have CONNECT privilege.

Keep in mind, that privileges which are granted to “public” are valid for all users.