Обсуждение: BUG #18160: first create table show "ERROR: permission denied for schema public", next create table works

The following bug has been logged on the website:

Bug reference:      18160
Logged by:          Pawel Ziewiecki
Email address:      pawel.ziewiecki@gmail.com
PostgreSQL version: 15.3
Operating system:   aws rds postgresql
Description:

create table (failed) + alter table (table not exist) + create table success
in public schema (?)

psql -h database-imenago.cahxcxwmeghl.eu-central-1.rds.amazonaws.com -p 5432
-U postgres
Password for user postgres:
psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
compression: off)
Type "help" for help.

postgres=> create user u1 with createdb password 'u1pwd';
CREATE ROLE
postgres=> \c postgres u1
Password for user u1:
psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
compression: off)
You are now connected to database "postgres" as user "u1".
postgres=> create database u1db with owner u1;
CREATE DATABASE
postgres=> \c u1db
psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
compression: off)
You are now connected to database "u1db" as user "u1".
u1db=> create table public.t1 (id int);
ERROR:  permission denied for schema public
LINE 1: create table public.t1 (id int);
                     ^
u1db=> alter table public.t1 owner to u1;
ERROR:  relation "public.t1" does not exist
u1db=> create table public.t1 (id int);
CREATE TABLE
u1db=> \d
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t1   | table | u1
(1 row)

On Wed, 2023-10-18 at 17:01 +0000, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference:      18160
> Logged by:          Pawel Ziewiecki
> Email address:      pawel.ziewiecki@gmail.com
> PostgreSQL version: 15.3
> Operating system:   aws rds postgresql
> Description:       
>
> create table (failed) + alter table (table not exist) + create table success
> in public schema (?)
>
> psql -h database-imenago.cahxcxwmeghl.eu-central-1.rds.amazonaws.com -p 5432
> -U postgres
> Password for user postgres:
> psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> compression: off)
> Type "help" for help.
>
> postgres=> create user u1 with createdb password 'u1pwd';
> CREATE ROLE
> postgres=> \c postgres u1
> Password for user u1:
> psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> compression: off)
> You are now connected to database "postgres" as user "u1".
> postgres=> create database u1db with owner u1;
> CREATE DATABASE
> postgres=> \c u1db
> psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384,
> compression: off)
> You are now connected to database "u1db" as user "u1".
> u1db=> create table public.t1 (id int);
> ERROR:  permission denied for schema public
> LINE 1: create table public.t1 (id int);
>                      ^
> u1db=> alter table public.t1 owner to u1;
> ERROR:  relation "public.t1" does not exist
> u1db=> create table public.t1 (id int);
> CREATE TABLE
> u1db=> \d
>        List of relations
>  Schema | Name | Type  | Owner
> --------+------+-------+-------
>  public | t1   | table | u1
> (1 row)
>

On Wed, 2023-10-18 at 17:01 +0000, PG Bug reporting form wrote:
> Operating system:   aws rds postgresql
>
> postgres=> create database u1db with owner u1;
> CREATE DATABASE
> postgres=> \c u1db
> psql (15.4 (Debian 15.4-1.pgdg120+1), server 15.3)
> You are now connected to database "u1db" as user "u1".
> u1db=> create table public.t1 (id int);
> ERROR:  permission denied for schema public
> LINE 1: create table public.t1 (id int);
>                      ^
> u1db=> alter table public.t1 owner to u1;
> ERROR:  relation "public.t1" does not exist
> u1db=> create table public.t1 (id int);
> CREATE TABLE
> u1db=> \d
>        List of relations
>  Schema | Name | Type  | Owner
> --------+------+-------+-------
>  public | t1   | table | u1
> (1 row)

I cannot reproduce that on real PostgreSQL.

Perhaps you should ask Amazon if they modified the permission system somehow.
If that's a bug, I'd say it is not a PostgreSQL bug.

Yours,
Laurenz Albe

On Wed, 2023-10-18 at 22:15 +0200, Laurenz Albe wrote:

[large quote without anything else]

Sorry, I hit "send" too soon.

Yours,
Laurenz Albe