PostgreSQL util queries

columns.sql

-- Adapted from information_schema.columns

SELECT
  c.oid :: int8 AS table_id,
  nc.nspname AS schema,
  c.relname AS table,
  (c.oid || '.' || a.attnum) AS id,
  a.attnum AS ordinal_position,
  a.attname AS name,
  CASE
    WHEN a.atthasdef THEN pg_get_expr(ad.adbin, ad.adrelid)
    ELSE NULL
  END AS default_value,
  CASE
    WHEN t.typtype = 'd' THEN CASE
      WHEN bt.typelem <> 0 :: oid
      AND bt.typlen = -1 THEN 'ARRAY'
      WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, NULL)
      ELSE 'USER-DEFINED'
    END
    ELSE CASE
      WHEN t.typelem <> 0 :: oid
      AND t.typlen = -1 THEN 'ARRAY'
      WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, NULL)
      ELSE 'USER-DEFINED'
    END
  END AS data_type,
  COALESCE(bt.typname, t.typname) AS format,
  CASE
    WHEN a.attidentity IN ('a', 'd') THEN TRUE
    ELSE FALSE
  END AS is_identity,
  CASE
    a.attidentity
    WHEN 'a' THEN 'ALWAYS'
    WHEN 'd' THEN 'BY DEFAULT'
    ELSE NULL
  END AS identity_generation,
  CASE
    WHEN a.attnotnull
    OR t.typtype = 'd'
    AND t.typnotnull THEN FALSE
    ELSE TRUE
  END AS is_nullable,
  CASE
    WHEN (
      c.relkind IN ('r', 'p')
    )
    OR (
      c.relkind IN ('v', 'f')
    )
    AND pg_column_is_updatable(c.oid, a.attnum, FALSE) THEN TRUE
    ELSE FALSE
  END AS is_updatable,
  array_to_json(
    array(
      SELECT
        enumlabel
      FROM
        pg_catalog.pg_enum enums
      WHERE
        quote_ident(COALESCE(bt.typname, t.typname)) = format_type(enums.enumtypid, NULL)
      ORDER BY
        enums.enumsortorder
    )
  ) AS enums,
  col_description(c.oid, a.attnum) AS comment
FROM
  pg_attribute a
  LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid
  AND a.attnum = ad.adnum
  JOIN (
    pg_class c
    JOIN pg_namespace nc ON c.relnamespace = nc.oid
  ) ON a.attrelid = c.oid
  JOIN (
    pg_type t
    JOIN pg_namespace nt ON t.typnamespace = nt.oid
  ) ON a.atttypid = t.oid
  LEFT JOIN (
    pg_type bt
    JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid
  ) ON t.typtype = 'd'
  AND t.typbasetype = bt.oid
WHERE
  NOT pg_is_other_temp_schema(nc.oid)
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND (c.relkind IN ('r', 'v', 'f', 'p'))
  AND (
    pg_has_role(c.relowner, 'USAGE')
    OR has_column_privilege(
      c.oid,
      a.attnum,
      'SELECT, INSERT, UPDATE, REFERENCES'
    )
  )

config.sql

SELECT
  name,
  setting,
  category,
  TRIM(split_part(category, '/', 1)) AS group,
  TRIM(split_part(category, '/', 2)) AS subgroup,
  unit,
  short_desc,
  extra_desc,
  context,
  vartype,
  source,
  min_val,
  max_val,
  enumvals,
  boot_val,
  reset_val,
  sourcefile,
  sourceline,
  pending_restart
FROM
  pg_settings
ORDER BY
  category,
  name

extensions.sql

SELECT
  e.name,
  n.nspname AS schema,
  e.default_version,
  x.extversion AS installed_version,
  e.comment
FROM
  pg_available_extensions() e(name, default_version, comment)
  LEFT JOIN pg_extension x ON e.name = x.extname
  LEFT JOIN pg_namespace n ON x.extnamespace = n.oid

functions.sql

SELECT
  p.oid :: int8 AS id,
  n.nspname AS schema,
  p.proname AS name,
  l.lanname AS language,
  CASE
    WHEN l.lanname = 'internal' THEN ''
    ELSE p.prosrc
  END AS definition,
  CASE
    WHEN l.lanname = 'internal' THEN p.prosrc
    ELSE pg_get_functiondef(p.oid)
  END AS complete_statement,
  pg_get_function_arguments(p.oid) AS argument_types,
  pg_get_function_identity_arguments(p.oid) AS identity_argument_types,
  t.typname AS return_type,
  CASE
    WHEN p.provolatile = 'i' THEN 'IMMUTABLE'
    WHEN p.provolatile = 's' THEN 'STABLE'
    WHEN p.provolatile = 'v' THEN 'VOLATILE'
  END AS behavior,
  p.prosecdef AS security_definer,
  JSON_OBJECT_AGG(p_config.param, p_config.value)
    FILTER (WHERE p_config.param IS NOT NULL) AS config_params
