Blog Post

Drop roles in Postgres

,

Photo by ANIRUDH on Unsplash

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.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating