The missing database migration for PgHero, with permission isolation setup (separate schema for PgHero, read only access for the application schema)

pghero.sql

-- database
CREATE DATABASE "app" WITH OWNER = "azure_pg_admin";
\c app

-- app user (just as an example)
CREATE ROLE "appuser" LOGIN ENCRYPTED PASSWORD '114514' ADMIN azure_pg_admin;
GRANT ALL PRIVILEGES ON DATABASE "app" TO "appuser";
GRANT ALL ON SCHEMA public TO "appuser";

-- PgHero user
CREATE ROLE "pghero" LOGIN ENCRYPTED PASSWORD '1919810' ADMIN azure_pg_admin;

-- enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- PgHero schema
CREATE SCHEMA IF NOT EXISTS pghero;

-- history storage
CREATE TABLE IF NOT EXISTS "pghero"."pghero_query_stats" (
  "id" bigserial primary key,
  "database" text,
  "user" text,
  "query" text,
  "query_hash" bigint,
  "total_time" float,
  "calls" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero"."pghero_query_stats" ("database", "captured_at");

CREATE TABLE IF NOT EXISTS "pghero"."pghero_space_stats" (
  "id" bigserial primary key,
  "database" text,
  "schema" text,
  "relation" text,
  "size" bigint,
  "captured_at" timestamp
);
CREATE INDEX ON "pghero"."pghero_space_stats" ("database", "captured_at");

-- view queries
CREATE OR REPLACE FUNCTION pghero.pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$
  SELECT * FROM pg_catalog.pg_stat_activity;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE VIEW pghero.pg_stat_activity AS SELECT * FROM pghero.pg_stat_activity();

-- kill queries
CREATE OR REPLACE FUNCTION pghero.pg_terminate_backend(pid int) RETURNS boolean AS
$$
  SELECT * FROM pg_catalog.pg_terminate_backend(pid);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- query stats
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE VIEW pghero.pg_stat_statements AS SELECT * FROM pghero.pg_stat_statements();

-- query stats reset
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset() RETURNS void AS
$$
  SELECT public.pg_stat_statements_reset();
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- improved query stats reset for Postgres 12+ - delete for earlier versions
CREATE OR REPLACE FUNCTION pghero.pg_stat_statements_reset(userid oid, dbid oid, queryid bigint) RETURNS void AS
$$
  SELECT public.pg_stat_statements_reset(userid, dbid, queryid);
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

-- suggested indexes
CREATE OR REPLACE FUNCTION pghero.pg_stats() RETURNS
TABLE(schemaname name, tablename name, attname name, null_frac real, avg_width integer, n_distinct real) AS
$$
  SELECT schemaname, tablename, attname, null_frac, avg_width, n_distinct FROM pg_catalog.pg_stats;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE VIEW pghero.pg_stats AS SELECT * FROM pghero.pg_stats();

-- user setup
ALTER ROLE "pghero" SET search_path = pghero, pg_catalog, public;
ALTER ROLE "pghero" SET lock_timeout = '1s';

-- database level permissions
GRANT CONNECT ON DATABASE "app" TO "pghero";

-- pghero schema permissions
GRANT ALL ON SCHEMA pghero TO "pghero";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA pghero TO "pghero";
ALTER DEFAULT PRIVILEGES IN SCHEMA pghero GRANT ALL PRIVILEGES ON TABLES TO "pghero";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA pghero TO "pghero";
ALTER DEFAULT PRIVILEGES IN SCHEMA pghero GRANT ALL PRIVILEGES ON SEQUENCES TO "pghero";

-- other schemas (run for every other schema)
GRANT USAGE ON SCHEMA public TO "pghero";
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA public TO "pghero";
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO "pghero";
ALTER DEFAULT PRIVILEGES FOR ROLE "app" IN SCHEMA public GRANT SELECT, USAGE ON SEQUENCES TO "pghero";

postgresql.conf

pg_stat_statements.save = ON
pg_stat_statements.track = ALL