FROM
  pg_proc p
  LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
  LEFT JOIN pg_language l ON p.prolang = l.oid
  LEFT JOIN pg_type t ON t.oid = p.prorettype
  LEFT JOIN (
    SELECT
	    oid as id,
	    (string_to_array(unnest(proconfig), '='))[1] AS param,
   	  (string_to_array(unnest(proconfig), '='))[2] AS value
	  FROM
	    pg_proc
	) p_config ON p_config.id = p.oid
GROUP BY
  p.oid,
  n.nspname,
  p.proname,
  l.lanname,
  p.prosrc,
  t.typname,
  p.provolatile,
  p.prosecdef

grants.sql

-- Adapted from information_schema.role_table_grants

SELECT
  c.oid :: int8 AS table_id,
  u_grantor.rolname AS grantor,
  grantee.rolname AS grantee,
  nc.nspname AS schema,
  c.relname AS table_name,
  c.prtype AS privilege_type,
  CASE
    WHEN pg_has_role(grantee.oid, c.relowner, 'USAGE')
    OR c.grantable THEN TRUE
    ELSE FALSE
  END AS is_grantable,
  CASE
    WHEN c.prtype = 'SELECT' THEN TRUE
    ELSE FALSE
  END AS with_hierarchy
FROM
  (
    SELECT
      pg_class.oid,
      pg_class.relname,
      pg_class.relnamespace,
      pg_class.relkind,
      pg_class.relowner,
      (
        aclexplode(
          COALESCE(
            pg_class.relacl,
            acldefault('r', pg_class.relowner)
          )
        )
      ).grantor AS grantor,
      (
        aclexplode(
          COALESCE(
            pg_class.relacl,
            acldefault('r', pg_class.relowner)
          )
        )
      ).grantee AS grantee,
      (
        aclexplode(
          COALESCE(
            pg_class.relacl,
            acldefault('r', pg_class.relowner)
          )
        )
      ).privilege_type AS privilege_type,
      (
        aclexplode(
          COALESCE(
            pg_class.relacl,
            acldefault('r', pg_class.relowner)
          )
        )
      ).is_grantable AS is_grantable
    FROM
      pg_class
  ) c(
    oid,
    relname,
    relnamespace,
    relkind,
    relowner,
    grantor,
    grantee,
    prtype,
    grantable
  ),
  pg_namespace nc,
  pg_authid u_grantor,
  (
    SELECT
      pg_authid.oid,
      pg_authid.rolname
    FROM
      pg_authid
    UNION ALL
    SELECT
      0 :: oid AS oid,
      'PUBLIC'
  ) grantee(oid, rolname)
WHERE
  c.relnamespace = nc.oid
  AND (c.relkind IN ('r', 'v', 'f', 'p'))
  AND c.grantee = grantee.oid
  AND c.grantor = u_grantor.oid
  AND (
    c.prtype IN (
      'INSERT',
      'SELECT',
      'UPDATE',
      'DELETE',
      'TRUNCATE',
      'REFERENCES',
      'TRIGGER'
    )
  )
  AND (
    pg_has_role(u_grantor.oid, 'USAGE')
    OR pg_has_role(grantee.oid, 'USAGE')
  )

policies.sql

SELECT
  pol.oid :: int8 AS id,
  n.nspname AS schema,
  c.relname AS table,
  c.oid :: int8 AS table_id,
  pol.polname AS name,
  CASE
    WHEN pol.polpermissive THEN 'PERMISSIVE' :: text
    ELSE 'RESTRICTIVE' :: text
  END AS action,
  CASE
    WHEN pol.polroles = '{0}' :: oid [] THEN array_to_json(
      string_to_array('public' :: text, '' :: text) :: name []
    )
    ELSE array_to_json(
      ARRAY(
        SELECT
          pg_authid.rolname
        FROM
          pg_authid
        WHERE
          pg_authid.oid = ANY (pol.polroles)
        ORDER BY
          pg_authid.rolname
      )
    )
  END AS roles,
  CASE
    pol.polcmd
    WHEN 'r' :: "char" THEN 'SELECT' :: text
    WHEN 'a' :: "char" THEN 'INSERT' :: text
    WHEN 'w' :: "char" THEN 'UPDATE' :: text
    WHEN 'd' :: "char" THEN 'DELETE' :: text
    WHEN '*' :: "char" THEN 'ALL' :: text
    ELSE NULL :: text
  END AS command,
  pg_get_expr(pol.polqual, pol.polrelid) AS definition,
  pg_get_expr(pol.polwithcheck, pol.polrelid) AS check
