Trouble DROP'ing a ROLE, despite REVOKE'ing its privileges - Mailing list pgsql-general

Hi. I'm writing a unit test that creates a set of schemas and roles,

then drops all those roles and schemas.

First I create a NOINHERIT NOLOGIN CREATEROLE "owner" ROLE,

which I GRANT on the current user (and other LOGIN users later), then SET ROLE "owner",

and create all the SCHEMAs (and there associated ROLEs),

to have them (the SCHEMAs) owned by "owner".

All ROLEs (including the "owner") have a GRANT USAGE ON SCHEMA, for all created SCHEMAs. (I use the ACLs on the SCHEMAs to find their associated ROLEs, in addition to naming conventions).

And they also get a ALTER DEFAULT PRIVILEGES IN SCHEMA ... GRANT $priv TO $role,

where $priv is for example "SELECT ON TABLES", "USAGE, SELECT ON SEQUENCES",

That's for the ROLEs to implicitly get GRANTs on new objects created in those schemas.

(I actually do that right after the CREATE SCHEMA, and before doing all the CREATE TABLE DDLs, but it's mostly meant for later SCHEMA changes, to have the ROLE "auto-updated")

Then right away, the unit test tries to tear down all objects, in another transaction.

First, I start with a SET ROLE "owner" again (not sure it's necessary).

Then a DROP OWNED BY "owner" CASCADE to get rid of all SCHEMAs.

(since they are all owned by "owner", on purpose).

Then I find and get rid of all ROLEs (associated to SCHEMAs, via USAGE ACLs, see above) except "owner", by DROP'ing them.

Again, so far, so good.

Final step is to DROP the "owner" ROLE itself, but before doing that,

I run REVOKE ALL ON DATABASE "ddevienne" FROM "owner".

(that ROLE has no other privileges in other databases. It's created on the fly).

Then I RESET ROLE, since one cannot DROP the current_role.

So the current ROLE is back to the LOGIN USER.

But despite both steps above, the DROP ROLE "owner" fails...

Here's the error I get:

Given that just REVOKE'd ALL on the DB just before, I don't understand that message.

I'm in C++, and any libpq error throws an exception, which implicitly ROLLBACK's the transaction, thus leaving / leaking all my SCHEMAs and ROLEs around.

And what's even Weirder, is that when I go in PSQL, and manually clean up the mess left behind by the unit test that failed, I have no problem DROP'ing those "owner" ROLEs. E.g.

ddevienne=> drop owned by "Epos-PNS (cbee4676ba7f428da29d36e1b8a4d523)";
DROP OWNED
Time: 27.607 ms
ddevienne=> drop role "Epos-PNS (cbee4676ba7f428da29d36e1b8a4d523)";
DROP ROLE
Time: 0.852 ms

These are *exactly* the same steps the unit tests goes through, yet in PSQL the 2nd works fine!

Except perhaps I didn't run those inside a transaction. In the code, I tried doing the DROP ROLE "owner" after COMMIT'ing the transaction, and that didn't help (but at least left just a single ROLE to cleanup manually, since the COMMIT succeeds).

So at this point, I'm kinda baffled, and need some advice.

Could there be some kind of race that the REVOKE ALL on the DB is not yet "effective"?

I tried adding a SLEEP(1s) in the code, and that didn't help.

What kind of QUERY should I run in the code, to assertain what kind of privileges that ROLE still has? Or what other objects could be depending on the "owner" ROLE?

Any help would be appreciated in diagnosing this vexing issue. Thanks, --DD