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

[AC Gomez <antklc@xxxxxxxxx>]

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;

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

7. For each Database
8.    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;

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