Avoiding access to the public schema in PostgreSQL

In PostgreSQL every database contains the public schema by default. Every user that gets created and can login is able to create objects there. Here is a little demo: I’ll create a new user named u1 which is allowed to login. No additional privileges are granted:

postgres=# create user u1 login password 'u1';
CREATE ROLE
postgres=# c postgres u1
You are now connected to database "postgres" as user "u1".

From now on this user is able to connect to any database in the cluster and is able to create objects in the public schema:

postgres=> c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create table t1 ( a int );
CREATE TABLE
postgres=> insert into t1 values (1);
INSERT 0 1
postgres=> select schemaname,tablename,tableowner 
             from pg_tables 
            where tablename = 't1';
 schemaname | tablename | tableowner 
------------+-----------+------------
 public     | t1        | u1
(1 row)

postgres=> c edb u1
You are now connected to database "edb" as user "u1".
edb=> create table t1 ( a int );
CREATE TABLE
edb=> select schemaname,tablename,tableowner 
        from pg_tables 
       where tablename = 't1';
 schemaname | tablename | tableowner 
------------+-----------+------------
 public     | t1        | u1
(1 row)

This is probably not what you want as such a user can fill your database and therefore may cause major issues (performance, disk full etc. ). How can we avoid that?

One way to do it is to revoke everything from public:

postgres=# revoke all on schema public from public;
REVOKE

If we now re-connect to the postgres database and try to create a table this will fail:

postgres=# c postgres u1
You are now connected to database "postgres" as user "u1".
postgres=> create table t2 ( a int );
ERROR:  no schema has been selected to create in
postgres=> create table public.t2 ( a int );
ERROR:  permission denied for schema public

The issue with this approach is that if we connect to another database we are still allowed to create tables:

postgres=# c edb u1
You are now connected to database "edb" as user "u1".
edb=> create table t2 ( a int );
CREATE TABLE
edb=> 

Only when we do the same revoke in this database …

postgres=# c edb
You are now connected to database "edb" as user "enterprisedb".
edb=# revoke all on schema public from public;    
REVOKE

.. we are not able to create tables anymore:

edb=# c edb u1
You are now connected to database "edb" as user "u1".
edb=> create table t3 ( a int );
ERROR:  no schema has been selected to create in
edb=> create table public.t3 ( a int );
ERROR:  permission denied for schema public

Seems a little bit complicated. What else can we do? As every new database is created from template1 by default we can do our revokes there:

postgres=# c template1
You are now connected to database "template1" as user "enterprisedb".
template1=# revoke all on schema public from public;
REVOKE

Every new database should have this included already:

postgres=# create database db1;
CREATE DATABASE
postgres=# c db1 u1
You are now connected to database "db1" as user "u1".
db1=> create table t1 ( a int );
ERROR:  no schema has been selected to create in
db1=> create table public.t1 ( a int ); 
ERROR:  permission denied for schema public
db1=> 

Much better.

But remember that it is usually not that easy to connect at all because of pg_hba.conf. When we try to connect from outside:

dwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 db1
psql: FATAL:  no pg_hba.conf entry for host "192.168.22.1", user "u1", database "db1", SSL off

We have much more control here. If we do not grant access to a specific database we will not be able to connect. So we might grant access to the db1 database but not to the postgres database by adding this line to pg_hba.conf:

host     db1             u1                  192.168.22.1/32         md5

Once the server was reloaded or restarted we are able to connect:

dwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 db1
Password for user u1: 
psql (9.3.10, server 9.4.5.12)
WARNING: psql major version 9.3, server major version 9.4.
         Some psql features might not work.
Type "help" for help.

db1=> 

But we are not able to connect to any other database:

dwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 postgres
psql: FATAL:  no pg_hba.conf entry for host "192.168.22.1", user "u1", database "postgres", SSL off
dwe@dwe:~$ psql -h 192.168.22.243 -p 5444 -U u1 template1
psql: FATAL:  no pg_hba.conf entry for host "192.168.22.1", user "u1", database "template1", SSL off

Conclusion: Always make sure that you allow connections from trusted sources only ( via pg_hba.conf ) and think about the public schema before granting access.