CYBERTEC Logo

PostgreSQL ERROR: permission denied for schema public

11.2022 / Category: , / Tags: | | |

In PostgreSQL 15, a fundamental change took place which is relevant to every user who happens to work with permissions: The default permissions of the public schema have been modified. This is relevant because it might hurt you during application deployment. You need to be aware of how it may affect you.

Creating users

Many people work as superusers only. This is not recommended and can lead to serious security issues. I recommend creating separate users to run your application. In PostgreSQL, you can create a new user using the CREATE USER or the CREATE ROLE command. The difference between these two options is that CREATE USER sets the LOGIN privilege directly while CREATE ROLE will set this attribute to NOLOGIN.
In this example, you'll create a “demo” user, as shown in the next listing:

Once this is done, you can reconnect to the database. The c command is a good way to do that if you happen to use psql. If you're running some other client, reconnect using the new user:

“current_user” will return the database user which is currently connected.

The public schema in PostgreSQL 15 and privilege escalation attacks

In PostgreSQL 14 and in prior versions, by default anybody can create a table. The new table will simply end up in the PUBLIC schema. The problem with this approach is that the public schema can quickly turn into a trash can containing all kinds of used and unused tables - which is neither desirable nor recommended. An even bigger problem is security: almost any privilege escalation attack on a PostgreSQL database requires the attacker to create malicious objects. See for example this blog about abusing SECURITY DEFINER functions for more details. The public schema is the perfect vector for such a privilege escalation attack. Therefore PostgreSQL has made a major leap forward and changed this behavior. In version 15, only the database owner can create objects in the public schema.

Check out the following listing:

PostgreSQL will error out and tell you that you don't have permissions to create something inside the PUBLIC schema without explicitly specifying who is allowed to do that beforehand. It is now necessary to grant permissions to a user explicitly. Here's how it works:

How to grant permissions on the PUBLIC schema

Let's connect to PostgreSQL as a superuser and set USAGE + CREATE = ALL permissions on the PUBLIC schema. Once this is done, you can go ahead and create objects in this schema:

The table will belong to the “demo” user who created the table:

Now, only the user "demo" and the database owner can create objects in the public schema.

Finally…

To see how to deal with closing the security hole in PostgreSQL versions prior to v15, see my blog about common security issues.

For more info on PostgreSQL security, there is no way around encrypting client / server connections using SSL in PostgreSQL. I created a blog post about this important topic and invite you to read it.

 


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
Subscribe
Notify of
guest
4 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jocimar Lopes
Jocimar Lopes
1 year ago

Thank you for sharing this update. It was very useful.

Sony AK
1 year ago

This is help me a lot, even I ask ChatGPT and cannot cover the diff of PostgreSQL 15.x when creating grant access to public schema haha

Muhammed Haris
Muhammed Haris
1 year ago

Great it works for me

gamesbook
gamesbook
7 months ago

Fails for me - are there other permissions needed for the user?

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


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

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    4
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram