# Dynamic postgres database credentials role revocation issue

**URL:** <https://discuss.hashicorp.com/t/dynamic-postgres-database-credentials-role-revocation-issue/62842>\
**Category:** Vault\
**Created:** [February 16, 2024, 12:56pm UTC](https://discuss.hashicorp.com/t/dynamic-postgres-database-credentials-role-revocation-issue/62842 "2024-02-16T12:56:27Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![nits7sid](https://avatars.discourse-cdn.com/v4/letter/n/c0e974/32.png) [@nits7sid](https://discuss.hashicorp.com/u/nits7sid)\
**Post date:** [February 16, 2024, 12:56pm UTC](https://discuss.hashicorp.com/t/dynamic-postgres-database-credentials-role-revocation-issue/62842/1 "2024-02-16T12:56:27Z")

</div>

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:

```auto
REASSIGN OWNED BY "{{name}}" TO postgres;
DROP ROLE IF EXISTS "{{name}}";

```

**Attached postgres logs:**  
[pdb.txt](https://discuss.hashicorp.com/uploads/short-url/9COOOyRC2ALXKWd3uthpxUBGhyd.txt) (92.8 KB)

**Attached users list from postgres roles table:**  
[List of roles.txt](https://discuss.hashicorp.com/uploads/short-url/5JImqgklcHqSNsIUG09Qat9JXVp.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?
