Security Analysis through system catalogs reveals access patterns, privilege escalations, and potential security vulnerabilities.
-- Comprehensive role and privilege analysis
SELECT 
    r.rolname as role_name,
    r.rolsuper as is_superuser,
    r.rolinherit as can_inherit,
    r.rolcreaterole as can_create_roles,
    r.rolcreatedb as can_create_databases,
    r.rolcanlogin as can_login,
    r.rolreplication as replication_role,
    r.rolbypassrls as bypass_rls,
    r.rolconnlimit as connection_limit,
    r.rolvaliduntil as valid_until
FROM pg_roles r
ORDER BY r.rolname;

-- Role membership hierarchy
WITH RECURSIVE role_tree AS (
    SELECT 
        r.rolname as role_name,
        r.oid as role_oid,
        NULL::name as parent_role,
        0 as level
    FROM pg_roles r
    WHERE NOT EXISTS (
        SELECT 1 FROM pg_auth_members m 
        WHERE m.member = r.oid
    )
    
    UNION ALL
    
    SELECT 
        mr.rolname,
        mr.oid,
        rt.role_name,
        rt.level + 1
    FROM role_tree rt
    JOIN pg_auth_members m ON m.roleid = rt.role_oid
    JOIN pg_roles mr ON mr.oid = m.member
    WHERE rt.level < 10
)
SELECT 
    role_name,
    parent_role,
    level,
    repeat('  ', level) || role_name as hierarchy
FROM role_tree
ORDER BY parent_role NULLS FIRST, role_name;

-- Table and schema permissions
SELECT 
    n.nspname as schema_name,
    c.relname as object_name,
    CASE c.relkind
        WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'materialized view'
        WHEN 'S' THEN 'sequence'
        WHEN 'f' THEN 'foreign table'
    END as object_type,
    r.rolname as grantee,
    p.privilege_type,
    p.is_grantable
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL (
    SELECT 
        (aclexplode(c.relacl)).grantee,
        (aclexplode(c.relacl)).privilege_type,
        (aclexplode(c.relacl)).is_grantable
    WHERE c.relacl IS NOT NULL
) p
LEFT JOIN pg_roles r ON r.oid = p.grantee
WHERE n.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY n.nspname, c.relname, r.rolname;

-- Function execution privileges
SELECT 
    n.nspname as schema_name,
    p.proname as function_name,
    r.rolname as grantee,
    priv.privilege_type,
    priv.is_grantable
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
CROSS JOIN LATERAL (
    SELECT 
        (aclexplode(p.proacl)).grantee,
        (aclexplode(p.proacl)).privilege_type,
        (aclexplode(p.proacl)).is_grantable
    WHERE p.proacl IS NOT NULL
) priv
LEFT JOIN pg_roles r ON r.oid = priv.grantee
WHERE n.nspname NOT IN ('information_schema', 'pg_catalog')
ORDER BY n.nspname, p.proname, r.rolname;

-- Row Level Security policies
SELECT 
    n.nspname as schema_name,
    c.relname as table_name,
    pol.polname as policy_name,
    CASE pol.polcmd
        WHEN '*' THEN 'ALL'
        WHEN 'r' THEN 'SELECT'
        WHEN 'a' THEN 'INSERT'
        WHEN 'w' THEN 'UPDATE'
        WHEN 'd' THEN 'DELETE'
    END as command_type,
    pol.polpermissive as is_permissive,
    array_to_string(array(
        SELECT rolname FROM pg_roles 
        WHERE oid = ANY(pol.polroles)
    ), ', ') as applies_to_roles,
    pg_get_expr(pol.polqual, pol.polrelid) as using_expression,
    pg_get_expr(pol.polwithcheck, pol.polrelid) as with_check_expression
FROM pg_policy pol
JOIN pg_class c ON c.oid = pol.polrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY n.nspname, c.relname, pol.polname;
Security Audit: Regular security analysis helps identify privilege escalations and maintain least-privilege access.
Loading PostgreSQL Knowledge Base...

PostgreSQL Assistant

Keyboard Shortcuts
