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.