pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17901: Unexpected 'permission denied' error depending on which template used to create database
2+ messages / 2 participants
[nested] [flat]
* BUG #17901: Unexpected 'permission denied' error depending on which template used to create database
@ 2023-04-17 11:33 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2023-04-17 11:33 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: moray.henderson@om.org
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);
^
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #17901: Unexpected 'permission denied' error depending on which template used to create database
@ 2023-04-18 14:08 Noah Misch <noah@leadboat.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Noah Misch @ 2023-04-18 14:08 UTC (permalink / raw)
To: moray.henderson@om.org; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-04-18 14:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-17 11:33 BUG #17901: Unexpected 'permission denied' error depending on which template used to create database PG Bug reporting form <noreply@postgresql.org>
2023-04-18 14:08 ` Noah Misch <noah@leadboat.com>
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