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

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(at)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(dot)g(dot)johnston(at)gmail(dot)com>
wrote:

> On Wed, Mar 27, 2024 at 6:26 PM Andrey Lizenko <lizenko79(at)gmail(dot)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