FROM
  pg_policy pol
  JOIN pg_class c ON c.oid = pol.polrelid
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

primary_keys.sql

SELECT
  n.nspname AS schema,
  c.relname AS table_name,
  a.attname AS name,
  c.oid :: int8 AS table_id
FROM
  pg_index i,
  pg_class c,
  pg_attribute a,
  pg_namespace n
WHERE
  i.indrelid = c.oid
  AND c.relnamespace = n.oid
  AND a.attrelid = c.oid
  AND a.attnum = ANY (i.indkey)
  AND i.indisprimary

publications.sql

SELECT
  p.oid :: int8 AS id,
  p.pubname AS name,
  r.rolname AS owner,
  p.pubinsert AS publish_insert,
  p.pubupdate AS publish_update,
  p.pubdelete AS publish_delete,
  p.pubtruncate AS publish_truncate,
  CASE
    WHEN p.puballtables THEN NULL
    ELSE pr.tables
  END AS tables
FROM
  pg_catalog.pg_publication AS p
  LEFT JOIN LATERAL (
    SELECT
      COALESCE(
        array_agg(
          json_build_object(
            'id',
            c.oid :: int8,
            'name',
            c.relname,
            'schema',
            nc.nspname
          )
        ),
        '{}'
      ) AS tables
    FROM
      pg_catalog.pg_publication_rel AS pr
      JOIN pg_class AS c ON pr.prrelid = c.oid
      join pg_namespace as nc on c.relnamespace = nc.oid
    WHERE
      pr.prpubid = p.oid
  ) AS pr ON 1 = 1
  JOIN pg_roles AS r ON p.pubowner = r.oid

relationships.sql

SELECT
  c.oid :: int8 AS id,
  c.conname AS constraint_name,
  nsa.nspname AS source_schema,
  csa.relname AS source_table_name,
  sa.attname AS source_column_name,
  nta.nspname AS target_table_schema,
  cta.relname AS target_table_name,
  ta.attname AS target_column_name
FROM
  pg_constraint c
  JOIN (
    pg_attribute sa
    JOIN pg_class csa ON sa.attrelid = csa.oid
    JOIN pg_namespace nsa ON csa.relnamespace = nsa.oid
  ) ON sa.attrelid = c.conrelid
  AND sa.attnum = ANY (c.conkey)
  JOIN (
    pg_attribute ta
    JOIN pg_class cta ON ta.attrelid = cta.oid
    JOIN pg_namespace nta ON cta.relnamespace = nta.oid
  ) ON ta.attrelid = c.confrelid
  AND ta.attnum = ANY (c.confkey)
WHERE
  c.contype = 'f'

roles.sql

-- TODO: Consider using pg_authid vs. pg_roles for unencrypted password field
SELECT
  oid :: int8 AS id,
  rolname AS name,
  rolsuper AS is_superuser,
  rolcreatedb AS can_create_db,
  rolcreaterole AS can_create_role,
  rolinherit AS inherit_role,
  rolcanlogin AS can_login,
  rolreplication AS is_replication_role,
  rolbypassrls AS can_bypass_rls,
  (
    SELECT
      COUNT(*)
    FROM
      pg_stat_activity
    WHERE
      pg_roles.rolname = pg_stat_activity.usename
  ) AS active_connections,
  CASE WHEN rolconnlimit = -1 THEN current_setting('max_connections') :: int8
       ELSE rolconnlimit
  END AS connection_limit,
  rolpassword AS password,
  rolvaliduntil AS valid_until,
  rolconfig AS config
FROM
  pg_roles

schemas.sql

-- Adapted from information_schema.schemata

SELECT
  n.oid :: int8 AS id,
  n.nspname AS name,
  u.rolname AS owner
FROM
  pg_namespace n,
  pg_authid u
WHERE
  n.nspowner = u.oid
  AND (
    pg_has_role(n.nspowner, 'USAGE')
    OR has_schema_privilege(n.oid, 'CREATE, USAGE')
  )

