BUG #18411: Unable to create database with owner on AWS RDS

PG Bug reporting form <noreply@postgresql.org> writes:

Using the postgres user on AWS RDS, execution of

CREATE USER my_user WITH PASSWORD 'my-user-password';
CREATE DATABASE my_database WITH OWNER=my_user;

Results in an error:

ERROR: must be able to SET ROLE "my_user"
SQL state: 42501

However, the following succeeds
CREATE USER my_user WITH PASSWORD 'my-user-password';
CREATE DATABASE my_database;
ALTER DATABASE my_database OWNER TO my_user;

Is this intended behaviour or am I taking advantage of a bug by creating the
database and then setting the OWNER using ALTER DATABASE?

Both things fail for me:

regression=# create user admin with createrole createdb;
CREATE ROLE
regression=# \c - admin
You are now connected to database "regression" as user "admin".
regression=> CREATE USER my_user WITH PASSWORD 'my-user-password';
CREATE ROLE
regression=> CREATE DATABASE my_database WITH OWNER=my_user;
ERROR: must be able to SET ROLE "my_user"
regression=> CREATE DATABASE my_database;
CREATE DATABASE
regression=> ALTER DATABASE my_database OWNER TO my_user;
ERROR: must be able to SET ROLE "my_user"

I suggest taking this up with AWS.

regards, tom lane

AWS uses role rdsadmin for tasks like this with limited default permissions
for other roles.

On Wed, 27 Mar 2024 at 22:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:

PG Bug reporting form <noreply@postgresql.org> writes:

Using the postgres user on AWS RDS, execution of

CREATE USER my_user WITH PASSWORD 'my-user-password';
CREATE DATABASE my_database WITH OWNER=my_user;

Results in an error:

ERROR: must be able to SET ROLE "my_user"
SQL state: 42501

However, the following succeeds
CREATE USER my_user WITH PASSWORD 'my-user-password';
CREATE DATABASE my_database;
ALTER DATABASE my_database OWNER TO my_user;

Is this intended behaviour or am I taking advantage of a bug by creating

the

database and then setting the OWNER using ALTER DATABASE?

Both things fail for me:

regression=# create user admin with createrole createdb;
CREATE ROLE
regression=# \c - admin
You are now connected to database "regression" as user "admin".
regression=> CREATE USER my_user WITH PASSWORD 'my-user-password';
CREATE ROLE
regression=> CREATE DATABASE my_database WITH OWNER=my_user;
ERROR: must be able to SET ROLE "my_user"
regression=> CREATE DATABASE my_database;
CREATE DATABASE
regression=> ALTER DATABASE my_database OWNER TO my_user;
ERROR: must be able to SET ROLE "my_user"

I suggest taking this up with AWS.

regards, tom lane

--
Regards, Andrei Lizenko

On Wed, Mar 27, 2024 at 6:26 PM Andrey Lizenko <lizenko79@gmail.com> wrote:

AWS uses role rdsadmin for tasks like this with limited default
permissions for other roles.

That seems irrelevant to the fact that you cannot accomplish a task using
create database that you can accomplish via alter database. Whatever the
mechanism, that inconsistency doesn't make sense. Both should work or both
should fail.

David J.

Totally from scratch it works w\o rdsadmin:

postgres=> \l+

List of databases
Name | Owner | Encoding | Locale Provider | Collate |
Ctype | ICU Locale | ICU Rules | Access privileges | Size |
Tablespace | Description

-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------+-----------+------------+--------------------------------------------
postgres | postgres | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | | 7724 kB |
pg_default | default administrative connection database
rdsadmin | rdsadmin | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | rdsadmin=CTc/rdsadmin+| No Access |
pg_default |
| | | | |
| | | rdstopmgr=Tc/rdsadmin | |
|
template0 | rdsadmin | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | =c/rdsadmin +| 7561 kB |
pg_default | unmodifiable empty database
| | | | |
| | | rdsadmin=CTc/rdsadmin | |
|
template1 | postgres | UTF8 | libc | en_US.UTF-8 |
en_US.UTF-8 | | | =c/postgres +| 7796 kB |
pg_default | default template for new databases
| | | | |
| | | postgres=CTc/postgres | |
|

postgres=> create role user1 login;

CREATE ROLE

postgres=> alter role user1 with encrypted password 'A123';
ALTER ROLE
postgres=> create database test1;
CREATE DATABASE
postgres=> alter database test1 owner to test1;
ALTER DATABASE

root@nl-oukb-de:~# PGPASSWORD=A123 psql -h

database-1.xxxxxxxxxxxb.eu-central-1.rds.amazonaws.com -U user1 -d
postgres
psql (16.2 (Ubuntu 16.2-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
compression: off)
Type "help" for help.

test1=> \l+ test1

List
of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype |
ICU Locale | ICU Rules | Access privileges | Size | Tablespace |
Description

-------+-------+----------+-----------------+-------------+-------------+------------+-----------+-------------------+---------+------------+-------------
test1 | test1 | UTF8 | libc | en_US.UTF-8 | en_US.UTF-8 |
| | | 7796 kB | pg_default |

test1=> create table test1 (i int);

CREATE TABLE
test1=> insert into test1 values (1);
INSERT 0 1
test1=> select * from test1;
i
---
1
(1 row)
test1=> drop table test1;
DROP TABLE

I suggest checking AWS permissions - IAM and so on.

On Thu, 28 Mar 2024 at 02:46, David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Wed, Mar 27, 2024 at 6:26 PM Andrey Lizenko <lizenko79@gmail.com>
wrote:

AWS uses role rdsadmin for tasks like this with limited default
permissions for other roles.

That seems irrelevant to the fact that you cannot accomplish a task using
create database that you can accomplish via alter database. Whatever the
mechanism, that inconsistency doesn't make sense. Both should work or both
should fail.

David J.

--
Regards, Andrei Lizenko

The convention here is to in-line/bottom post.

On Wednesday, March 27, 2024, Andrey Lizenko <lizenko79@gmail.com> wrote:

Totally from scratch it works w\o rdsadmin:

Your test doesn’t include the failure mode command…create database with
owner.

David J.

Thank you all for your responses, sorry for the delay in replying.

Reading the threads, it appears to me that nobody has tried to reproduce
the issue with the "CREATE DATABASE my_database WITH OWNER=my_user" on an
AWS instance so I am still at a loss as to whether:

- I have hit a bug with the "CREATE DATABASE my_database WITH
OWNER=my_user" command, in which case I am working around it by issuing an
"ALTER DATABASE my_database OWNER TO my_user" command,

or

- I am exploiting a bug in the "ALTER DATABASE my_database OWNER TO
my_user" command in which case I really should look at doing this another
way.

As David J points out, both "CREATE DATABASE my_database WITH
OWNER=my_user" and "ALTER DATABASE my_database OWNER TO my_user" should
fail or both should succeed - I am seeing inconsistent behaviour on AWS
where "CREATE DATABASE" fails whilst "ALTER DATABASE" succeeds.

Myka

On Thu, 28 Mar 2024 at 05:31, David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Wednesday, April 17, 2024, Myka Dresser <myanodress@gmail.com> wrote:

Thank you all for your responses, sorry for the delay in replying.

Reading the threads, it appears to me that nobody has tried to reproduce
the issue with the "CREATE DATABASE my_database WITH OWNER=my_user" on an
AWS instance so I am still at a loss as to whether:

Tom demonstrated it behaves consistently in community PostgreSQL so your
bug report here is closed. You need to open one with AWS RDS.

David J.