Top SQL scripts I found helpful as a newbie

[nate-oo]

Top SQL scripts I found helpful as a newbie

I put these scripts together originally to just help myself gain better visibility into my overall database setup. These are the top SQL scripts I have found useful and were created through a combination of resources found online, Supabase AI, and my personal changes.

I figured, hey.....if I found them useful as a newbie, others might as well.

*Disclaimer:
I am not a SQL expert but these seem to work just fine for me :)
Please read through this README.md file before trying these if you decide to :)

Cheers,
Nate

Helpful SQL for DB Management:

------------------------------------------------------------------------------------------------------------
--TABLES
--To list all tables and the description for each in the current database, you can run the following query:
SELECT 
pg_get_userbyid(nspowner) AS schema_owner,
nspname AS schema_name, 
relname AS table_name, 
obj_description(relfilenode, 'pg_class') AS table_description
FROM pg_class
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
LEFT JOIN pg_description ON pg_class.oid = pg_description.objoid
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
AND relkind = 'r'
-- You can uncomment here and specify a schema name if you want
-- AND nspname = 'public'
ORDER BY schema_owner, schema_name, table_name;



------------------------------------------------------------------------------------------------------------
--TABLES + RELATIONSHIPS
--To list all tables and the relationships for each in the current database, you can run the following query:
SELECT 
pg_get_userbyid(nspowner) AS schema_owner,
nspname AS schema_name, 
relname AS table_name, 
obj_description(relfilenode, 'pg_class') AS table_description,
contype AS constraint_type, 
pg_get_constraintdef(pg_constraint.oid) AS constraint_definition
FROM pg_class
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
LEFT JOIN pg_constraint ON pg_class.oid = pg_constraint.conrelid
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
AND relkind = 'r'
AND (contype = 'p' OR contype = 'f')
-- You can uncomment here and specify a schema name if you want
-- AND nspname = 'public'
ORDER BY schema_owner, schema_name, table_name, constraint_type DESC;


------------------------------------------------------------------------------------------------------------
--TRIGGERS
--To list all triggers and details for each in the current database, you can run the following query:
SELECT event_object_schema AS schema,
       event_object_table AS table,
       trigger_name AS trigger,
       action_timing AS timing,
       event_manipulation AS event,
       action_condition AS condition,
       action_statement AS statement
FROM information_schema.triggers
ORDER BY event_object_schema, event_object_table, trigger_name;


------------------------------------------------------------------------------------------------------------
--FUNCTIONS
--To list all functions and details for each in the current database, you can run the following query:
SELECT n.nspname AS schema,
       p.proname AS function,
       pg_get_function_arguments(p.oid) AS arguments,
       pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT LIKE 'pg_%'
  AND n.nspname != 'information_schema'
ORDER BY n.nspname, p.proname;


------------------------------------------------------------------------------------------------------------
--ENUMS
--To list all ENUMS and details for each in the current database, you can run the following query:
SELECT 
    n.nspname AS schema_name,
    t.typname AS enum_name,
    string_agg(e.enumlabel, ', ' ORDER BY e.enumsortorder) AS enum_values,
    array_agg(DISTINCT c.relname) AS tables_used_on
FROM 
    pg_type t
JOIN 
    pg_enum e ON t.oid = e.enumtypid
JOIN 
    pg_catalog.pg_namespace n ON n.oid = t.typnamespace
LEFT JOIN (
    SELECT 
        a.attrelid AS table_oid,
        a.atttypid AS enum_oid
    FROM 
        pg_attribute a
    WHERE 
        a.atttypid IN (SELECT oid FROM pg_type WHERE typtype = 'e')
) AS enum_usage ON t.oid = enum_usage.enum_oid
LEFT JOIN 
    pg_class c ON c.oid = enum_usage.table_oid

-- You can uncomment here and specify a schema name if you want
-- WHERE nspname = 'public'
GROUP BY 
    schema_name, enum_name
ORDER BY 
    schema_name, enum_name;

Helpful SQL for Privilege Management:

