ownership_of_auth.sql

-- referenced in "Resolving 500 Errors in Authentication" troubleshooting guide


SELECT 
    n.nspname AS schema_name,
    CASE 
        WHEN c.relkind = 'r' THEN 'table'
        WHEN c.relkind = 'v' THEN 'view'
        WHEN c.relkind = 'm' THEN 'materialized view'
        WHEN c.relkind = 'i' THEN 'index'
        WHEN c.relkind = 'S' THEN 'sequence'
        WHEN c.relkind = 'f' THEN 'foreign table'
        WHEN c.relkind = 'p' THEN 'partitioned table'
        ELSE c.relkind::text
    END AS object_type,
    c.relname AS object_name,
    a.rolname AS owner
FROM 
    pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_roles a ON a.oid = c.relowner
WHERE 
    n.nspname = 'auth'
    AND c.relkind IN ('r', 'v', 'm', 'S', 'f', 'p')

UNION ALL

SELECT 
    n.nspname AS schema_name,
    CASE 
        WHEN p.prokind = 'f' THEN 'function'
        WHEN p.prokind = 'p' THEN 'procedure'
        WHEN p.prokind = 'a' THEN 'aggregate function'
        ELSE p.prokind::text
    END AS object_type,
    p.proname AS object_name,
    a.rolname AS owner
FROM 
    pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    JOIN pg_roles a ON a.oid = p.proowner
WHERE 
    n.nspname = 'auth'

UNION ALL

SELECT 
    n.nspname AS schema_name,
    CASE 
        WHEN t.typtype = 'e' THEN 'enum'
        WHEN t.typtype = 'c' THEN 'composite'
        ELSE 'custom type'
    END AS object_type,
    t.typname AS object_name,
    a.rolname AS owner
FROM 
    pg_type t
    JOIN pg_namespace n ON n.oid = t.typnamespace
    JOIN pg_roles a ON a.oid = t.typowner
WHERE 
    n.nspname = 'auth'
    AND t.typtype NOT IN ('b', 'd', 'p')  -- Exclude built-in types, domains, and pseudo-types

ORDER BY 
    schema_name, object_type, object_name;