Database Configuration

-- As a Postgres superuser / admin
CREATE ROLE dc_app WITH LOGIN PASSWORD '<choose-a-strong-password>';
CREATE DATABASE dc OWNER dc_app;

-- Restrict the role to its own database
REVOKE ALL ON DATABASE dc FROM PUBLIC;
GRANT CONNECT ON DATABASE dc TO dc_app;

CREATE ROLE dc_readonly WITH LOGIN PASSWORD '<password>';
GRANT CONNECT ON DATABASE dc TO dc_readonly;
\c dc
GRANT USAGE ON SCHEMA public TO dc_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dc_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dc_readonly;

jdbc:postgresql://postgres.example.com:5432/dc?ssl=true&sslmode=verify-full&sslrootcert=/etc/ssl/certs/db-ca.crt

apiVersion: batch/v1
kind: CronJob
metadata:
  name: dc-postgres-backup
  namespace: <your-namespace>
spec:
  schedule: "0 2 * * *"  # daily at 02:00
  jobTemplate:
    spec:
      template:
        spec:
          containers:
            - name: backup
              image: postgres:15-alpine
              command:
                - /bin/sh
                - -c
                - |
                  pg_dump -h <your-db-host> \
                    -U "$POSTGRES_USER" \
                    -d <your-database> \
                    -F c -f /backup/dc-$(date +%Y%m%d).dump
              env:
                - name: POSTGRES_USER
                  valueFrom: { secretKeyRef: { name: dc-db-secret, key: POSTGRES_USER } }
                - name: PGPASSWORD
                  valueFrom: { secretKeyRef: { name: dc-db-secret, key: POSTGRES_PASSWORD } }
              volumeMounts:
                - { name: backup-storage, mountPath: /backup }
          volumes:
            - name: backup-storage
              persistentVolumeClaim:
                claimName: dc-backup-pvc
          restartPolicy: OnFailure

# Copy the dump into a pod that can reach the database (the postgres image is fine).
kubectl cp dc-20260101.dump <namespace>/<helper-pod>:/tmp/

kubectl exec -it <helper-pod> -n <namespace> -- /bin/sh

# Inside the pod — `-c` drops conflicting objects before restoring.
pg_restore -h <your-db-host> -U <your-user> -d <your-database> -c /tmp/dc-20260101.dump