We've set up a Azure Postgres database and are trying to run our database/schema/tables creations scripts. These script have worked fine on our VM/server based database hosts where we connect to the database as:
sudo -u postgres psql
But for AZ Postgres we connect remotely using:
psql -h mypsql.postgres.database.azure.com -U admuser -d postgres
Connection works fine, then we start our scripts which contain the following, and it fails on the create database step:
postgres=> create user rsm_user with login password 'test123';
CREATE ROLE
postgres=> create role rsm_role;
CREATE ROLE
postgres=> grant rsm_role to rsm_user;
GRANT ROLE
postgres=> create database rsm_db with owner = rsm_role;
ERROR: must be able to SET ROLE "rsm_role"
postgres=> create database rsm_db with owner = rsm_user;
ERROR: must be able to SET ROLE "rsm_user"
Looking at the differences between our local and AZ remote instance we see that is superuser locally is postgres:
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
rsm_user | | {rsm_role}
rsm_role | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
On ZA remote:
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+----------------------------------------------------------------------------------------------------------
admuser | Create role, Create DB | {pg_read_all_settings,pg_read_all_stats,pg_stat_scan_tables,azure_pg_admin,rsm_user,rsm_role}
azure_pg_admin | Cannot login | {pg_monitor}
azuresu | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
replication | Replication | {pg_use_reserved_connections}
rsm_role | Cannot login | {}
rsm_user | | {rsm_role}
The local postgres user seems the equivalent of the remote azuresu user, and the remote admuser that we login as does not have the same level of privileges as postgres.
is there a way to connect as azuresu on the remote instance?
Or is that another step we need to to allow the CREATE DATABASE command to work?
