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.
Table of Contents
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:
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 |
$ 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:
|
1 2 3 4 5 |
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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 |
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.
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:
|
1 2 3 |
security=# REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE |
This will lead exactly to the kind of behavior we expected before:
|
1 2 3 4 5 |
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.
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.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at