From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: moray.henderson@om.org
Subject: BUG #17901: Unexpected 'permission denied' error depending on which template used to create database
Date: Mon, 17 Apr 2023 11:33:28 +0000
Message-ID: <17901-93eacb513e503f43@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17901
Logged by: Moray Henderson
Email address: moray.henderson@om.org
PostgreSQL version: 15.2
Operating system: Windows 10 Pro 22H2
Description:
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);
^
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, moray.henderson@om.org
Subject: Re: BUG #17901: Unexpected 'permission denied' error depending on which template used to create database
In-Reply-To: <17901-93eacb513e503f43@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox