Waiting for PostgreSQL 15 – Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.

On 10th of September 2021, Noah Misch committed patch:

Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.
 
This switches the default ACL to what the documentation has recommended
since CVE-2018-1058.  Upgrades will carry forward any old ownership and
ACL.  Sites that declined the 2018 recommendation should take a fresh
look.  Recipes for commissioning a new database cluster from scratch may
need to create a schema, grant more privileges, etc.  Out-of-tree test
suites may require such updates.
 
Reviewed by Peter Eisentraut.
 
Discussion: https://postgr.es/m/20201031163518.GB4039133@rfd.leadboat.com

This is big change.

PostgreSQL always allowed, by default, any user to connect to any database, and create new tables (and views, and so on) in “public" schema. The one that is created by default.

Some people saw it as security issue, some didn't.

Some deleted public schema, or just tightened privileges.

Not any more. Since Pg 15 (assuming the change will not get rolled back), by default, non-superuser accounts will not be able to create tables in public schema of databases they don't own.

Let's see it:

$ create database x;
CREATE DATABASE
 
$ create user test;
CREATE ROLE
 
$ create database test with owner test;
CREATE DATABASE

So, I made test user, and two databases: “x", owner by superuser (pgdba in my case), and “test" owned by user test. Creation of tables looks like this:

=$ psql -U test -d x -c 'create table a (b int)'
ERROR:  permission denied for schema public
LINE 1: create table a (b int)
                     ^
 
=$ psql -U test -d test -c 'create table a (b int)'
CREATE TABLE

Of course you can still grant all privileges to public schema, to retain previous behavior, but, by default, creation of objects in databases that belong to others will not work.

Cool, lots of people will be happier. Thanks a lot to all involved.

2 thoughts on “Waiting for PostgreSQL 15 – Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner.”

  1. If you CREATE USER other IN ROLE test, you connect to DB test as other, and you SET ROLE test, does creating objects (in schema public, with owner test) succeed?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.