Cannot Drop Role with "Replication" Attribute in Azure PostgreSQL Flexible Server 16

Gabriel Aquino 0

Hello,

I am encountering the following error when trying to drop a role that has the "REPLICATION" attribute enabled.

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

Some context:

Environment: Azure Database for PostgreSQL Flexible Server (version 16.8)

Authentication: PostgreSQL authentication only

This "admin" user created the role test_replication but is unable to drop it.

To investigate, we ran the following script:

DROP ROLE IF EXISTS test_noreplication;  

CREATE ROLE test_noreplication WITH  
LOGIN  
NOSUPERUSER  
INHERIT  
CREATEDB  
NOCREATEROLE  
NOREPLICATION;  

DROP ROLE IF EXISTS test_replication;  

CREATE ROLE test_replication WITH  
LOGIN  
NOSUPERUSER  
INHERIT  
CREATEDB  
NOCREATEROLE  
REPLICATION;

After running the script, we confirmed that we cannot drop the role that has the REPLICATION attribute enabled.

Thanks! Looking forward to your insights.

Prasad Chaganti 0 Reputation points Microsoft External Staff

2025-03-07T17:04:14.36+00:00
Hi Gabriel Aquino,

Thank you for posting the Question and using Microsoft Q&A,

It looks like you're trying to drop a role in your database, but you're encountering a permission error. This error indicates that only roles with the CREATEROLE attribute and the ADMIN option on the role "test_replication" can drop this role.

To resolve this issue, you need to ensure that the role you're using has the necessary permissions. Here are the steps you can follow:

Check your current role's attributes: Verify if your current role has the CREATEROLE attribute.

Grant the necessary permissions: If your role doesn't have the required permissions, you can grant them using the following SQL commands: -- Grant CREATEROLE attribute to your role

ALTER ROLE your_role_name CREATEROLE;

Grant ADMIN option on the role "test_replication"

GRANT ADMIN OPTION ON ROLE test_replication TO your_role_name;
I hope this information helps. Please do let us know if you have any further queries.

Share via

Cannot Drop Role with "Replication" Attribute in Azure PostgreSQL Flexible Server 16

Your answer