pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18411: Unable to create database with owner on AWS RDS
8+ messages / 5 participants
[nested] [flat]
* BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-03-27 17:32 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: PG Bug reporting form @ 2024-03-27 17:32 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: myanodress@gmail.com
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-03-27 21:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2024-03-27 21:00 UTC (permalink / raw)
To: myanodress@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-03-28 01:25 Andrey Lizenko <lizenko79@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Andrey Lizenko @ 2024-03-28 01:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: myanodress@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-03-28 01:45 David G. Johnston <david.g.johnston@gmail.com>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-03-28 01:45 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; myanodress@gmail.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-03-28 05:20 Andrey Lizenko <lizenko79@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andrey Lizenko @ 2024-03-28 05:20 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; myanodress@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-03-28 05:31 David G. Johnston <david.g.johnston@gmail.com>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2024-03-28 05:31 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; myanodress@gmail.com <myanodress@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-04-17 14:42 Myka Dresser <myanodress@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Myka Dresser @ 2024-04-17 14:42 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Andrey Lizenko <lizenko79@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* BUG #18411: Unable to create database with owner on AWS RDS
@ 2024-04-17 14:49 David G. Johnston <david.g.johnston@gmail.com>
parent: Myka Dresser <myanodress@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: David G. Johnston @ 2024-04-17 14:49 UTC (permalink / raw)
To: Myka Dresser <myanodress@gmail.com>; +Cc: Andrey Lizenko <lizenko79@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-04-17 14:49 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-27 17:32 BUG #18411: Unable to create database with owner on AWS RDS PG Bug reporting form <noreply@postgresql.org>
2024-03-27 21:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-03-28 01:25 ` Andrey Lizenko <lizenko79@gmail.com>
2024-03-28 01:45 ` David G. Johnston <david.g.johnston@gmail.com>
2024-03-28 05:20 ` Andrey Lizenko <lizenko79@gmail.com>
2024-03-28 05:31 ` David G. Johnston <david.g.johnston@gmail.com>
2024-04-17 14:42 ` Myka Dresser <myanodress@gmail.com>
2024-04-17 14:49 ` David G. Johnston <david.g.johnston@gmail.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