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:
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:
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.
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:
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:
How to grant permissions on the PUBLIC schema
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:
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:
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.
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.