多次授权后删除PostgreSQL角色和数据库

Question

我正在尝试高效地从Postgres实例中删除角色，并遇到一些问题。我正在寻找一些SQL语句来删除角色，这样我就不必手动操作了，也不用再阅读错误消息了。

在我正在处理的设置中，每个角色都有一个同名的数据库：

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

用户通常会授予其他用户对其数据库中模式的访问权限：

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

当我试图删除 beta_user 时，会发生这种情况：

-- 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

我可以连接到名为 alpha_user 的数据库，并删除 OWNED BY，但这样做效率低下：

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

用户可以授权访问任意数量的数据库，而且有很多用户。是否存在一种超级用户可以在所有已授权访问的数据库中执行DROP OWNED BY语句（或一系列语句），以针对某个用户撤销其所有拥有的对象？

- alacarter

---

你使用的 PostgreSQL 版本和操作系统是什么？ - Clément Prévost

Postgres 9.4和Ubuntu 14.04。操作系统为什么很重要？ - alacarter

你可以使用Linux提供的命令行工具来帮助你。 - Clément Prévost

Answer 1

也许这会对你有所帮助：

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

- Adam Silenko