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. It’s recommended to create 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 tooling, 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 user which is currently used.
Using the PUBLIC schema in PostgreSQL 15
In PostgreSQL 14 and in prior versions, creating a table would be possible. The new table would simply end up in the public schema and all would be good. 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. Therefore PostgreSQL has made a major leap forward and changed this behavior.
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:
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 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)
Finally…
If you want to learn more about PostgreSQL, and if you happen to be interested in security in general, 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.