New Role drop with Grant/Revokes stop working after subsequent runs

Hi,

On PostgreSQL 9.6.

We have developed some code that creates a new role to be used as the main
role for DB usage. This code will be called on a predetermined frequency to
act a role/pwd rotation mechanism.

Each time the code is run we feed it the prior role that was created (the
Db owner being the initial role fed in).

The first time the code runs, it works as expected, ie, new user and pwd
created with all appropriate grants. Also, on the very first run Revokes
not done for the DB Owner because we want to keep db owner.

The second time we run the code we feed the prior new user created and that
goes as expected, ie, new role and pwd with all grants granted and prior
user's grants revoked and prior user deleted. No errors.

The third time we run it, we feed in the prior created user and as
expected, the user is created. However, this time GRANTS and REVOKES do not
take effect even though there aren't any errors. The only error this time
is that when the DROP ROLE command is issued an error is thrown saying that
the prior role cannot be dropped because it has dependencies. While the
error is correct, this is not expected, given the prior runs. When I check
for new user Grants and prior User revokes, they were not applied despite
the commands having run without error. I know they ran because I have
logging after each command runs, which would not happen if an error were to
be thrown.

This code does not run on a loop so there isn't a loop variable that goes
awry after the second run. And further, there is no state which we save
from prior runs other than user/password.

I suppose the main question is, why would a bunch of grant and revoke
commands run and not do anything, not even throw an error?

I can see why the process would have run without issue on the first run as
it was using the db master role. But after that, this is working with newly
created roles, so if there was a failure to be had it should have happened
on the second run. yet it does tead fails on the third run??

Here is a summary of the process:

1. START
1. We begin with the db owner role as the bootstrap seed - but
subsequent runs feed in successive users.
2. With this role we create a new user/password, for example: CREATE
USER UUU WITH PASSWORD 'PPpp' CREATEDB CREATEROLE
3. GRANTS
4. GRANT <prior user> TO <new user>
5. For each Data
6. For each Schema

1. GRANT USAGE ON SCHEMA <schema> TO <new user>
2. GRANT CREATE ON SCHEMA <schema> TO <new user>
3. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO
<new user>
4. GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> TO
<new user>
5. GRANT EXECUTE ON ALL FUNCTIONS

ii. GRANT
ALL DEFAULT PRIVILEGES

iii. GRANT
POSTGRES_FDW

iv. GRANT
FOREIGN SERVER

end loop; end loop;

1. REVOKES

i. GRANT <prior user> TO <new user>

ii. REASSIGN OWNED BY <prior user> TO <new user>

iii. DROP OWNED BY <prior user> TO <new user>

1. For each Database
2. For each Schema

1. REVOKE USAGE ON SCHEMA <schema> TO <new user>
2. REVOKE CREATE ON SCHEMA <schema> TO <new user>
3. REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> TO
<new user>
4. REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> TO
<new user>
5. REVOKE EXECUTE ON ALL FUNCTIONS

ii. REVOKE
ALL DEFAULT PRIVILEGES

iii. REVOKE
POSTGRES_FDW

iv. REVOKE
FOREIGN SERVERS

end loop; end loop;

1. DROP ROLE <prior user> (if it's not the db owner)