------------------------------------------------------------------------------------------------------------
--DATABASE Privileges
--To list all the databases and role permissions for each, you can run the following query.
SELECT
  d.datname AS database,
  pg_get_userbyid(d.datdba) AS owner_role,
  CASE WHEN COUNT(a.privilege_type) > 0 THEN COALESCE(r.rolname, '[public]') ELSE '' END AS role,
  COALESCE(string_agg(a.privilege_type, ', ' order by a.privilege_type), '') AS privileges,
  CASE
  --Gathered what is considered 'all privileges' from here: https://www.postgresql.org/docs/current/ddl-priv.html
    WHEN array_agg(a.privilege_type order by a.privilege_type) @> ARRAY['CONNECT', 'CREATE', 'TEMPORARY'] THEN 'ALL'
    ELSE 'NOT ALL'
  END AS all_privileges
FROM
  pg_database d
  LEFT JOIN LATERAL (
    SELECT
      *
    FROM
      aclexplode(datacl) AS x) a ON TRUE
  LEFT JOIN pg_roles r ON a.grantee = r.oid
WHERE
  d.datistemplate = FALSE
GROUP BY
  d.datname,
  d.datdba,
  r.rolname
ORDER BY
  d.datname;



------------------------------------------------------------------------------------------------------------
--SCHEMA Privileges
--To list all schemas in the current database and privileges for each, you can run the following query.
SELECT
 nspname AS schema,
 COALESCE(r.rolname, '[public]') AS role,
 string_agg(a.privilege_type, ', ' order by a.privilege_type) AS privileges,
  CASE
  --Gathered what is considered 'all privileges' from here: https://www.postgresql.org/docs/current/ddl-priv.html
    WHEN array_agg(a.privilege_type order by a.privilege_type) @> ARRAY['CREATE', 'USAGE'] THEN 'ALL'
    ELSE 'NOT ALL'
  END AS all_privileges
FROM
 pg_namespace
 JOIN LATERAL (
   SELECT
     *
   FROM
     aclexplode(nspacl) AS x) a ON TRUE
 LEFT JOIN pg_roles r ON a.grantee = r.oid
-- You can uncomment here and specify a schema name if you want
-- WHERE nspname = 'public'
GROUP BY
 nspname,
 r.rolname
ORDER BY
 nspname,
 r.rolname;



------------------------------------------------------------------------------------------------------------
--DEFAULT Privileges
--Default access privileges (defined with ALTER DEFAULT PRIVILEGES) can be shown with the following query:
SELECT
  pg_get_userbyid(a.defaclrole) AS owner_role,
  nspname AS schema,
  CASE a.defaclobjtype
    WHEN 'r' THEN 'table'
    WHEN 'S' THEN 'sequence'
    WHEN 'f' THEN 'function'
    WHEN 'T' THEN 'type'
    WHEN 'n' THEN 'schema'
    ELSE a.defaclobjtype::text
  END AS object_type,
  r.rolname AS role,
  string_agg(acle.privilege_type, ', ' order by acle.privilege_type) AS privileges,
  CASE
  --Gathered what is considered 'all privileges' from here: https://www.postgresql.org/docs/current/ddl-priv.html
    WHEN a.defaclobjtype = 'r' AND array_agg(acle.privilege_type order by acle.privilege_type) @> ARRAY['DELETE', 'INSERT', 'REFERENCES', 'SELECT', 'TRIGGER', 'TRUNCATE', 'UPDATE'] THEN 'ALL'
    WHEN a.defaclobjtype = 'S' AND array_agg(acle.privilege_type order by acle.privilege_type) @> ARRAY['SELECT', 'UPDATE', 'USAGE'] THEN 'ALL'
    WHEN a.defaclobjtype = 'f' AND array_agg(acle.privilege_type order by acle.privilege_type) @> ARRAY['EXECUTE'] THEN 'ALL'
    WHEN a.defaclobjtype = 'T' AND array_agg(acle.privilege_type order by acle.privilege_type) @> ARRAY['USAGE'] THEN 'ALL'
    WHEN a.defaclobjtype = 'n' AND array_agg(acle.privilege_type order by acle.privilege_type) @> ARRAY['CREATE', 'USAGE'] THEN 'ALL'
    ELSE 'NOT ALL'
  END AS all_privileges
FROM
  pg_default_acl a
  LEFT JOIN pg_namespace b ON a.defaclnamespace = b.oid
  JOIN LATERAL (
    SELECT
      *
    FROM
      aclexplode(a.defaclacl) AS x) acle ON TRUE
  LEFT JOIN pg_roles r ON acle.grantee = r.oid
