Decline in role and postgres database after many grants

Question

I am trying to effectively drop role from a postgres instance and run into some problems. I am looking for SQL for the drop role so that I can stop reading error messages and do not have to do it manually.

In the setup in which I work, each homeland gets its own database with the same name:

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

Users often provide access to schemes 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 try to reset beta_user , this will happen:

 -- 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 provide access to any number of databases, and there are many users. Is there an instruction (or a series of statements) that a super user can execute for DROP OWNED BY for a user in all the databases where the user has been granted access?

Answer 1

Perhaps 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