id: postgres
name: "PostgreSQL"
version: "1.0"
description: "Zentrale relationale Datenbank für IT.Box-Dienste"
category: infrastructure

links:
  homepage: "https://www.postgresql.org/"
  docs: "https://www.postgresql.org/docs/18/"
  github: "https://github.com/postgres/postgres"

image:
  name: "postgres"
  tag: "latest"

ports:
  - host: 8100
    container: 5432
    protocol: tcp
    description: "PostgreSQL-Port (nur lokal)"
    bind: "127.0.0.1"

volumes:
  - host: "/opt/appctl/data/postgres"
    container: "/var/lib/postgresql"
    description: "Datenbank-Dateien"

  - host: "/opt/appctl/config/postgres/init"
    container: "/docker-entrypoint-initdb.d"
    description: "Initialisierungs-Skripte (nur beim ersten Start)"
    readonly: true

environment:
  - key: "POSTGRES_USER"
    value: "itbox"

  - key: "POSTGRES_PASSWORD"
    value: "${POSTGRES_PASSWORD}"

  - key: "POSTGRES_DB"
    value: "itbox"

  - key: "POSTGRES_STUDENT_PASSWORD"
    value: "${POSTGRES_STUDENT_PASSWORD}"

secrets:
  - key: "POSTGRES_PASSWORD"
    description: "PostgreSQL-Passwort fuer den itbox-Benutzer"
    required: true
    generate: "password"

  - key: "POSTGRES_STUDENT_PASSWORD"
    description: "PostgreSQL-Passwort fuer den student_write-Benutzer"
    required: true
    generate: "password"

depends_on: []

configs:
  - path: "/opt/appctl/config/postgres/init/create-databases.sh"
    description: "Erstellt Datenbanken fuer alle IT.Box-Dienste"
    content: |
      #!/bin/bash
      set -e

      # Datenbanken fuer IT.Box-Dienste erstellen
      # Dieses Skript laeuft nur beim ersten Start (leeres data-Verzeichnis)

      for db in langflow openwebui n8n; do
          echo "Creating database: $db"
          psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" \
              -c "CREATE DATABASE $db OWNER $POSTGRES_USER;"
      done

      # Schueler-Datenbank mit eingeschraenktem Benutzer
      echo "Creating database: students"
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" \
          -c "CREATE DATABASE students OWNER $POSTGRES_USER;"

      echo "Creating user: student_write"
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" \
          -c "CREATE USER student_write WITH PASSWORD '$POSTGRES_STUDENT_PASSWORD';"

      # Rechte fuer student_write auf students-DB (nur DML, kein DDL)
      psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "students" <<-EOSQL
          GRANT CONNECT ON DATABASE students TO student_write;
          GRANT USAGE ON SCHEMA public TO student_write;
          GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO student_write;
          GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO student_write;
          ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO student_write;
          ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO student_write;
      EOSQL

      echo "All databases and users created successfully."

post_install:
  messages:
    - "PostgreSQL laeuft auf localhost:8100 (nur lokal erreichbar)"
    - "Benutzer: itbox / <generiertes Passwort>"
    - "Datenbanken: itbox, langflow, openwebui, n8n, students"
    - "Schueler-DB: students / student_write (nur SELECT, INSERT, UPDATE, DELETE)"
    - "Andere Container verbinden sich automatisch ueber Docker-Netzwerk"
    - "Passwort anzeigen: sudo cat /opt/appctl/compose/.env | grep POSTGRES_PASSWORD"
    - "Schueler-Passwort: sudo cat /opt/appctl/compose/.env | grep POSTGRES_STUDENT_PASSWORD"
    - "Manueller Zugriff: docker exec -it postgres psql -U itbox"

  ports_info:
    - port: 8100
      protocol: tcp
      description: "PostgreSQL - Nur lokal erreichbar (127.0.0.1)"

resources:
  ram_mb: 150
  cpu_cores: 0.3
  disk_mb: 100

warnings:
  - "Port nur auf 127.0.0.1 gebunden - kein Zugriff von aussen moeglich"
  - "Init-Skript laeuft nur beim ersten Start (leeres data-Verzeichnis)"

tags:
  - database
  - postgresql
  - infrastructure