-- You can uncomment here and specify a schema name if you want
-- WHERE nspname = 'public'
GROUP BY
  r.rolname,
  nspname,
  a.defaclobjtype,
  a.defaclrole
ORDER BY
  pg_get_userbyid(a.defaclrole),
  nspname,
  a.defaclobjtype;



------------------------------------------------------------------------------------------------------------
--TABLE Privileges
--To list all tables in the current database and privileges for each, you can run the following query.
select
  table_schema as schema,
  table_name as table,
  grantee as role,
  string_agg(privilege_type, ', ' order by privilege_type) as privileges,
  CASE
  --Gathered what is considered 'all privileges' from here: https://www.postgresql.org/docs/current/ddl-priv.html
    WHEN array_agg(privilege_type::text order by privilege_type::text) @> ARRAY['DELETE', 'INSERT', 'REFERENCES', 'SELECT', 'TRIGGER', 'TRUNCATE', 'UPDATE'] THEN 'ALL'
    ELSE 'NOT ALL'
  END AS all_privileges
from
  information_schema.table_privileges
-- You can uncomment here and specify a schema name if you want
-- where table_schema = 'public'
group by
  table_schema,
  table_name,
  grantee



------------------------------------------------------------------------------------------------------------
--FUNCTION privileges
--To list all functions in the current database and privileges for each, you can run the following query.
select
  routine_schema as schema,
  routine_name as function,
  grantee as role,
  string_agg(privilege_type, ', ' order by privilege_type) as privileges,
  CASE
  --Gathered what is considered 'all privileges' from here: https://www.postgresql.org/docs/current/ddl-priv.html
    WHEN array_agg(privilege_type::text order by privilege_type::text) @> ARRAY['EXECUTE'] THEN 'ALL'
    ELSE 'NOT ALL'
  END AS all_privileges
from
  information_schema.routine_privileges
-- You can uncomment here and specify a schema name if you want
where routine_schema = 'public'
group by
  routine_schema,
  routine_name,
  grantee



------------------------------------------------------------------------------------------------------------
--SEQUENCE Privileges
--I couldn't figure out this one



------------------------------------------------------------------------------------------------------------
--COLUMN Privileges
--To list all columns in the current database and privileges for each, you can run the following query.
select
  table_schema as schema,
  table_name as table,
  column_name as column,
  grantee as role,
  string_agg(privilege_type, ', ' order by privilege_type) as privileges,
  CASE
  --Gathered what is considered 'all privileges' from here: https://www.postgresql.org/docs/current/ddl-priv.html
    WHEN array_agg(privilege_type::text order by privilege_type::text) @> ARRAY['INSERT', 'REFERENCES', 'SELECT', 'UPDATE'] THEN 'ALL'
    ELSE 'NOT ALL'
  END AS all_privileges
from
  information_schema.column_privileges
-- You can uncomment here and specify a schema name and table_name if you want
where 
table_schema = 'public'
and table_name ='my_table'
group by
  table_schema,
  table_name,
  column_name,
  grantee



------------------------------------------------------------------------------------------------------------
-- RLS Polices
--To list all RLS policies in the current database and the qualifiers of each, you can run the following query:
-- This was modified slightly from this source: https://www.azureblue.io/cheat-sheet-postgres/
SELECT 
  n.nspname AS schema,
  c.relname AS table,
  CASE
    WHEN c.relrowsecurity THEN 'Enabled'::text
    ELSE 'Disabled'::text
  END AS rls_enabled,
  pol.polname AS policy,
  CASE
    WHEN pol.polpermissive THEN 'PERMISSIVE'::text
    ELSE 'RESTRICTIVE'::text
  END AS permissive,
  CASE
    WHEN pol.polroles = '{0}'::oid[] THEN string_to_array('public'::text, ''::text)::name[]
    ELSE 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 cmd,
  pg_get_expr(pol.polqual, pol.polrelid) AS qual,
  pg_get_expr(pol.polwithcheck, pol.polrelid) AS with_check
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_policy pol ON pol.polrelid = c.oid
-- You can uncomment here and specify a schema name if you want
WHERE 
c.relname NOT LIKE '%\_pkey' and c.relname NOT LIKE '%\_fkey' and c.relname NOT LIKE '%\_id_seq'
-- and n.nspname = 'public'
-- and c.relname ='my_table'
ORDER BY 
n.nspname,
c.relname;