Hi Team,
We are currently investigating Dynamic database credentials using postgres plugin with HC vault version v1.15.2
We are using below revocation statements:
REASSIGN OWNED BY "{{name}}" TO postgres;
DROP ROLE IF EXISTS "{{name}}";
Attached postgres logs:
pdb.txt (92.8 KB)
Attached users list from postgres roles table:
List of roles.txt (7.9 KB)
As part of testing we have observed below 2 patterns:
Pattern-1
Role ID’s appearing in postgres db logs for REASSIGN OWNED doesn’t seems to be appearing in list of user’s table.
e.g: the below line for id: v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911"appears in the log but not in the list of users.
REASSIGN OWNED BY "v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911" TO postgres
BUT there is no trace or log for DROP ROLE against the above role ID if we assume that that role has been deleted?
So pattern-1 observation says that the role v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911 doesn’t exist in the users list. i.e. role is dropped however, the DROP ROLE statement is not appearing in the postgres sql log for v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911
Pattern-2
ID’s from the list of users table seems to be appearing in the logs for DROP ROLE statement and it fails with error role "v-kubernet-db-app-tCBmXmAHsSQwhRlEhxLG-1708082378" cannot be dropped because some objects depend on it.
e.g: For Role id v-kubernet-db-app-lfV4xdqM7bN6uTm3wIdh-1708083414 we have below line appearing the logs:
role "v-kubernet-db-app-lfV4xdqM7bN6uTm3wIdh-1708083414" cannot be dropped because some objects depend on it
So pattern-2 observation says that the DROP ROLE has failed for some ID’s due to some dependency on objects.
Any what could be wrong here? Also any suggestion on what could be the good way to revoke roles from postgres without impacting applications connecting to the DB?