How to delete PG user when they have no ADMIN OWNER

Question

How to delete PG user when they have no ADMIN OWNER

Brenwell 0

I have a Azure Flexible Postgres Server and I have a user I want to delete. However I am not the superuser because that is Azure itself. I am a member of the azure_pg_admin group. But for some unknow reason the user has no ADMIN OWNER

How could I possibly delete them?

Query 1 ERROR at Line 1: : ERROR:  permission denied to drop role

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

Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-27T12:15:57.73+00:00

Hi Brenwell
Could you please share the specific error message you are encountering?
Alex Burlachenko 3,980 Reputation points

2025-03-27T13:44:04.6033333+00:00
Brenwell hi,
as a member of the azure_pg_admin group, you should have sufficient privileges to manage users (so strange why u dont) any way (if u dont try drop the user so try it)

DROP USER username;

If this fails with a permission error, proceed to reassign or drop owned objects first. The user might own database objects preventing deletion. Check and reassign

-- Find objects owned by the user SELECT * FROM pg_tables WHERE tableowner = 'username'; SELECT * FROM pg_views WHERE viewowner = 'username'; -- Reassign ownership (example for tables) REASSIGN OWNED BY username TO current_user; -- Or drop owned objects if they're no longer needed DROP OWNED BY username;

Then try dropping the user again.

Best regards,

Alex

P.S. If my answer help to you, please Accept my answer

Brenwell 0

If I try to drop the user I get this

Query 1 ERROR at Line 1: : ERROR:  permission denied to drop role

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

Brenwell 0

if I try to reassign I get this

Query 1 ERROR at Line 1: : ERROR:  permission denied to reassign objects DETAIL:  Only roles with privileges of role "user" may reassign objects owned by it.

Alex Burlachenko 3,980 Reputation points

2025-03-27T15:22:40.8033333+00:00

Hmmm need to think
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-28T08:52:21.3066667+00:00
Hi Brenwell,
I’m sorry you’re still wrestling with this,it’s tricky when Azure’s superuser restrictions come into play, and I can see you’ve already gotten some great suggestions here. As a member of azure_pg_admin, you should be able to delete that user, even without superuser rights or “ADMIN OWNER” status, as long as you clear any dependencies. let’s simplify it into a quick plan based on what’s worked for others with Azure Flexible Postgres:

Check for Ownership: Connect as your azure_pg_admin user and see if the target user owns anything (databases or objects):
SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = 'target_user');
For objects in a specific database:
SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relowner = (SELECT oid FROM pg_roles WHERE rolname = 'target_user');

Reassign If Needed: If you find any databases or objects, reassign them to your admin user:

For databases
ALTER DATABASE database_name OWNER TO your_admin_user;

For objects (run in each affected database)
REASSIGN OWNED BY target_user TO your_admin_user;

Drop Privileges: Clear any privileges tied to the user (run in each relevant database)
DROP OWNED BY target_user;

Delete the User: Finally, drop the user
DROP ROLE target_user;

Since you’re in azure_pg_admin, this should work—Azure gives you CREATEROLE, which is enough to manage non-superuser roles like this, even if they don’t have an “ADMIN OWNER” tag. If you’re still getting a “permission denied” error, it might be a lingering dependency not caught above. Could you share the exact error (if any) , so I can double-check?

I hope this information helps. Please do let us know if you have any further queries. 
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-31T16:31:15.7166667+00:00

Hi Brenwell,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-04-01T16:57:11.0633333+00:00

Hi Brenwell,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Brenwell 0

I tried this. the first to statements returned no rows.
The third returned

REASSIGN OWNED BY username TO <current_user>;

Query 1 ERROR at Line 1: : ERROR:  permission denied to reassign objects
DETAIL:  Only roles with privileges of role "username" may reassign objects owned by it.

and the final

REASSIGN OWNED BY <username> TO current_user;

Query 1 ERROR at Line 1: : ERROR:  permission denied to drop objects
DETAIL:  Only roles with privileges of role "username" may drop objects owned by it.

I am not sure how get around this

PratikLad 720 Reputation points Microsoft External Staff

2025-04-17T03:45:41.47+00:00

Hi Brenwell,

We have some limitation on Postgres 16, please check the below document.

Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION permission. For example, they can now change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.

https://www.postgresql.org/docs/16/release-16.html

Your answer

Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-27T12:15:57.73+00:00

Hi Brenwell
Could you please share the specific error message you are encountering?
Alex Burlachenko 3,980 Reputation points

