Thread: BUG #18411: Unable to create database with owner on AWS RDS
The following bug has been logged on the website: Bug reference: 18411 Logged by: Myka Anold Dresser Email address: myanodress@gmail.com PostgreSQL version: 16.2 Operating system: AWS RDS Description: 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? The documentation suggests that both forms should cause an error https://www.postgresql.org/docs/current/sql-grant.html To create an object owned by another role or give ownership of an existing object to another role, you must have the ability to SET ROLE to that role; otherwise, commands such as ALTER ... OWNER TO or CREATE DATABASE ... OWNER will fail.
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=> create role user1 login;
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 | | |
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.
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:
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.
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.