Удаление роли и базы данных postgres после многих грантов

Я пытаюсь эффективно drop role из экземпляра postgres и столкнулся с некоторыми проблемами. Я ищу какой-нибудь SQL для drop role, чтобы перестать читать сообщения об ошибках и не делать это вручную.

В настройке, в которой я работаю, каждая роль получает свою собственную базу данных с тем же именем:

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 для пользователя во всех базах данных, к которым пользователю был предоставлен доступ?


postgresql sql database psql database-administration
person alacarter    schedule 07.01.2016    source источник
comment
Какая у вас версия PostgreSQL и какая у вас операционная система?   -  person Clément Prévost    schedule 02.03.2016
comment
Постгрес 9.4 и Ubuntu 14.04. Почему операционная система должна иметь значение?   -  person alacarter    schedule 02.03.2016
comment
Вы можете воспользоваться помощью инструментов командной строки, которые предоставляет Linux.   -  person Clément Prévost    schedule 02.03.2016

Ответы (1)


arrow_upward
1
arrow_downward

Возможно, это поможет вам:

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
person Adam Silenko    schedule 23.03.2016