Database Expertise for Every Industry

-- 1. Audit public schema grants on sensitive business tables
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee IN ('PUBLIC', 'anon')
  AND table_schema NOT IN ('pg_catalog', 'information_schema');

-- 2. Audit active database connections and client authentication methods
SELECT usename, client_addr, count(*) AS active_conns, state
FROM pg_stat_activity
GROUP BY usename, client_addr, state
ORDER BY active_conns DESC;

-- 1. Identify slow multi-tenant queries consuming high CPU & I/O
SELECT
  round((total_exec_time / 1000)::numeric, 2) AS total_seconds,
  calls,
  round((mean_exec_time)::numeric, 2) AS mean_ms,
  substr(query, 1, 60) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- 2. Check connection pool saturation against max_connections
SELECT
  count(*) AS current_connections,
  current_setting('max_connections')::int AS max_conns,
  round((count(*)::numeric / current_setting('max_connections')::int) * 100, 1) AS pool_utilization_pct
FROM pg_stat_activity;