
You can’t just exec DROP ROLE your_role_name;
if it’s granted perms or other roles are granted to it. I had to go fishing to find all the grants to revoke them. Note: if you are worried about re-granting later, you can always fiddle with this to output the grants for these perms as a rollback.
If it owns something
In my case, the role didn’t own anything, but if yours does, check this out: REASSIGN OWNED — change the ownership of database objects owned by a database role.
Roles granted to a role
You need to find out if any other role is granted the role you want to get rid of. If any exist, you can copy out the REVOKE statements to run those.
SELECT r.rolname AS granted_role,
m.rolname AS member_role,
'REVOKE ' || r.rolname || ' FROM ' || m.rolname || ';' AS revoke_statement
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
WHERE r.rolname = 'your_role_name';
Grants at the DB and schema level
You need to revoke at the db and schema level. Just copy out the REVOKE statements that are generated.
WITH role_db_privs AS (
SELECT
r.rolname,
d.datname,
ARRAY_AGG(DISTINCT dp.privilege_type) AS database_privileges
FROM pg_roles r
CROSS JOIN pg_database d
LEFT JOIN LATERAL (
SELECT
privilege_type
FROM aclexplode(d.datacl) a
WHERE a.grantee = r.oid
) dp ON true
WHERE r.rolname = 'your_role_name'
AND d.datname NOT IN ('template0', 'template1')
GROUP BY r.rolname, d.datname
HAVING ARRAY_AGG(DISTINCT dp.privilege_type) IS NOT NULL
)
SELECT format(
'REVOKE ALL %s ON DATABASE %I FROM %I;',
array_to_string(database_privileges, ', '),
datname,
rolname
) as revoke_statement
FROM role_db_privs;
WITH role_schema_privs AS (
SELECT
r.rolname,
n.nspname,
ARRAY_AGG(DISTINCT sp.privilege_type) AS schema_privileges
FROM pg_roles r
CROSS JOIN pg_namespace n
LEFT JOIN LATERAL (
SELECT
privilege_type
FROM aclexplode(n.nspacl) a
WHERE a.grantee = r.oid
) sp ON true
WHERE r.rolname = 'your_role_name'
AND n.nspname NOT LIKE 'pg_%'
AND n.nspname != 'information_schema'
GROUP BY r.rolname, n.nspname
HAVING ARRAY_AGG(DISTINCT sp.privilege_type) IS NOT NULL
)
SELECT format(
'REVOKE ALL %s ON SCHEMA %I FROM %I;',
array_to_string(schema_privileges, ', '),
nspname,
rolname
) as revoke_statement
FROM role_schema_privs;
Object level grants
Then you have to revoke all the object grants, as well. I don’t take credit for this lovely query below. I took this base query from a DBA Stack Exchange answer and added revoke statements to it. This way, again, you can just copy out the REVOKE statements to clear out the perms. I also ordered this by grantee, so I could easily fish out just the revokes for this one role.
/*
Source query is from an answer here:
https://dba.stackexchange.com/questions/285591/how-to-list-all-grants-per-user-role-on-postgresql
I made some minor modifications to support my purposes as outlined in the paragraph above.
*/WITH rol AS (
SELECT oid,
rolname::text AS role_name
FROM pg_roles
UNION
SELECT 0::oid AS oid,
'public'::text
),
schemas AS ( -- Schemas
SELECT oid AS schema_oid,
n.nspname::text AS schema_name,
n.nspowner AS owner_oid,
'schema'::text AS object_type,
coalesce ( n.nspacl, acldefault ( 'n'::"char", n.nspowner ) ) AS acl
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
),
classes AS ( -- Tables, views, etc.
SELECT schemas.schema_oid,
schemas.schema_name AS object_schema,
c.oid,
c.relname::text AS object_name,
c.relowner AS owner_oid,
CASE
WHEN c.relkind = 'r' THEN 'table'
WHEN c.relkind = 'v' THEN 'view'
WHEN c.relkind = 'm' THEN 'materialized view'
WHEN c.relkind = 'c' THEN 'type'
WHEN c.relkind = 'i' THEN 'index'
WHEN c.relkind = 'S' THEN 'sequence'
WHEN c.relkind = 's' THEN 'special'
WHEN c.relkind = 't' THEN 'TOAST table'
WHEN c.relkind = 'f' THEN 'foreign table'
WHEN c.relkind = 'p' THEN 'partitioned table'
WHEN c.relkind = 'I' THEN 'partitioned index'
ELSE c.relkind::text
END AS object_type,
CASE
WHEN c.relkind = 'S' THEN coalesce ( c.relacl, acldefault ( 's'::"char", c.relowner ) )
ELSE coalesce ( c.relacl, acldefault ( 'r'::"char", c.relowner ) )
END AS acl
FROM pg_class c
JOIN schemas
ON ( schemas.schema_oid = c.relnamespace )
WHERE c.relkind IN ( 'r', 'v', 'm', 'S', 'f', 'p' )
),
cols AS ( -- Columns
SELECT c.object_schema,
null::integer AS oid,
c.object_name || '.' || a.attname::text AS object_name,
'column' AS object_type,
c.owner_oid,
coalesce ( a.attacl, acldefault ( 'c'::"char", c.owner_oid ) ) AS acl
FROM pg_attribute a
JOIN classes c
ON ( a.attrelid = c.oid )
WHERE a.attnum > 0
AND NOT a.attisdropped
),
procs AS ( -- Procedures and functions
SELECT schemas.schema_oid,
schemas.schema_name AS object_schema,
p.oid,
p.proname::text AS object_name,
p.proowner AS owner_oid,
CASE p.prokind
WHEN 'a' THEN 'aggregate'
WHEN 'w' THEN 'window'
WHEN 'p' THEN 'procedure'
ELSE 'function'
END AS object_type,
pg_catalog.pg_get_function_arguments ( p.oid ) AS calling_arguments,
coalesce ( p.proacl, acldefault ( 'f'::"char", p.proowner ) ) AS acl
FROM pg_proc p
JOIN schemas
ON ( schemas.schema_oid = p.pronamespace )
),
udts AS ( -- User defined types
SELECT schemas.schema_oid,
schemas.schema_name AS object_schema,
t.oid,
t.typname::text AS object_name,
t.typowner AS owner_oid,
CASE t.typtype
WHEN 'b' THEN 'base type'
WHEN 'c' THEN 'composite type'
WHEN 'd' THEN 'domain'
WHEN 'e' THEN 'enum type'
WHEN 't' THEN 'pseudo-type'
WHEN 'r' THEN 'range type'
WHEN 'm' THEN 'multirange'
ELSE t.typtype::text
END AS object_type,
coalesce ( t.typacl, acldefault ( 'T'::"char", t.typowner ) ) AS acl
FROM pg_type t
JOIN schemas
ON ( schemas.schema_oid = t.typnamespace )
WHERE ( t.typrelid = 0
OR ( SELECT c.relkind = 'c'
FROM pg_catalog.pg_class c
WHERE c.oid = t.typrelid ) )
AND NOT EXISTS (
SELECT 1
FROM pg_catalog.pg_type el
WHERE el.oid = t.typelem
AND el.typarray = t.oid )
),
fdws AS ( -- Foreign data wrappers
SELECT null::oid AS schema_oid,
null::text AS object_schema,
p.oid,
p.fdwname::text AS object_name,
p.fdwowner AS owner_oid,
'foreign data wrapper' AS object_type,
coalesce ( p.fdwacl, acldefault ( 'F'::"char", p.fdwowner ) ) AS acl
FROM pg_foreign_data_wrapper p
),
fsrvs AS ( -- Foreign servers
SELECT null::oid AS schema_oid,
null::text AS object_schema,
p.oid,
p.srvname::text AS object_name,
p.srvowner AS owner_oid,
'foreign server' AS object_type,
coalesce ( p.srvacl, acldefault ( 'S'::"char", p.srvowner ) ) AS acl
FROM pg_foreign_server p
),
all_objects AS (
SELECT schema_name AS object_schema,
object_type,
schema_name AS object_name,
null::text AS calling_arguments,
owner_oid,
acl
FROM schemas
UNION
SELECT object_schema,
object_type,
object_name,
null::text AS calling_arguments,
owner_oid,
acl
FROM classes
UNION
SELECT object_schema,
object_type,
object_name,
null::text AS calling_arguments,
owner_oid,
acl
FROM cols
UNION
SELECT object_schema,
object_type,
object_name,
calling_arguments,
owner_oid,
acl
FROM procs
UNION
SELECT object_schema,
object_type,
object_name,
null::text AS calling_arguments,
owner_oid,
acl
FROM udts
UNION
SELECT object_schema,
object_type,
object_name,
null::text AS calling_arguments,
owner_oid,
acl
FROM fdws
UNION
SELECT object_schema,
object_type,
object_name,
null::text AS calling_arguments,
owner_oid,
acl
FROM fsrvs
),
acl_base AS (
SELECT object_schema,
object_type,
object_name,
calling_arguments,
owner_oid,
( aclexplode ( acl ) ).grantor AS grantor_oid,
( aclexplode ( acl ) ).grantee AS grantee_oid,
( aclexplode ( acl ) ).privilege_type AS privilege_type,
( aclexplode ( acl ) ).is_grantable AS is_grantable
FROM all_objects
)
SELECT acl_base.object_schema,
acl_base.object_type,
acl_base.object_name,
acl_base.calling_arguments,
owner.role_name AS object_owner,
grantor.role_name AS grantor,
grantee.role_name AS grantee,
acl_base.privilege_type,
acl_base.is_grantable,
-- Adding REVOKE statement column
CASE
WHEN acl_base.privilege_type IS NOT NULL
THEN 'REVOKE ' || acl_base.privilege_type || ' ON ' || acl_base.object_type || ' ' || acl_base.object_schema || '.' || acl_base.object_name || ' FROM ' || grantee.role_name || ';'
ELSE NULL
END AS revoke_statement
FROM acl_base
JOIN rol owner
ON ( owner.oid = acl_base.owner_oid )
JOIN rol grantor
ON ( grantor.oid = acl_base.grantor_oid )
JOIN rol grantee
ON ( grantee.oid = acl_base.grantee_oid )
WHERE acl_base.grantor_oid <> acl_base.grantee_oid
ORDER BY grantee;
At the end of this revoking, exec DROP ROLE your_role_name;
and you are done.
The post Drop roles in Postgres appeared first on sqlkitty.