2025-03-27T13:44:04.6033333+00:00

Brenwell hi,
as a member of the azure_pg_admin group, you should have sufficient privileges to manage users (so strange why u dont) any way (if u dont try drop the user so try it)

DROP USER username;

If this fails with a permission error, proceed to reassign or drop owned objects first. The user might own database objects preventing deletion. Check and reassign

-- Find objects owned by the user SELECT * FROM pg_tables WHERE tableowner = 'username'; SELECT * FROM pg_views WHERE viewowner = 'username'; -- Reassign ownership (example for tables) REASSIGN OWNED BY username TO current_user; -- Or drop owned objects if they're no longer needed DROP OWNED BY username;

Then try dropping the user again.

Best regards,

Alex

P.S. If my answer help to you, please Accept my answer
Brenwell 0 Reputation points

2025-03-27T15:20:00.7966667+00:00

If I try to drop the user I get this

Query 1 ERROR at Line 1: : ERROR: permission denied to drop role DETAIL: Only roles with the CREATEROLE attribute and the ADMIN option on role "user" may drop this role.
Brenwell 0 Reputation points

2025-03-27T15:20:40.6433333+00:00

if I try to reassign I get this

Query 1 ERROR at Line 1: : ERROR: permission denied to reassign objects DETAIL: Only roles with privileges of role "user" may reassign objects owned by it.
Alex Burlachenko 3,980 Reputation points

2025-03-27T15:22:40.8033333+00:00

Hmmm need to think
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-28T08:52:21.3066667+00:00

Hi Brenwell,
I’m sorry you’re still wrestling with this,it’s tricky when Azure’s superuser restrictions come into play, and I can see you’ve already gotten some great suggestions here. As a member of azure_pg_admin, you should be able to delete that user, even without superuser rights or “ADMIN OWNER” status, as long as you clear any dependencies. let’s simplify it into a quick plan based on what’s worked for others with Azure Flexible Postgres:

Check for Ownership: Connect as your azure_pg_admin user and see if the target user owns anything (databases or objects):
SELECT datname FROM pg_database WHERE datdba = (SELECT oid FROM pg_roles WHERE rolname = 'target_user');
For objects in a specific database:
SELECT nspname, relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relowner = (SELECT oid FROM pg_roles WHERE rolname = 'target_user');

Reassign If Needed: If you find any databases or objects, reassign them to your admin user:

For databases
ALTER DATABASE database_name OWNER TO your_admin_user;

For objects (run in each affected database)
REASSIGN OWNED BY target_user TO your_admin_user;

Drop Privileges: Clear any privileges tied to the user (run in each relevant database)
DROP OWNED BY target_user;

Delete the User: Finally, drop the user
DROP ROLE target_user;

Since you’re in azure_pg_admin, this should work—Azure gives you CREATEROLE, which is enough to manage non-superuser roles like this, even if they don’t have an “ADMIN OWNER” tag. If you’re still getting a “permission denied” error, it might be a lingering dependency not caught above. Could you share the exact error (if any) , so I can double-check?

I hope this information helps. Please do let us know if you have any further queries. 
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-03-31T16:31:15.7166667+00:00

Hi Brenwell,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Adithya Prasad K 725 Reputation points Microsoft External Staff

2025-04-01T16:57:11.0633333+00:00

Hi Brenwell,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Brenwell 0 Reputation points

2025-04-10T14:42:51.1633333+00:00

I tried this. the first to statements returned no rows.
The third returned

REASSIGN OWNED BY username TO <current_user>;

Query 1 ERROR at Line 1: : ERROR: permission denied to reassign objects DETAIL: Only roles with privileges of role "username" may reassign objects owned by it.

and the final

REASSIGN OWNED BY <username> TO current_user;

Query 1 ERROR at Line 1: : ERROR: permission denied to drop objects DETAIL: Only roles with privileges of role "username" may drop objects owned by it.

I am not sure how get around this
PratikLad 720 Reputation points Microsoft External Staff

2025-04-17T03:45:41.47+00:00

Hi Brenwell,

We have some limitation on Postgres 16, please check the below document.

Previously roles with CREATEROLE privileges could change many aspects of any non-superuser role. Such changes, including adding members, now require the role requesting the change to have ADMIN OPTION permission. For example, they can now change the CREATEDB, REPLICATION, and BYPASSRLS properties only if they also have those permissions.

https://www.postgresql.org/docs/16/release-16.html

Share via

How to delete PG user when they have no ADMIN OWNER

Your answer