Error dropping role with no members and no admin

Question

Error dropping role with no members and no admin

GregF 10

I have a role that cannot be deleted. It has no members and is not granted to any other roles.

When I try to drop it as my admin user, I receive the error.

ERROR: permission denied to drop role

DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "test_svc_account" may drop this role

We are using Azure database for PostgreSQL 16.4

How can I get rid of this role?

Prasad Chaganti 770 Reputation points Microsoft External Staff Moderator

2025-03-21T19:46:46.54+00:00
Hi GregF,

It looks like you're encountering a permissions issue when trying to drop a role in your Azure PostgreSQL database. This is a common issue, especially with PostgreSQL 16, where roles with the CREATEROLE attribute need the ADMIN OPTION on the role they want to drop12.

Grant the Necessary Permissions:

First, make sure that your admin user has the CREATEROLE attribute and the ADMIN OPTION on the role you want to drop. You can grant these permissions using the following SQL commands:

-- Grant CREATEROLE attribute to your admin user ALTER ROLE your_admin_user CREATEROLE; -- Grant ADMIN OPTION on the role to be dropped GRANT ADMIN OPTION ON ROLE test_svc_account TO your_admin_user;

Once the permissions are granted, you should be able to drop the role:

DROP ROLE test_svc_account;

I hope this information helps. Please do let us know if you have any further queries.
GregF 10 Reputation points

2025-03-21T19:58:38.5766667+00:00

Thanks for the reply.

The problem is that my admin user does not have the ADMIN on option on the role that I need to drop, and there is no way to grant it. There are no admins in the database that have ADMIN on this role.

