Dropping a user in postgres. WIP

drop-pguser.sql

-- Delete permissions and drop a user (ROLE) from a 
-- postgres database.
-- Be sure to update the following:
-- <username_to_delete> - The target user name
-- <your_database_name> - The target database name
-- <schema_name> - The target schema.
DO $$
DECLARE
    r RECORD;
BEGIN
    -- Revoke privileges from all tables
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = '<schema_name>' LOOP
        EXECUTE 'REVOKE ALL ON TABLE <schema_name>.' || quote_ident(r.tablename) || ' FROM <username_to_delete> CASCADE';
    END LOOP;

    -- Revoke privileges from all sequences
    FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = '<schema_name>' LOOP
        EXECUTE 'REVOKE ALL ON SEQUENCE <schema_name>.' || quote_ident(r.sequencename) || ' FROM <username_to_delete> CASCADE';
    END LOOP;

    -- Revoke privileges from all functions
    FOR r IN SELECT proname FROM pg_proc WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = '<schema_name>') LOOP
        EXECUTE 'REVOKE ALL ON FUNCTION <schema_name>.' || quote_ident(r.proname) || '() FROM <username_to_delete> CASCADE';
    END LOOP;

    -- Revoke privileges from the schema
    EXECUTE 'REVOKE ALL ON SCHEMA <schema_name> FROM <username_to_delete> CASCADE';

    -- Revoke role memberships
    EXECUTE 'REVOKE ALL PRIVILEGES ON DATABASE <your_database_name> FROM <username_to_delete> CASCADE';

    -- Finally, drop the user
    EXECUTE 'DROP ROLE IF EXISTS <username_to_delete>';
END $$;