https://www.depesz.com/2021/09/10/waiting-for-postgresql-15-revoke-public-create-from-public-schema-now-owned-by-pg_database_owner/ Skip to content =$ | Menu * Why upgrade PG? * explain.D.C * paste.D.C * Waiting for ... + ... PostgreSQL 15 + ... PostgreSQL 14 + ... PostgreSQL 13 + ... PostgreSQL 12 + ... PostgreSQL 11 + ... PostgreSQL 10 + ... PostgreSQL 9.6 + ... PostgreSQL 9.5 + ... PostgreSQL 9.4 + ... PostgreSQL 9.3 + ... PostgreSQL 9.2 + ... PostgreSQL 9.1 + ... PostgreSQL 9.0 + ... PostgreSQL 8.5 + ... PostgreSQL 8.4 * Projects * Contact 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. Posted on 2021-09-10|Tags pg15, postgresql, privileges, public, security, waiting| 2 thoughts on "Waiting for PostgreSQL 15 - Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner." 1. [e179] Aristotle says: 2021-09-12 at 17:48 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. [d5ec] depesz says: 2021-09-13 at 07:29 @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/ Leave a Reply Cancel reply Your email address will not be published. [ ] [ ] [ ] [ ] [ ] [ ] [ ] Comment [ ] Name [ ] Email [ ] Website [ ] [Post Comment] [ ] [ ] [ ] [ ] [ ] [ ] [ ] [ ] This site uses Akismet to reduce spam. Learn how your comment data is processed. Post navigation Previous Previous post: How to get advisory lock in shell? Search Search for: [ ] Search Follow me * Comments RSS * Posts RSS Popular Posts * Waiting for PostgreSQL 15 - Revoke PUBLIC CREATE from public schema, now owned by pg_database_owner. 5.2k views | 2 comments * Explaining the unexplainable - part 3 189 views | 0 comments * CHAR(x) vs. VARCHAR(x) vs. VARCHAR vs. TEXT - UPDATED 2010-03-03 175 views | 0 comments * Explaining the unexplainable - part 2 132 views | 0 comments * Why is UPSERT so complicated? 128 views | 0 comments * Explaining the unexplainable 107 views | 0 comments * how to insert data to database - as fast as possible 102 views | 0 comments * Starting with Pg - where is the config? 91 views | 0 comments * Waiting for PostgreSQL 11 - Fast ALTER TABLE ADD COLUMN with a non-NULL default 90 views | 0 comments * How to play with upcoming, unreleased, PostgreSQL? 89 views | 0 comments PostgreSQL * Documentation * Explain Analyze analyzer * IRC help channel * Mailing Lists search * PG Planet * PostgreSQL Home Page About me * CPAN * GitLab * Linked In * Why upgrade PG? * explain.D.C * paste.D.C * Waiting for ... + ... PostgreSQL 15 + ... PostgreSQL 14 + ... PostgreSQL 13 + ... PostgreSQL 12 + ... PostgreSQL 11 + ... PostgreSQL 10 + ... PostgreSQL 9.6 + ... PostgreSQL 9.5 + ... PostgreSQL 9.4 + ... PostgreSQL 9.3 + ... PostgreSQL 9.2 + ... PostgreSQL 9.1 + ... PostgreSQL 9.0 + ... PostgreSQL 8.5 + ... PostgreSQL 8.4 * Projects * Contact