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.