tables.sql

SELECT
  c.oid :: int8 AS id,
  nc.nspname AS schema,
  c.relname AS name,
  c.relrowsecurity AS rls_enabled,
  c.relforcerowsecurity AS rls_forced,
  CASE
    WHEN c.relreplident = 'd' THEN 'DEFAULT'
    WHEN c.relreplident = 'i' THEN 'INDEX'
    WHEN c.relreplident = 'f' THEN 'FULL'
    ELSE 'NOTHING'
  END AS replica_identity,
  pg_total_relation_size(format('%I.%I', nc.nspname, c.relname)) :: int8 AS bytes,
  pg_size_pretty(
    pg_total_relation_size(format('%I.%I', nc.nspname, c.relname))
  ) AS size,
  pg_stat_get_live_tuples(c.oid) AS live_rows_estimate,
  pg_stat_get_dead_tuples(c.oid) AS dead_rows_estimate,
  obj_description(c.oid) AS comment
FROM
  pg_namespace nc
  JOIN pg_class c ON nc.oid = c.relnamespace
WHERE
  c.relkind IN ('r', 'p')
  AND NOT pg_is_other_temp_schema(nc.oid)
  AND (
    pg_has_role(c.relowner, 'USAGE')
    OR has_table_privilege(
      c.oid,
      'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'
    )
    OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')
  )

triggers.sql

SELECT
  pg_t.oid AS id,
  pg_t.tgrelid AS table_id,
  CASE
    WHEN pg_t.tgenabled = 'D' THEN 'DISABLED'
    WHEN pg_t.tgenabled = 'O' THEN 'ORIGIN'
    WHEN pg_t.tgenabled = 'R' THEN 'REPLICA'
    WHEN pg_t.tgenabled = 'A' THEN 'ALWAYS'
  END AS enabled_mode,
  (
    STRING_TO_ARRAY(
      ENCODE(pg_t.tgargs, 'escape'), '\000'
    )
  )[:pg_t.tgnargs] AS function_args,
  is_t.trigger_name AS name,
  is_t.event_object_table AS table,
  is_t.event_object_schema AS schema,
  is_t.action_condition AS condition,
  is_t.action_orientation AS orientation,
  is_t.action_timing AS activation,
  ARRAY_AGG(is_t.event_manipulation)::text[] AS events,
  pg_p.proname AS function_name,
  pg_n.nspname AS function_schema
FROM
  pg_trigger AS pg_t
JOIN
  pg_class AS pg_c
ON pg_t.tgrelid = pg_c.oid
JOIN information_schema.triggers AS is_t
ON is_t.trigger_name = pg_t.tgname
AND pg_c.relname = is_t.event_object_table
JOIN pg_proc AS pg_p
ON pg_t.tgfoid = pg_p.oid
JOIN pg_namespace AS pg_n
ON pg_p.pronamespace = pg_n.oid
GROUP BY
  pg_t.oid,
  pg_t.tgrelid,
  pg_t.tgenabled,
  pg_t.tgargs,
  pg_t.tgnargs,
  is_t.trigger_name,
  is_t.event_object_table,
  is_t.event_object_schema,
  is_t.action_condition,
  is_t.action_orientation,
  is_t.action_timing,
  pg_p.proname,
  pg_n.nspname

types.sql

SELECT
  t.oid :: int8 AS id,
  t.typname AS name,
  n.nspname AS schema,
  format_type (t.oid, NULL) AS format,
  array_to_json(
    array(
      SELECT
        e.enumlabel
      FROM
        pg_enum e
      WHERE
        e.enumtypid = t.oid
      ORDER BY
        e.oid
    )
  ) AS enums,
  obj_description (t.oid, 'pg_type') AS comment
FROM
  pg_type t
  LEFT JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE
  (
    t.typrelid = 0
    OR (
      SELECT
        c.relkind = 'c'
      FROM
        pg_class c
      WHERE
        c.oid = t.typrelid
    )
  )
  AND NOT EXISTS (
    SELECT
      1
    FROM
      pg_type el
    WHERE
      el.oid = t.typelem
      AND el.typarray = t.oid
  )

version.sql

SELECT
  version(),
  current_setting('server_version_num') :: int8 AS version_number,
  (
    SELECT
      COUNT(*) AS active_connections
    FROM
      pg_stat_activity
  ) AS active_connections,
  current_setting('max_connections') :: int8 AS max_connections