I created this role with an admin user, but then dropped that user. Leaving this role with no admins (except for maybe "azuresu", but I can't use that account).

Here is a screenshot of that shows the situation:
Mihir Saxena 0 Reputation points Microsoft External Staff Moderator

2025-03-26T03:30:56.79+00:00
Error dropping role with no members and no admin:

Below are the mentioned steps where I successfully dropped the "new_role":

Creating an Admin user:

CREATE ROLE test_admin WITH LOGIN PASSWORD 'Test@123' CREATEDB CREATEROLE;

Grant this new Admin permission to create roles:

ALTER ROLE test_admin CREATEROLE;

Exit and reconnect as test_admin:

psql -h postgrever.postgres.database.azure.com -U test_admi -d postgres

Creating a New role:

CREATE ROLE test_svc_account;

Verify that test_admin is the owner: (output will come as "f")

SELECT rolname, rolcanlogin, rolcreatedb, rolcreaterole FROM pg_roles WHERE rolname = 'test_svc_account';

Switch back to the original myadmin user:

psql -h postgrever.postgres.database.azure.com -U admi -d postgres

Dropping the test_admin user:

(Now, test_svc_account is orphaned, no user has ADMIN rights on it.)

DROP ROLE test_admin;

Droping test_svc_account:

DROP ROLE test_svc_account;

Steps you can try to drop the role:

Verify Your Current User’s Privileges:

SELECT rolname, rolcreaterole, rolsuper FROM pg_roles WHERE rolname = current_user;

{ rolcreaterole = Must be true to drop a role (t)}

If rolcreaterole = f, then try granting an existing admin user "CREATEROLE" and then try dropping the role:

GRANT CREATEROLE TO current_user;

DROP ROLE test_svc_account;

If the Role owns any objects, transfer them first and then try dropping it:

REASSIGN OWNED BY test_svc_account TO current_user;

DROP OWNED BY test_svc_account;
GregF 10 Reputation points

2025-03-26T11:22:38.07+00:00

Thank you for your effort to duplicate my issue. Those are the steps that I followed that led me to this situation. However, I am unable to drop the role.

Does your original admin user (myadmin) have SUPERUSER? I think that may be the issue here. I have opened a case with Azure support.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-03-27T03:43:43.2366667+00:00

Hi GregF,

Could you provide the service request number.

We will reach out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Mihir Saxena 0 Reputation points Microsoft External Staff Moderator

2025-03-27T04:07:10.9533333+00:00

Hey @GregF ,

Have you tried mentioned steps i.e. granting "createrole" to the existing admin or transferring the objects associated with the role before attempting to drop it?

Also, my original admin user (myadmin) does not have SUPERUSER privileges. However, having SUPERUSER is not mandatory if the admin has the "rolcreaterole" privilege, they can still drop roles easily (as seen in my case).

Additionally, in Azure Database (a managed service), SUPERUSER access is typically not granted to customers. Azure maintains strict control over server-level operations and infrastructure to ensure security, stability, and consistency.

For more details, please refer to the Microsoft documentation:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users
GregF 10 Reputation points

2025-03-27T11:05:26.3133333+00:00

@Mihir Saxena Thank you for the update. The existing admin user (in our case local_admin which is created at instance creation) already has "createrole". This is the default admin for the instance and the highest privileged user that we have access to.

This seems like am edge-case scenario. We will definitely do our best to avoid it in the future. This exercise is part of my research to recommend to my team the best way to use administrative accounts in Azure database for PostgreSQL.

@PratikLad The engineer working my service request is suggesting that the internal team drop the role on our behalf.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-03-31T07:44:02.47+00:00

Hi @GregF ,

Glad to see you resolved the issue can you post the solution to help other community members
GregF 10 Reputation points

2025-03-31T12:31:03.27+00:00

@PratikLad The issue is not resolved. Internal Azure support staff is still attempting to drop the role on my behalf. They have not been able to do so yet.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-04-01T08:57:34.54+00:00

Hi @GregF ,

Could you provide the service request number.

We will reach out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Thuerriedl, Reinhard 25

Same problem here. With Azure Database for PostgreSQL Flexible Server V16 it is not possible to drop orphaned roles with the cloud provider admin account azure_pg_admin.

Only actual DB superusers can manage orphaned roles in PG V16. The community is aware of the problem and discussing if they should provide a "fix" or declare it a "feature". See https://www.postgresql.org/message-id/flat/CAE9k0PmwJxFcajwnouQECsRWhtGSe0OeXP-BK%3DG%2Bn1umjuqEBw%40mail.gmail.com

Steps to reproduce

Create chain of roles

postgres=> SELECT CURRENT_USER;
 current_user 
--------------
 postgres
(1 row)

postgres=> CREATE ROLE grandpa CREATEROLE;
GRANT grandpa TO CURRENT_USER; -- not needed on native PG and AWS
SET ROLE grandpa;
CREATE ROLE papa CREATEROLE;
GRANT papa TO CURRENT_USER; -- not needed on native PG and AWS
SET ROLE papa;
CREATE ROLE son;

-- output
CREATE ROLE
GRANT ROLE
SET
CREATE ROLE
GRANT ROLE
SET
CREATE ROLE

postgres=> \drg
                              List of role grants
   Role name    |          Member of          |       Options       | Grantor  
----------------+-----------------------------+---------------------+----------
 azure_pg_admin | pg_checkpoint               | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_create_subscription      | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_monitor                  | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_read_all_data            | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_read_all_settings        | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_read_all_stats           | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_signal_autovacuum_worker | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_signal_backend           | ADMIN, INHERIT, SET | azuresu
 azure_pg_admin | pg_stat_scan_tables         | ADMIN, INHERIT, SET | azuresu
 grandpa        | papa                        | ADMIN               | azuresu
 grandpa        | papa                        | INHERIT, SET        | grandpa
 papa           | son                         | ADMIN               | azuresu
 postgres       | azure_pg_admin              | ADMIN, INHERIT, SET | azuresu
 postgres       | grandpa                     | ADMIN               | azuresu
 postgres       | grandpa                     | INHERIT, SET        | postgres
 postgres       | pg_read_all_settings        | ADMIN, INHERIT, SET | azuresu
 postgres       | pg_read_all_stats           | ADMIN, INHERIT, SET | azuresu
 postgres       | pg_stat_scan_tables         | ADMIN, INHERIT, SET | azuresu
 replication    | pg_use_reserved_connections | INHERIT, SET        | azuresu
(19 rows)

Create orphan and try to drop it

postgres=> SET ROLE grandpa;
DROP ROLE papa;
SET
DROP ROLE
postgres=> DROP ROLE son;
ERROR:  permission denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option on role "son" may drop this role.

postgres=> SET ROLE postgres;
SET
postgres=> DROP ROLE son;
ERROR:  permission denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option on role "son" may drop this role.

postgres=> SET ROLE azure_pg_admin;
SET
postgres=> DROP ROLE son;
ERROR:  permission denied to drop role
DETAIL:  Only roles with the CREATEROLE attribute and the ADMIN option on the target roles may drop roles.

No available user (including the admin account azure_pg_admin) can drop the orphaned role anymore. At least on Azure. It is possible to drop the orphaned role with the AWS (Aurora-PostgreSQL) and GCP (Cloud SQL for PostgreSQL) managed PostgreSQL v16 databases.

1 answer

Your answer

Prasad Chaganti 770 Reputation points Microsoft External Staff Moderator

2025-03-21T19:46:46.54+00:00

Hi GregF,

It looks like you're encountering a permissions issue when trying to drop a role in your Azure PostgreSQL database. This is a common issue, especially with PostgreSQL 16, where roles with the CREATEROLE attribute need the ADMIN OPTION on the role they want to drop12.

Grant the Necessary Permissions:

First, make sure that your admin user has the CREATEROLE attribute and the ADMIN OPTION on the role you want to drop. You can grant these permissions using the following SQL commands:

-- Grant CREATEROLE attribute to your admin user ALTER ROLE your_admin_user CREATEROLE; -- Grant ADMIN OPTION on the role to be dropped GRANT ADMIN OPTION ON ROLE test_svc_account TO your_admin_user;

Once the permissions are granted, you should be able to drop the role:

DROP ROLE test_svc_account;

I hope this information helps. Please do let us know if you have any further queries.
GregF 10 Reputation points

2025-03-21T19:58:38.5766667+00:00

Thanks for the reply.

The problem is that my admin user does not have the ADMIN on option on the role that I need to drop, and there is no way to grant it. There are no admins in the database that have ADMIN on this role.

I created this role with an admin user, but then dropped that user. Leaving this role with no admins (except for maybe "azuresu", but I can't use that account).

Here is a screenshot of that shows the situation:
Mihir Saxena 0 Reputation points Microsoft External Staff Moderator

2025-03-26T03:30:56.79+00:00

Error dropping role with no members and no admin:

Below are the mentioned steps where I successfully dropped the "new_role":

Creating an Admin user:

CREATE ROLE test_admin WITH LOGIN PASSWORD 'Test@123' CREATEDB CREATEROLE;

Grant this new Admin permission to create roles:

ALTER ROLE test_admin CREATEROLE;

Exit and reconnect as test_admin:

psql -h postgrever.postgres.database.azure.com -U test_admi -d postgres

Creating a New role:

CREATE ROLE test_svc_account;

Verify that test_admin is the owner: (output will come as "f")

SELECT rolname, rolcanlogin, rolcreatedb, rolcreaterole FROM pg_roles WHERE rolname = 'test_svc_account';

Switch back to the original myadmin user:

psql -h postgrever.postgres.database.azure.com -U admi -d postgres

Dropping the test_admin user:

(Now, test_svc_account is orphaned, no user has ADMIN rights on it.)

DROP ROLE test_admin;

Droping test_svc_account:

DROP ROLE test_svc_account;

Steps you can try to drop the role:

Verify Your Current User’s Privileges:

SELECT rolname, rolcreaterole, rolsuper FROM pg_roles WHERE rolname = current_user;

{ rolcreaterole = Must be true to drop a role (t)}

If rolcreaterole = f, then try granting an existing admin user "CREATEROLE" and then try dropping the role:

GRANT CREATEROLE TO current_user;

DROP ROLE test_svc_account;

If the Role owns any objects, transfer them first and then try dropping it:

REASSIGN OWNED BY test_svc_account TO current_user;

DROP OWNED BY test_svc_account;
GregF 10 Reputation points

2025-03-26T11:22:38.07+00:00

Thank you for your effort to duplicate my issue. Those are the steps that I followed that led me to this situation. However, I am unable to drop the role.

Does your original admin user (myadmin) have SUPERUSER? I think that may be the issue here. I have opened a case with Azure support.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-03-27T03:43:43.2366667+00:00

Hi GregF,

Could you provide the service request number.

We will reach out to the internal team to get more information related to your query and will get back to you as soon as we have an update.
Mihir Saxena 0 Reputation points Microsoft External Staff Moderator

2025-03-27T04:07:10.9533333+00:00

Hey @GregF ,

Have you tried mentioned steps i.e. granting "createrole" to the existing admin or transferring the objects associated with the role before attempting to drop it?

Also, my original admin user (myadmin) does not have SUPERUSER privileges. However, having SUPERUSER is not mandatory if the admin has the "rolcreaterole" privilege, they can still drop roles easily (as seen in my case).

Additionally, in Azure Database (a managed service), SUPERUSER access is typically not granted to customers. Azure maintains strict control over server-level operations and infrastructure to ensure security, stability, and consistency.

For more details, please refer to the Microsoft documentation:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-create-users
GregF 10 Reputation points

2025-03-27T11:05:26.3133333+00:00

@Mihir Saxena Thank you for the update. The existing admin user (in our case local_admin which is created at instance creation) already has "createrole". This is the default admin for the instance and the highest privileged user that we have access to.

This seems like am edge-case scenario. We will definitely do our best to avoid it in the future. This exercise is part of my research to recommend to my team the best way to use administrative accounts in Azure database for PostgreSQL.

@PratikLad The engineer working my service request is suggesting that the internal team drop the role on our behalf.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-03-31T07:44:02.47+00:00

Hi @GregF ,

Glad to see you resolved the issue can you post the solution to help other community members
GregF 10 Reputation points

2025-03-31T12:31:03.27+00:00

@PratikLad The issue is not resolved. Internal Azure support staff is still attempting to drop the role on my behalf. They have not been able to do so yet.
PratikLad 1,830 Reputation points Microsoft External Staff Moderator

2025-04-01T08:57:34.54+00:00

Hi @GregF ,

Could you provide the service request number.

We will reach out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Answer 1

GregF 10

The solution for my specific case is that Azure technical support dropped the orphaned role on my behalf. This is definitely a shortcoming of Azure database for PostgreSQL, and DBAs need to take measures to ensure they do not to fall into this situation.

Share via

Error dropping role with no members and no admin

Steps to reproduce

1 answer

Your answer