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.

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

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?

@Aristotle:

you don’t need set role:

$ CREATE USER other  IN ROLE test;
CREATE ROLE

and then:

=$ psql -U other -d test
$ CREATE TABLE public.q ();
CREATE TABLE

Also – you could have tested it in like 10 seconds, instead of asking and waiting for answer: https://www.depesz.com/2019/05/15/how-to-play-with-upcoming-unreleased-postgresql/

Do you mean the

CREATE TABLE

in your example will create objects that are owned by

test

? Even though I am connected as

other

? How does that work?

@Aristotle:

You do realize that instead of asking what will happen you can simply test it? Just for you I remade test database and ran:

=$ psql -U other -d test
 
$ CREATE TABLE public.q ();
CREATE TABLE
 
$ \d
       List OF relations
 Schema │ Name │ TYPE  │ Owner 
────────┼──────┼───────┼───────
 public │ q    │ TABLE │ other
(1 ROW)

So, no, it will NOT be owned by test. It will be owner by other, but since other is “in” role test, they can make tables in the db that allows table creation for test.

Comments are closed.