PostgreSQL has a sophisticated security system capable of handling complex policies and user permissions are an essential component of every productive setup many people rely on. However, over the past couple of years I have noticed that many users fall into the same trap: The “public” schema.
To make life easier in general, PostgreSQL provides a thing called the “public” schema, which can be found in every database. The thing here is that the public schema can be used by everybody. This can lead to some interesting and unexpected behavior. Let us assume we are connected to a database as superuser:
security=# CREATE ROLE john NOSUPERUSER LOGIN; CREATE ROLE security=# CREATE TABLE t_test (id int4); CREATE TABLE
We have created a new user as well as a table belonging to the superuser. Let us see what the new user is capable of doing.
$ psql security -U john psql (9.3.1) Type "help" for help. security=> SELECT * FROM t_test; ERROR: permission denied for relation t_test
As expected our new user is not allowed to read from existing tables and he is not allowed to create a new schema or even drop an existing:
security=> CREATE SCHEMA sales; ERROR: permission denied for database security security=> DROP DATABASE security; ERROR: must be owner of database security
So far everything is going according to plan. But, let us take a look at the next example:
security=> CREATE TABLE t_disaster (id int4); CREATE TABLE
The user can easily create a new table. Most people would have expected some “permission denied” here. However, this does not happen because the public schema can be utilized by everybody. \d reveals what is going on here:
security=> \d List of relations Schema | Name | Type | Owner --------+------------+-------+------- public | t_disaster | table | john public | t_test | table | hs (2 rows)
We can see that the new relation belongs to John.
Closing the hole
To make sure that things like this can’t happen, we have to remove permissions from the public schema. The superuser can execute the following command to achieve that:
security=# REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE
This will lead exactly to the kind of behavior we expected before:
security=> CREATE TABLE t_forbidden (id int4); ERROR: no schema has been selected to create in security=> CREATE TABLE public.t_forbidden (id int4); ERROR: permission denied for schema public
Don’t be misled by the error message in the first example. This is only due to the fact that PostgreSQL does not know where to put the table. Prefixing the table properly will do the job and reveal the real cause of the problem and lead to the expected result.