01_init_dba.sh

#!/usr/bin/env bash
set -euo pipefail

# ---------------------------------------------------------
# 1) POSTGRES (MAIN DB) – EXTENSIONS + GLOBAL VISIBILITY
# ---------------------------------------------------------
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname postgres <<-EOSQL

-- Core monitoring extensions
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;
CREATE EXTENSION IF NOT EXISTS pgaudit;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- pg_stat_statements must be readable by everyone
GRANT SELECT ON ALL TABLES IN SCHEMA public TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;

-- Make sure stats are global
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.track_utility = on;

-- PGAUDIT full visibility
ALTER SYSTEM SET pgaudit.log = 'all';
ALTER SYSTEM SET pgaudit.log_relation = on;
ALTER SYSTEM SET pgaudit.log_statement_once = off;

SELECT pg_reload_conf();

-- ---------------------------------------------------------
-- POWA REPOSITORY DATABASE
-- ---------------------------------------------------------
CREATE DATABASE powa
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       LC_COLLATE = 'C'
       LC_CTYPE = 'C'
       TEMPLATE = template0;

EOSQL

# ---------------------------------------------------------
# 2) POWA DATABASE – REPOSITORY + COLLECTOR
# ---------------------------------------------------------
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname powa <<-EOSQL

-- Required dependencies
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- POWA core
CREATE EXTENSION IF NOT EXISTS powa;

-- Ensure POWA sees EVERYTHING
GRANT ALL PRIVILEGES ON DATABASE powa TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO postgres;

ALTER DEFAULT PRIVILEGES
  IN SCHEMA public
  GRANT ALL ON TABLES TO postgres;

-- Register local server explicitly (failsafe)
SELECT powa_register_server(
    'local-postgres',
    current_setting('server_version_num')::int,
    current_setting('server_version'),
    current_setting('data_directory')
);

EOSQL

echo "POWA FULL initialization completed successfully"