This post was originally published in 2023 and was updated in 2025.
Security is always a moving target, and PostgreSQL 15 makes important changes that close off a long-standing risk: default privileges on the public schema. For years, any new user could create objects in the public schema unless you explicitly revoked their rights. That default behavior could open the door to unauthorized changes or compliance violations if overlooked.
PostgreSQL 15 addresses this by revoking the CREATE privilege on the public schema for all users except the database owner. This update shifts ownership and access control in a way that strengthens database security out of the box.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
postgres=# create database d1; CREATE DATABASE postgres=# postgres=# c d1 You are now connected to database "d1" as user "postgres". d1=# d1=# dn List of schemas Name | Owner --------+------------------- public | pg_database_owner (1 row) |
When we create a table without specifying the schema name, it gets created in the schema, which is set as per the search_path. By default, The first part of search_path defines the schema with the same name as the current user, and the second part of search_path refers to the public schema.
|
1 2 3 4 5 |
postgres=# show search_path; search_path ----------------- "$user", public (1 row) |
Postgres looks for the schemas sequentially according to the list mentioned in the search_path, so when we execute create table command, Postgres creates the table in the first schema mentioned in the search_path. If it is not present, it creates it in the following schema.
Similarly, if the schema name is not specified in the select query, Postgres will search for tables within the named schema according to the search_path.
Public Schema security upgrade in PostgreSQL 15
Up to Postgres 14, whenever you create a database user, by default, it gets created with CREATE and USAGE privileges on the public schema.
It means that until Postgres 14, any user can write to the public schema until you manually revoke the user’s create privilege on the public schema.
Starting with PostgreSQL 15, the CREATE privilege on public schema is revoked/removed from all users except the database owner.
In Postgres 15, now new users cannot create tables or write data to Postgres public schema by default. You have to grant create privilege to the new user manually.
The usage privilege on the public schema for the new users is still present in Postgres 15, like in Postgres 14 and previous versions.
The example below shows that a new user (test1) can create a table in Postgres 14 without granting any privileges.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
postgres=# conninfo You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432". postgres=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row) postgres=# postgres=# postgres=# show search_path; search_path ----------------- "$user", public (1 row) postgres=# postgres=# create table department (empID int); CREATE TABLE postgres=# insert into department values (10); INSERT 0 1 postgres=# |
The example below shows that Postgres 15 only allows new users (test1) to create tables by granting them create privileges on the public schema.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
postgres=# conninfo You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432". postgres=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row) postgres=# show search_path; search_path ----------------- "$user", public (1 row) postgres=# create table department (empID int); ERROR: permission denied for schema public LINE 1: create table department (empID int); postgres=# conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# postgres=# grant create on schema public to test1; GRANT postgres=# postgres=# conninfo You are connected to database "postgres" as user "test1" via socket in "/var/run/postgresql" at port "5432". postgres=# create table department (empID int); CREATE TABLE postgres=# |
The following example shows that the usage privilege on the public schema for the new users is still present in Postgres 15, like in Postgres 14 and previous versions.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
postgres=# conninfo You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432". postgres=# postgres=# create table transport ( vehicleno int); CREATE TABLE postgres=# insert into transport values (25); INSERT 0 1 postgres=# postgres=# grant select on transport to test1; GRANT postgres=# q -bash-4.2$ psql -d postgres -U test1 Password for user test1: psql (15.3) Type "help" for help. postgres=# select * from transport; vehicleno ----------- 25 (1 row) postgres=# |
Public schema ownership changes in PostgreSQL 15
In Postgres 14 and previous versions, by default, the public schema is owned by the bootstrap superuser (postgres), but from Postgres 15, ownership of the public schema has been changed to the new pg_database_owner role. It enables every database owner to own the database’s public schema.
The below example shows the ownership changes between Postgres 14 and Postgres 15.
Postgres 14
|
1 2 3 4 5 6 |
postgres=# dn List of schemas Name | Owner --------+---------- public | postgres (1 row) |
Postgres 15
|
1 2 3 4 5 6 7 |
postgres=# dn List of schemas Name | Owner --------+------------------- public | pg_database_owner (1 row) postgres=# |
Visit the links below for further details about the Postgres schemas.
PostgreSQL 15’s changes to public schema privileges are a reminder that security is never “set and forget.” Every new release introduces adjustments that help you close gaps and stay compliant, but only if you know where to look and how to apply them.
If compliance and data protection are top priorities for your PostgreSQL environment, you’ll want to go deeper than schema permissions. Explore our resource How Percona for PostgreSQL Meets Compliance and Security Standards to see how you can align PostgreSQL with today’s toughest regulations while keeping full control of your deployment.
how do we revoke the default privilege that allows all user to create a table on public schema? I have tried below but not working:
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public, readonly;