UPDATED July 2023: 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, PostgreSQL provides a thing called the “public” schema, which can be found in every database. Prior to version 15, everybody could create objects in the public schema. This could lead to some interesting and unexpected behavior. (For info about PG v15 and higher, see the last paragraph.) Let’s assume I’m connected to a database as superuser in v14:
security=# CREATE ROLE john NOSUPERUSER LOGIN; CREATE ROLE security=# CREATE TABLE t_test (id int4); CREATE TABLE
I’ve created a new user as well as a table belonging to the superuser. Let’s see what the new user is capable of doing.
$ psql security -U john psql (14.5) 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 one:
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 (PG prior to v15)
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 CREATE 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, reveal the real cause of the problem and lead to the expected result.
PostgreSQL v15: new default for public schema
In PostgreSQL v15 and higher, the default for the public schema has been changed.
That may lead you to receive this same error message – to learn how to fix it, see my blog about ERROR: Permission Denied: schema public.
- For further security information, check out View Permissions and Row-Level Security in PostgreSQL.
- To find out more about schemas, see What is a Schema in PostgreSQL?