Dropping postgres role and database after many grants

Question

I'm trying to efficiently drop role from a postgres instance, and running into some problems. I'm looking for some SQL to drop role so that I can stop reading error messages and don't have to do this manually.

In the setup I'm working in, every roles gets its own database of the same name:

CREATE ROLE alpha_user;
CREATE DATABASE alpha_user;
ALTER DATABASE alpha_user OWNER TO alpha_user;

Users often grant access to schemas in their database to other users:

-- alpha_user logs in to the alpha_user database
GRANT USAGE ON SCHEMA myschema TO beta_user;

When I attempt to drop beta_user, this happens:

-- log in as superuser
DROP ROLE beta_user;
-- ERROR:  role "beta_user" cannot be dropped because some objects depend on it
-- DETAIL:  N objects in database alpha_user

I can connect to the alpha_user database, and drop OWNED BY, but this is inefficient:

-- log in as superuser
\c alpha_user;
DROP OWNED BY beta_user CASCADE;
DROP beta_user;
-- success

Users can grant access to any number of databases, and there are many users. Is there a statement (or series of statements) that a super user can execute to DROP OWNED BY for a user across all databases where the user has been granted access?

Answer 1

Maybe this will help you:

with 
  user_id as (select oid, rolname as my_user from pg_authid where rolname in('abc', 'xyz'))
select 'REVOKE ' || rolname || ' FROM ' || my_user || ' CASCADE;' as sql
from pg_auth_members
join pg_authid on pg_auth_members.roleid = pg_authid.oid  
JOIN user_id ON pg_auth_members.member = user_id.oid
union 
SELECT 'REVOKE ALL ON ' || datname || ' FROM ' || my_user || ' CASCADE;' 
FROM pg_database 
JOIN user_id ON pg_database.datdba = user_id.oid