At least I think I've revoked the privileges...

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".

To be able to do that, I of course GRANT CREATE ON DATABASE "ddevienne" TO
"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",
"EXECUTE ON ROUTINES", "USAGE ON TYPES".

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")

And I do all those creations in a first transaction. So far so good.

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:

ERROR:  role "Epos-PNS (9e8a5159b0a64c02a40358431af1aa00)" cannot be
dropped because some objects depend on it
DETAIL:  privileges for database ddevienne

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

Reply via email to