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.”

  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?

  2. 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?

  3. @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.

Leave a Reply

Your email address will not be published. Required fields are marked *

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