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.
Table of Contents
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:
|
1 2 |
security=# CREATE USER demo LOGIN; CREATE ROLE |
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:
|
1 2 3 4 5 6 7 |
security=# c security demo You are now connected to database 'security' as user 'demo'. security=> SELECT current_user; current_user -------------- demo (1 row) |
“current_user” will return the database user which is currently connected.
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.
|
1 2 3 |
security=> CREATE TABLE foo (id int); ERROR: permission denied for schema public LINE 1: CREATE TABLE foo (id int); |
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:
|
1 2 3 4 |
security=> c security postgres You are now connected to database 'security' as user 'postgres'. security=# GRANT ALL ON SCHEMA public TO demo; GRANT |
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:
|
1 2 3 4 |
security=# c security demo You're now connected to database 'security' as user 'demo'. security=> CREATE TABLE foo (id int); CREATE TABLE |
The table will belong to the “demo” user who created the table:
|
1 2 3 4 5 6 |
security=> d List of relations Schema | Name | Type | Owner -------+------+-------+------- public | foo | table | demo (1 row) |
Now, only the user "demo" and the database owner can create objects in the public schema.
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.
+43 (0) 2622 93022-0
office@cybertec.at
Thank you for sharing this update. It was very useful.
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
Great it works for me
Fails for me - are there other permissions needed for the user?