Postgres 15 will include this change:
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
Currently, CockroachDB matches the old Postgres behavior, but since we want our privileges to be more compatible with Postgres, we should adapt this change too. (Let's just make sure that commit doesn't get reverted before PG 15 is released.)
To resolve this issue: change it so non-superuser accounts are not able to create tables in the public schema of databases they don't own. Make sure to update remapPublicSchemas in backupccl/restore_job.go too. This should be controlled by a cluster setting, which defaults to keeping the old behavior.
This should be the new behavior:
$ create database x;
CREATE DATABASE
$ create user test;
CREATE ROLE
$ create database test with owner test;
CREATE DATABASE
# Quit sql shell
$ 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
Jira issue: CRDB-10011
Epic CRDB-26874
Postgres 15 will include this change:
Currently, CockroachDB matches the old Postgres behavior, but since we want our privileges to be more compatible with Postgres, we should adapt this change too. (Let's just make sure that commit doesn't get reverted before PG 15 is released.)
To resolve this issue: change it so non-superuser accounts are not able to create tables in the
publicschema of databases they don't own. Make sure to updateremapPublicSchemasin backupccl/restore_job.go too. This should be controlled by a cluster setting, which defaults to keeping the old behavior.This should be the new behavior:
Jira issue: CRDB-10011
Epic CRDB-26874