Cannot Drop Role in PostgreSQL (in Azure PostgreSQL Flexible Server)

Question

Spoiler alert: I am aware that a similar question has been asked before, yet I am using Azure PosgreSQL Flexible server, which means that I do not have superuser access to Postgresql (as it is a managed service, and only Azure has superuser permissions) thus some solutions that might have worked for others do not apply here, also the behaviour of the managed postgreSQL server might be different.

Here is the problem: We have some dynamically created DB roles in postgresql which get Connection permissions to a DB and additional permissions (insert, update etc.) on all Tables in the public schema of that DB. The create statement used for these roles is:

    CREATE USER "test" WITH LOGIN PASSWORD 'somepasswordyoudonotneedtoknow' VALID UNTIL '2024-05-03 20:02:25+00';
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO "test";
    GRANT CONNECT ON DATABASE "myDB" TO "test";

That seems to work fine so far. We use the role to connect to the DB and execute some select statements. Now after the role expired we want to remove it again, by revoking access and dropping the role:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "test";
REVOKE ALL PRIVILEGES ON DATABASE "myDB" FROM "test";
DROP ROLE IF EXISTS "test";

The first two statements execute without an error, but the DROP ROLE fails with:

ERROR:  privileges for database myDBrole "test" cannot be dropped because some objects depend on it 

ERROR:  role "test" cannot be dropped because some objects depend on it
SQL state: 2BP01
Detail: privileges for database myDB

This indicates that the user still has some privileges on the database itself, but the REVOKE ALL PRIVILEGES ON DATABASE did not return any error.

When I then check the permissions for the database using pgAdmin (4.8) I can see on the security tab of the DB properties, that the role still has connect permissions on the DB. Even when I try to delete this entry and save (no error shows up), then open the properties again, the role still has "c" (connect) permissions.

So I am not sure what is going on, and why no errors are shown then trying to revoke CONNECT access to the database in the first place. All other statements I tried to transfer all OWNER ship of this role failed since I do need superuser permissions for that, which Azure does not give to clients in the managed Solution.

Any ideas?

I also tried getting a list of all privileges for the user:

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'test';

But no result where returned

Answer 1

So Update from my side:

TLDR: perform a Point in Time restore of an old backup where your DB is not corrupt yet and better also implement your own backup process :-)

After literally weeks of back and forth with the Azure Support team, at some point the product team was involved but also did not have a solution to fix the issues. Once the DB is in a corrupt state, the only fix that Azure Support came up with was to restore the the DB from a previous backup where the DB was not yet in a defective state and take it from there. Unfortunately since this "solution" was provided several weeks after the case has been opened, we did not have any backup anymore that did not contain the corrupted DB plus of course live goes on and the other databases got updated so it would have been a migration project with quite some effort to restore a backup on a new flexible server instance then merge all delta data from current state of the other DBs to the new instance then shut down the old instance and point all consumers to the new DB instance. We are very lucky that this happened to us in our DEV environment and not in Production, but certainly we learned not to use the Azure Portal UI to delete Databases anymore, since this was beginning of all the troubles.

It is a limitation of the flexible server that the customer does not get a superuser role that one has to be aware from the beginning when choosing PostgreSQL flexible Server. It is understandable on the one hand, that Azure wants to limit the damage the customer can do to the managed instance, but also it can lead to problems if you cannot properly manage the data and permissions in your own instance.

Answer 2

Unfortunately, I am facing the same issue and while trying to look for help in the Azure portal, they had a link pointing to this stackoverflow post (kind of funny right?).

As far as I know, I can see two options here:

1. We can delete the database (which might not work for everyone since you might don't want to lost data), and then create the role with the CREATEROLE attribute

CREATE USER "test" WITH LOGIN PASSWORD CREATEROLE 'somepasswordyoudonotneedtoknow' VALID UNTIL '2024-05-03 20:02:25+00';

this way you can drop it next time, as long as you are connected with the same role that you used to create the 'test' role.

2. We try to reach the Azure support team to delete the role.

My rationale might be wrong, so please correct me (this is my first stackoverflow post/answer)