96 lines
4.1 KiB
Bash
96 lines
4.1 KiB
Bash
|
#!/bin/bash
|
||
|
|
# =============================================================================
|
||
|
|
# eBdesk SiCABE — PostgreSQL Multi-Database Init
|
||
|
|
# =============================================================================
|
||
|
|
# Script ini dijalankan OTOMATIS oleh PostgreSQL Docker entrypoint pada
|
||
|
|
# FIRST STARTUP saja (saat volume data masih kosong).
|
||
|
|
#
|
||
|
|
# Membuat database dan user terpisah untuk setiap komponen SiCABE:
|
||
|
|
# - keycloak → Governance (IAM)
|
||
|
|
# - nessie → Storage (Metadata Catalog: Iceberg REST + version store)
|
||
|
|
# - airflow → Ingestion (Orchestrator)
|
||
|
|
# - openmetadata_db → Governance (Metadata Platform)
|
||
|
|
#
|
||
|
|
# Variable environment yang harus tersedia (di-pass ke container postgres):
|
||
|
|
# POSTGRES_USER, POSTGRES_PASSWORD (superuser — sudah built-in)
|
||
|
|
# KEYCLOAK_DB_PASSWORD, NESSIE_DB_PASSWORD
|
||
|
|
# AIRFLOW_DB_PASSWORD, OPENMETADATA_DB_PASSWORD
|
||
|
|
# =============================================================================
|
||
|
|
|
||
|
|
set -e
|
||
|
|
|
||
|
|
# ─── Fungsi: Buat database, user, dan grant schema permissions ────────────
|
||
|
|
create_service_db() {
|
||
|
|
local db_name="$1"
|
||
|
|
local db_user="$2"
|
||
|
|
local db_pass="$3"
|
||
|
|
|
||
|
|
echo "[SiCABE] Creating database '${db_name}' with user '${db_user}'..."
|
||
|
|
|
||
|
|
# Create user (if not exists) — connect to POSTGRES_DB (keycloak)
|
||
|
|
psql -v ON_ERROR_STOP=1 --username "${POSTGRES_USER}" --dbname "${POSTGRES_DB}" <<-EOSQL
|
||
|
|
DO \$\$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '${db_user}') THEN
|
||
|
|
CREATE ROLE ${db_user} LOGIN PASSWORD '${db_pass}';
|
||
|
|
END IF;
|
||
|
|
END
|
||
|
|
\$\$;
|
||
|
|
EOSQL
|
||
|
|
|
||
|
|
# Create database (if not exists) with owner
|
||
|
|
psql -v ON_ERROR_STOP=1 --username "${POSTGRES_USER}" --dbname "${POSTGRES_DB}" <<-EOSQL
|
||
|
|
SELECT 'CREATE DATABASE ${db_name} OWNER ${db_user}'
|
||
|
|
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '${db_name}')\gexec
|
||
|
|
EOSQL
|
||
|
|
|
||
|
|
# Grant all privileges on the database
|
||
|
|
psql -v ON_ERROR_STOP=1 --username "${POSTGRES_USER}" --dbname "${db_name}" <<-EOSQL
|
||
|
|
GRANT ALL ON SCHEMA public TO ${db_user};
|
||
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${db_user};
|
||
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${db_user};
|
||
|
|
ALTER SCHEMA public OWNER TO ${db_user};
|
||
|
|
EOSQL
|
||
|
|
|
||
|
|
echo "[SiCABE] ✓ Database '${db_name}' siap."
|
||
|
|
}
|
||
|
|
|
||
|
|
# ─── Validasi env vars ─────────────────────────────────────────────────────
|
||
|
|
if [[ -z "${POSTGRES_USER}" ]]; then
|
||
|
|
echo "[SiCABE] ERROR: POSTGRES_USER tidak terdefinisi!"
|
||
|
|
exit 1
|
||
|
|
fi
|
||
|
|
|
||
|
|
# ─── Buat database & user untuk setiap service ─────────────────────────────
|
||
|
|
# Urutan: sesuai fase deployment
|
||
|
|
|
||
|
|
# Phase 1 — Governance (IAM)
|
||
|
|
create_service_db "keycloak" "keycloak" "${KEYCLOAK_DB_PASSWORD}"
|
||
|
|
|
||
|
|
# Phase 2 — Storage (Metadata Catalog)
|
||
|
|
create_service_db "nessie" "nessie" "${NESSIE_DB_PASSWORD}"
|
||
|
|
|
||
|
|
# Nessie (JDBC2 version store) membutuhkan schema dibuat lebih dulu.
|
||
|
|
# Tabel-tabel Nessie akan di-auto-create oleh server saat startup.
|
||
|
|
psql -v ON_ERROR_STOP=1 --username "${POSTGRES_USER}" --dbname "nessie" <<-EOSQL
|
||
|
|
CREATE SCHEMA IF NOT EXISTS nessie AUTHORIZATION nessie;
|
||
|
|
EOSQL
|
||
|
|
|
||
|
|
# Phase 5 — Ingestion (Orchestrator)
|
||
|
|
create_service_db "airflow" "airflow" "${AIRFLOW_DB_PASSWORD}"
|
||
|
|
|
||
|
|
# Phase 6 — Governance (Metadata Platform)
|
||
|
|
create_service_db "openmetadata_db" "openmetadata" "${OPENMETADATA_DB_PASSWORD}"
|
||
|
|
|
||
|
|
# Pipeline — database SUMBER contoh untuk DAG `sicabe_batch_daily`
|
||
|
|
# (task `ingest_to_bronze` membaca public.orders via Sling). Dimiliki superuser
|
||
|
|
# (POSTGRES_USER) sehingga tidak perlu role tambahan. Tabel + 6 baris contoh
|
||
|
|
# diisi oleh `scripts/seed-source-postgres.sh` (dipanggil bootstrap step 6).
|
||
|
|
psql -v ON_ERROR_STOP=1 --username "${POSTGRES_USER}" --dbname "${POSTGRES_DB}" <<-EOSQL
|
||
|
|
SELECT 'CREATE DATABASE sicabe_data OWNER ${POSTGRES_USER}'
|
||
|
|
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'sicabe_data')\gexec
|
||
|
|
EOSQL
|
||
|
|
echo "[SiCABE] ✓ Database 'sicabe_data' siap (sumber contoh pipeline)."
|
||
|
|
|
||
|
|
echo "[SiCABE] ✅ Semua database dan user berhasil dibuat."
|