In September 2021, a patch for the upcoming PostgreSQL version 15 was committed which introduces a visible change for users: the CREATE privilege for the public schema is no longer set by default. This is a recommendation from CVE-2018-1058.
What does that mean for the average (non-superuser) user?
In PostgreSQL 14:
postgres=# SELECT version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE
postgres=# CREATE DATABASE priv_test;
CREATE DATABASE
postgres=# \c priv_test
You are now connected to database "priv_test" as user "ads".
priv_test=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+-------+-------------------+------------------------
public | ads | ads=UC/ads +| standard public schema
| | =UC/ads |
(1 row)
We see that PUBLIC (the second line in Access privileges) has USAGE (U) and CREATE (C) privileges. A regular user can create a table in the public schema:
priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
priv_test=> CREATE TABLE priv_test (id INT);
CREATE TABLE
priv_test=> \dp priv_test
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------+-------+-------------------+-------------------+----------
public | priv_test | table | | |
(1 row)
This is how the public schema privileges look in PostgreSQL 15:
postgres=# SELECT version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE
postgres=# CREATE DATABASE priv_test;
CREATE DATABASE
postgres=# \c priv_test
You are now connected to database "priv_test" as user "ads".
priv_test=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+-------------------+----------------------------------------+------------------------
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
| | =U/pg_database_owner |
(1 row)
The C (CREATE) is missing. And the CREATE TABLE for a regular user is no longer working by default:
priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
priv_test=> CREATE TABLE priv_test (id INT);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE priv_test (id INT);
But how is this change handled in upgrades?
Continue reading "Changes to the public schema in PostgreSQL 15 and how to handle upgrades"