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.