Azure Postgresql 16 CREATE ROLE REPLICATION lacks membership

Question

Hello all.

We have Azure postgresql flexible server 16.

I'm connected as main admin user (using password) named rixo_sa

If I'm trying to create role without params, role i s created with default membership of current user rixo_sa.

But the problem is when try to create role with some parameter enabled e.g. REPLICATION:

CREATE ROLE rixo_role_for_test2 REPLICATION;

Then role is created without membership to rixo_sa and is blocked for every alter or drop:

GRANT rixo_role_for_test2 TO rixo_etl;

[42501] ERROR: permission denied to grant role "rixo_role_for_test2" Detail: Only roles with the ADMIN option on role "rixo_role_for_test2" may grant this role.

What can I do with those roles, which are blocked and cannot be dropped?

DROP ROLE rixo_role_for_test2;

[42501] ERROR: permission denied to drop role Detail: Only roles with the CREATEROLE attribute and the ADMIN option on role "rixo_role_for_test2" may drop this role.

Answer 1

Hi @LiborOndruek-3549,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

As i understand that you are facing permission issues while creating and dropping roles in Azure PostgreSQL Flexible Server.

When you create a role with a parameter enabled, such as REPLICATION, the role is created without membership to the rixo_sa role. This is because the rixo_sa role does not have the CREATEROLE attribute and the ADMIN option on the rixo_role_for_test2 role.

To grant the rixo_role_for_test2 role to the rixo_etl role, you need to have the ADMIN option on the rixo_role_for_test2 role. You can grant the ADMIN option to the rixo_sa role by running the following command:

GRANT rixo_role_for_test2 TO rixo_sa WITH ADMIN OPTION;

After granting the ADMIN option to the rixo_sa role, you can grant the rixo_role_for_test2 role to the rixo_etl role:

GRANT rixo_role_for_test2 TO rixo_etl;

Regarding the issue with dropping the role, you need to have the CREATEROLE attribute and the ADMIN option on the rixo_role_for_test2 role to drop it. Since you do not have these permissions, you cannot drop the role. You can try to contact your database administrator to grant you the necessary permissions to drop the role.

For more information, please refer the document: https://www.postgresql.org/docs/16/release-16.html

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 2

Hi Maesh.

My admin user rixo_sa has by default CREATEROLE privilege.

The problem is little bit magical circle. If you want alter role which is not granting admin option of your role, you have to have admin option on this role.

The restriction is by design of Azure Database for PostgreSQL flexible server, where admin user (created by ARM API) has no superuser privileges and is not able to grant roles without admin permissions.