Common security issues prior to PostgreSQL 15

10.2013 / Category: / Tags:

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.

Unexpected results

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:

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.

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:

So far everything is going according to plan. But, let us take a look at the next example:

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:

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:

This will lead exactly to the kind of behavior we expected before:

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.


In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram