3

... including the inherited ones? When organizing access rights through different roles, this seems to be something really useful, yet I haven't found anything that would show how to do this.

AIUI, I can use \dp to list all the privileges a role has been granted, and I can use \du to list all the roles a role is "member of", but I haven't yet come across a command (or query) that would list all the privileges a role has, including all the inherited privileges (and from what role a given privilege was inherited).

I can imagine how to combine the queries behind \du and \dp to recursively get all the "member of" relationships of a given role, then get all the privileges of all the roles in that membership tree, and list them in a useful way. It doesn't look like rocket science, but it's also doesn't look like something trivial that I can do in a couple minutes. I'm just wondering whether this has been done before -- I can't be the first to want this.

The answers in this question show a few ways to query privileges, but nothing about inherited privileges. This answer shows a recursive query to get the roles, but doesn't address privileges.

1 Answer 1

0

Here is m option to get privileges. Hope someone will find it useful

WITH server_permissions AS (
    SELECT 
        r.rolname, 
        'Server_Permissions' AS "Level", 
        r.rolsuper, 
        r.rolinherit,
        r.rolcreaterole, 
        r.rolcreatedb, 
        r.rolcanlogin,
        ARRAY(
            SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
            WHERE m.member = r.oid
        ) AS memberof,
        r.rolbypassrls
    FROM pg_catalog.pg_roles r
    WHERE r.rolname !~ '^pg_'
),

db_ownership AS (
    SELECT 
        r.rolname, 
        'DB_Ownership' AS "Level", 
        d.datname
    FROM pg_catalog.pg_database d, pg_catalog.pg_roles r
    WHERE d.datdba = r.oid
),

schema_permissions AS (
    SELECT
        'Schema Permissions' AS "Level",                
        r.rolname AS role_name,
        nspname AS schema_name,
        pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') AS create_grant,
        pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') AS usage_grant
    FROM pg_namespace pn, pg_catalog.pg_roles r
    WHERE array_to_string(nspacl, ',') LIKE '%' || r.rolname || '%' 
          AND nspowner > 1
),

table_ownership AS (
    SELECT 
        'Table Ownership' AS "Level",
        tableowner, 
        schemaname, 
        tablename
    FROM pg_tables
    GROUP BY tableowner, schemaname, tablename
),

object_permissions AS (
    SELECT  
        'Object Permissions' AS "Level",
        COALESCE(NULLIF(s[1], ''), 'public') AS rolname,
        n.nspname,
        relname, 
        CASE 
            WHEN relkind = 'm' THEN 'Materialized View'
            WHEN relkind = 'p' THEN 'Partitioned Table'
            WHEN relkind = 'S' THEN 'Sequence'
            WHEN relkind = 'I' THEN 'Partitioned Index'
            WHEN relkind = 'v' THEN 'View'
            WHEN relkind = 'i' THEN 'Index'
            WHEN relkind = 'c' THEN 'Composite Type'
            WHEN relkind = 't' THEN 'TOAST table'
            WHEN relkind = 'r' THEN 'Table'
            WHEN relkind = 'f' THEN 'Foreign Table'
        END AS "Object Type",
        s[2] AS privileges
    FROM 
        pg_class c
        JOIN pg_namespace n ON n.oid = relnamespace
        JOIN pg_roles r ON r.oid = relowner,
        UNNEST(COALESCE(relacl::text[], FORMAT('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl, 
        REGEXP_SPLIT_TO_ARRAY(acl, '=|/') s 
    WHERE relkind <> 'i' AND relkind <> 't'
)   

SELECT 
    "Level", 
    rolname AS "Role", 
    'N/A' AS "Object Name", 
    'N/A' AS "Schema Name", 
    'N/A' AS "DB Name", 
    'N/A' AS "Object Type", 
    'N/A' AS "Privileges", 
    rolsuper::text AS "Is SuperUser", 
    rolinherit::text,
    rolcreaterole::text, 
    rolcreatedb::text, 
    rolcanlogin::text,
    memberof::text,
    rolbypassrls::text 
FROM server_permissions

UNION

SELECT 
    dow."Level", 
    dow.rolname,
    'N/A',  
    'N/A', 
    datname,
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A'
FROM db_ownership AS dow 

UNION

SELECT
    "Level", 
    role_name, 
    'N/A', 
    schema_name, 
    'N/A', 
    'N/A',
    CASE 
        WHEN create_grant IS TRUE AND usage_grant IS TRUE THEN 'Usage+Create' 
        WHEN create_grant IS TRUE AND usage_grant IS FALSE THEN 'Create' 
        WHEN create_grant IS FALSE AND usage_grant IS TRUE THEN 'Usage' 
        ELSE 'None' 
    END, 
    'N/A', 
    'N/A', 
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A'
FROM schema_permissions

UNION

SELECT 
    "Level", 
    tableowner, 
    tablename, 
    schemaname,
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A'
FROM table_ownership

UNION

SELECT 
    "Level", 
    rolname, 
    relname,  
    nspname, 
    'N/A', 
    "Object Type", 
    privileges,
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A',
    'N/A'
FROM object_permissions
ORDER BY "Role";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.