My Azure Server Admin has CreateRole rights disabled for Azure Database for Postgresql Flexible server

Question

In the Azure Database for Postgresql, Recently the CreateRole rights have been revoked for the Server Admin user. Due to this, I am not able to create new users or roles in the system. Please help me to enable the create role access for the Azure Server admin role

Answer 1

Hi Nathar Hussain •,

Welcome to Microsoft Q&A forum.

As I understand, you want to create roles in Azure Database for Postgresql Flexible server.

When you first created your Azure Database for PostgreSQL flexible server instance, you provided a server admin username and password. For more information, you can follow the Quickstart to see the step-by-step approach. Since the server admin user name is a custom name, you can locate the chosen server admin user name from the Azure portal.

The Azure Database for PostgreSQL flexible server instance is created with the three default roles defined. You can see these roles by running the command: SELECT rolname FROM pg_roles;

• azure_pg_admin
• azuresu
• your server admin user

Your server admin user is a member of the azure_pg_admin role. However, the server admin account isn't part of the azuresu role. Since this service is a managed PaaS service, only Microsoft is part of the super user role.

The PostgreSQL engine uses privileges to control access to database objects, as discussed in the PostgreSQL product documentation. In Azure Database for PostgreSQL flexible server, the server admin user is granted these privileges:

• Sign in, NOSUPERUSER, INHERIT, CREATEDB, CREATEROLE

The server admin user account can be used to create more users and grant those users into the azure_pg_admin role. Also, the server admin account can be used to create less privileged users and roles that have access to individual databases and schemas.

How to create more admin users in Azure Database for PostgreSQL flexible server

1. Get the connection information and admin user name. You need the full server name and admin sign-in credentials to connect to your Azure Database for PostgreSQL flexible server instance. You can easily find the server name and sign-in information from the server Overview page or the Properties page in the Azure portal.
2. Use the admin account and password to connect to your Azure Database for PostgreSQL flexible server instance. Use your preferred client tool, such as pgAdmin or psql. If you're unsure of how to connect, see the quickstart
3. Edit and run the following SQL code. Replace your new user name with the placeholder value <new_user>, and replace the placeholder password with your own strong password.

CREATE USER <new_user> CREATEDB CREATEROLE PASSWORD '<StrongPassword!>';

GRANT azure_pg_admin TO <new_user>;

Let us know if you face any issue in this.

Thanks