Vault PostgreSQL revocation fails

Vault
1

I can’t get the Vault PostgreSQL integration to work for all cases.
If database migrations are done by a temporary RW vault user new tables will be owned by the temporary Vault user.
I assumed to resolve this by including the following in the Vault revocation statement:

REASSIGN OWNED BY {{username}} TO {{database}};
REVOKE {{database}} FROM {{username}};
DROP USER IF EXISTS {{username}};

Unfortunately that doesn’t work since REASSIGN OWNED BY only affects the current schema. Since the vault user connects to the postgres schema this doesn’t have any affect.

2

I don’t normally use postgreSQL but did need to write a procedure for this, see if this helps:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM {{username}};
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM {{username}};
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM {{username}};
DROP USER IF EXISTS {{username}};