Date: Tue, 18 Apr 2023 07:08:46 -0700
From: Noah Misch <noah@leadboat.com>
To: moray.henderson@om.org, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17901: Unexpected 'permission denied' error depending on
 which template used to create database
Message-ID: <20230418140846.GA1477639@rfd.leadboat.com>
References: <17901-93eacb513e503f43@postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <17901-93eacb513e503f43@postgresql.org>
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/20230418140846.GA1477639%40rfd.leadboat.com>
Precedence: bulk

On Mon, Apr 17, 2023 at 11:33:28AM +0000, PG Bug reporting form wrote:
> In PostgreSQL 15.2 a user with 'CREATEDB NOINHERIT' permission can create a
> new database and tables from template1, but receives 'permission denied'
> when creating tables in a database created from template0. Is that expected
> behaviour? (I'm a little hazy on exactly how 'noinherit' is supposed to be
> used: I... inherited it!)
> 
> The cluster was initialised with ICU locales:
> .\initdb --locale-provider icu --icu-locale en-GB --locale en-GB-x-icu -A
> md5 -U postgres -W -D 'C:\ProgramData\PostgreSQL\15\data'
> I do not believe template1 has been modified from its default since the
> cluster was initialised.
> 
> postgres=> SELECT * FROM version();
>                           version
> ------------------------------------------------------------
>  PostgreSQL 15.2, compiled by Visual C++ build 1914, 64-bit
> (1 row)
> postgres=# CREATE ROLE dbadmin LOGIN CREATEDB NOINHERIT PASSWORD
> 'Passw0rd';
> CREATE ROLE
> postgres=# \c - dbadmin
> Password for user dbadmin:
> You are now connected to database "postgres" as user "dbadmin".
> postgres=> CREATE DATABASE d0 TEMPLATE template0;
> CREATE DATABASE
> postgres=> CREATE DATABASE d1 TEMPLATE template1;
> CREATE DATABASE
> postgres=> \c d1
> You are now connected to database "d1" as user "dbadmin".
> d1=> CREATE TABLE t (i int);
> CREATE TABLE
> d1=> \c d0
> You are now connected to database "d0" as user "dbadmin".
> d0=> CREATE TABLE t (i int);
> ERROR:  permission denied for schema public
> LINE 1: create table t (i int);

It is expected.  Your d0 public schema has permissions like this:

[local] test=# \dn+ public
                                       List of schemas
  Name  │       Owner       │           Access privileges            │      Description       
────────┼───────────────────┼────────────────────────────────────────┼────────────────────────
 public │ pg_database_owner │ pg_database_owner=UC/pg_database_owner↵│ standard public schema
        │                   │ =U/pg_database_owner                   │ 

Role dbadmin is automatically a member of pg_database_owner while in a
database it owns.  Since role dbadmin is NOINHERIT, the privileges of
pg_database_owner don't normally apply.  The privileges are accessible with
SET ROLE, so do this:

\c d0 dbadmin
SET ROLE pg_database_owner;
GRANT ALL ON SCHEMA public TO dbadmin;
SET ROLE dbadmin;
CREATE TABLE t (i int);

Regarding d1 behaving differently, I'm guessing this instance was upgraded
from v14 or earlier.  In d1, "\dn+ public" will look different, because it has
the access privileges migrated from the earlier version.  The v14 access
privileges were more permissive.