[Hashicorp Vault/PostgreSQL] Cleanup of roles with permissions and ownership

I has this customer who uses Hashicorp vault for database access management. When I logged in into the database I noticed that there were a lot of old expired roles that should have been revoked.

The roles are created with this creation statement:

CREATE ROLE "{{name}}" LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE my_parent_role INHERIT;

This parent role is the owner of the database, granting the new roles access to the database, with permission to create and alter the tables.

When the roles expire, vault tries to revoke them but since the roles are created with inherited permissions of a “parent” role, it failed because the role still owns its ACL rules. Furthermore, if the role created tables, the temporary role itself becomes the owner of the table. This causes thet you can’t simply drop a role.

DROP ROLE my_temp_role;

ERROR: role "my_temp_role" cannot be dropped because some objects depend on it

To mitigate this I created this more complicated revocation statement that does a couple of things.

It closes all existing connections
It revokes most commonly used rights
It reassigns any existing owned objects to the pg_database_owner role
Finally it drops the role

DO $$
DECLARE
	r RECORD;
BEGIN
    -- Terminate connections
    FOR r IN SELECT pid FROM pg_stat_activity WHERE usename = '{{name}}' LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;

    -- Revoke privileges
    BEGIN
        REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "{{name}}";
        REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM "{{name}}";
        REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM "{{name}}";
        REVOKE ALL ON SCHEMA public FROM "{{name}}";
        REVOKE ALL ON DATABASE my_database FROM "{{name}}";
    EXCEPTION
        WHEN OTHERS THEN
            -- Ignore the error or log it, since this only fails if the role or privileges don't exist
            RAISE NOTICE 'Failed to revoke privileges or role does not exist: %', SQLERRM;
    END;

    -- Reassign what's left
    BEGIN
        REASSIGN OWNED BY "{{name}}" TO pg_database_owner;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Failed to reassign owned or role does not exist: %', SQLERRM;
    END;

    -- Drop role if exists
    BEGIN
        DROP ROLE IF EXISTS "{{name}}";
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Failed to drop role or role does not exist: %', SQLERRM;
    END;

    -- Done
    RAISE NOTICE 'Done';
END $$;

Now the role is dropped successfully in the most common use cases.

Disclaimer: If you want to use this also, you might need to modify this a bit for your situation.

hashicorp vault

postgresql