E2E Test Case: DB TLS + non-default privileged user

A concrete, scripted set of end-to-end cases covering encrypted-by-default PostgreSQL connections and support for a non-default privileged DB username, run against a real cluster (e.g. minikube). Cases run mostly at the kubectl exec / DB level rather than through the gateway, since what’s being verified is the connection itself (is it encrypted? which account is it using?) — a couple also reuse the shared gateway + API-key setup from Feature-specific testing through the gateway with an API key to confirm the stack is fully functional, not just that the DB layer looks right in isolation.

What it covers

By default, Magda now:

On a default render, PGSSLMODE is carried on 9 components: the 4 Node services that talk to Postgres directly (authorization-api, content-api, gateway, registry-api), the 4 migrator Jobs (authorization-db, content-db, registry-db, session-db), and the registry-db auto-vacuum CronJob. With global.enableMultiTenants=true, tenant-api and the tenant-db migrator also render, bringing the total to 11.

The objective assertion used throughout — run against the DB pod as the built-in postgres superuser — is:

SELECT a.datname, a.usename, s.ssl, s.version
FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid)
WHERE a.usename IS NOT NULL;

ssl = t (with a TLS version, e.g. TLSv1.3) confirms the backend negotiated TLS for that connection; ssl = f confirms it did not.

Setup

These cases exec directly into the primary PostgreSQL pod rather than port-forwarding, since the assertions authenticate using a password that the chart injects into the pod as an environment variable:

DBPOD=$(kubectl get pod -n magda -l app.kubernetes.io/name=combined-db-postgresql-pg17 -o name | head -1)

Which password variable to use depends on the case, because the two are not both always present:

Case Database Privileged username Use
C1, C3, C4 in-cluster default (postgres) $POSTGRES_PASSWORD
C2 in-cluster custom (e.g. magda_admin) $POSTGRES_POSTGRES_PASSWORD
C5 external custom (e.g. magda_admin) the external DB’s own credentials

POSTGRES_POSTGRES_PASSWORD holds the built-in postgres superuser’s password, and it exists only when both conditions hold: a non-default privileged username is configured and the database is in-cluster. The chart gates the postgresql-postgres-password secret key on exactly that pair (db-main-account-secret.yaml: (ne $username "postgres") and (not $usesExternalDb)), and the PostgreSQL subchart wires the matching environment variable under an equivalent condition. Where it does not exist, PGPASSWORD would expand to an empty string and psql would fail to authenticate.

C5 falls outside this entirely: it runs against an external database (global.useAwsRdsDb=true), so Magda neither creates nor injects that key — connect with whatever credentials the external instance was provisioned with. Its assertions run against the standalone PostgreSQL you stand up, not the in-cluster pod.

Each case below already uses the correct variable; if you adapt a command, check this table first.

(Substitute the appropriate pod selector if you’re running against a non-combined DB topology.)

Case C1 — fresh install, stock defaults

Verifies TLS is on for every logical database and every connecting role with zero extra configuration.

kubectl create namespace magda
helm install magda oci://ghcr.io/magda-io/charts/magda -n magda
# wait until settled
kubectl get pods -n magda --no-headers | grep -vE "Running|Completed"   # expect empty

DBPOD=$(kubectl get pod -n magda -l app.kubernetes.io/name=combined-db-postgresql-pg17 -o name | head -1)
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_PASSWORD psql -U postgres -c "
    SELECT a.datname, a.usename, s.ssl, s.version
    FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid)
    WHERE a.usename IS NOT NULL ORDER BY 1,2;"'

Expected: ssl = t for every client backend across all logical databases, and for registry-api’s own connection. All migrator Jobs Completed.

Case C2 — fresh install, hardening profile

The headline case: TLS-by-default and a non-default privileged username together, with no manually created secret and no manual grant.

kubectl create namespace magda
helm install magda oci://ghcr.io/magda-io/charts/magda -n magda \
  --set global.postgresql.auth.username=magda_admin

# both password keys were auto-created
kubectl get secret -n magda db-main-account-secret -o jsonpath='{.data}' | tr ',' '\n'
# expect BOTH postgresql-password (magda_admin's) AND postgresql-postgres-password (the built-in postgres superuser's)

# magda_admin has Create role + Create DB, but is NOT a superuser
DBPOD=$(kubectl get pod -n magda -l app.kubernetes.io/name=combined-db-postgresql-pg17 -o name | head -1)
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_POSTGRES_PASSWORD psql -U postgres -tAc "\du magda_admin"'
# expect: Create role, Create DB -- and NOT Superuser

# the restricted `client` role was created by the migrators, using magda_admin
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_POSTGRES_PASSWORD psql -U postgres -tAc "\du client"'

# every `client`-role connection is TLS
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_POSTGRES_PASSWORD psql -U postgres -tAc "
    SELECT count(*) FILTER (WHERE s.ssl), count(*)
    FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid)
    WHERE a.usename = '"'"'client'"'"';"'
# expect both counts equal and non-zero

Case C3 — upgrade in place from a pre-TLS release

The riskiest case: an existing cluster, seeded with real data, upgrading from a release that predates both TLS and the migrator’s explicit sslmode support, in one step — and with no manual intervention.

kubectl create namespace magda
helm install magda oci://ghcr.io/magda-io/charts/magda --version <prior-release> -n magda
# wait until settled, then seed a dataset through the API so there is data to lose
# (see docs/docs/e2e-cluster-deployment-test.md for the gateway + API key setup)

DBPOD=$(kubectl get pod -n magda -l app.kubernetes.io/name=combined-db-postgresql-pg17 -o name | head -1)
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_PASSWORD psql -U postgres -d registry -tAc \
   "SELECT max(version) FROM schema_version WHERE success"'
# record this value as BASELINE_VERSION

helm upgrade magda oci://ghcr.io/magda-io/charts/magda --version <target-release> -n magda

Assertions after the upgrade settles:

# migration history was baselined at the recorded version -- nothing re-applied, nothing failed
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_PASSWORD psql -U postgres -d registry -c \
   "SELECT installed_rank, version, description, type, success FROM flyway_schema_history ORDER BY installed_rank"'
# expect: rank 1 is the baseline at BASELINE_VERSION; zero rows with success = false

# TLS now in force
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_PASSWORD psql -U postgres -tAc \
   "SELECT count(*) FILTER (WHERE s.ssl), count(*) FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid) WHERE a.usename = '"'"'client'"'"';"'

# the seeded dataset is still readable through the API; nothing crash-looped
kubectl get pods -n magda --no-headers | grep -vE "Running|Completed"   # expect empty

Record how long the pods took to settle and whether any needed a restart — that’s the “upgrade blip” this case is really checking for.

Case C4 — rollback / escape hatch

Confirms global.postgresql.client.sslmode=disable fully restores the previous (plaintext) client behaviour, for operators who need to roll back.

helm upgrade magda oci://ghcr.io/magda-io/charts/magda -n magda \
  --set global.postgresql.client.sslmode=disable
# wait until settled
DBPOD=$(kubectl get pod -n magda -l app.kubernetes.io/name=combined-db-postgresql-pg17 -o name | head -1)
kubectl exec -n magda $DBPOD -- bash -c \
  'PGPASSWORD=$POSTGRES_PASSWORD psql -U postgres -tAc \
   "SELECT DISTINCT s.ssl FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid) WHERE a.usename = '"'"'client'"'"';"'

Expected: f only. The stack remains fully functional — confirm with a search and a dataset fetch through the gateway (shared setup, as above).

The corresponding rollback for the in-cluster server’s TLS listener is a per-DB-chart setting — <db-chart>.magda-postgres.postgresql.tls.enabled=false, e.g. combined-db.magda-postgres.postgresql.tls.enabled=false. It is not a global.* value: it is consumed directly by the packaged PostgreSQL subchart’s own templates, and a wrapper chart cannot compute a subchart value from another value, so a global switch could only ever be a second source of truth that disagreed with what the StatefulSet actually does. Turning the listener off while clients still resolve sslmode to require is rejected at render time.

Case C5 — enforced-SSL external DB simulation

The closest reachable equivalent of connecting to a managed provider (RDS, with rds.force_ssl=1) without needing real cloud infrastructure: stand up a standalone PostgreSQL that refuses plaintext connections, then point Magda at it exactly as if it were RDS.

kubectl create namespace extdb
# Deploy a standalone bitnami postgresql with TLS on and a non-`postgres` admin,
# then patch pg_hba.conf so only hostssl entries remain:
#   hostssl all all 0.0.0.0/0 scram-sha-256
# and reload: psql -U postgres -c "SELECT pg_reload_conf()"
# Confirm plaintext is refused:
#   PGSSLMODE=disable psql -h <svc> -U magda_admin   -> must FAIL with
#   "no pg_hba.conf entry ... no encryption"

Privileges the external admin account needs. CREATEDB + CREATEROLE alone are not sufficient. The registry migrator keeps its data in the postgres database and its V2_4 migration runs CREATE EXTENSION IF NOT EXISTS "uuid-ossp", which requires CREATE privilege on that database (the extension is trusted in PostgreSQL 13+, so a non-superuser with CREATE on the current database can install it). For the in-cluster database Magda grants this automatically — its bundled PostgreSQL is configured with auth.database: postgres, so the chart runs GRANT ALL PRIVILEGES ON DATABASE postgres TO <privileged user> at first boot. Managed providers grant the equivalent to their admin account (RDS rds_superuser, etc.). For this simulation you must grant it yourself, or the registry-db migrator fails with permission denied to create extension "uuid-ossp" and the install aborts with BackoffLimitExceeded:

# as the built-in postgres superuser, matching what the in-cluster chart does:
psql -U postgres -c 'ALTER ROLE magda_admin WITH CREATEDB CREATEROLE'
psql -U postgres -c 'GRANT ALL PRIVILEGES ON DATABASE postgres TO magda_admin'

Then install Magda against it:

kubectl create namespace magda
helm install magda oci://ghcr.io/magda-io/charts/magda -n magda \
  --set global.useCombinedDb=false \
  --set global.useAwsRdsDb=true \
  --set global.awsRdsEndpoint=<extdb service DNS name> \
  --set global.postgresql.auth.username=magda_admin

Expected: all migrators complete and every service connects successfully — without relaxing the server’s SSL enforcement. This also exercises the ExternalName service path and the auth.username validation that rejects the default postgres account for external databases.

Known limitation: CREATEDB/CREATEROLE grant only runs on first boot

The grant that lets a non-default privileged user create databases and the restricted client role (case C2) is delivered via a PostgreSQL initdb script, which the bitnami chart only runs once, when the data directory is first initialised. If you take an existing in-cluster deployment that was installed with the default postgres user and later switch global.postgresql.auth.username to a custom value, the grant will not be applied retroactively — the new user will exist (or fail to, depending on how it was provisioned) without the CREATEDB/CREATEROLE privileges the chart assumes it has, and the DB migrators will fail with a permission error.

The manual remedy is a one-time, one-line fix connected as the built-in postgres superuser:

ALTER ROLE magda_admin CREATEDB CREATEROLE;

(substituting your actual privileged username). There is no scripted migration for this — it’s a manual step for anyone changing the privileged username on a cluster that already has data.

Cleanup

Between cases, purge the deployment so each starts from a clean slate:

helm uninstall magda -n magda 2>/dev/null || true
kubectl delete namespace magda --wait=true --timeout=180s 2>/dev/null || true
minikube ssh -- 'sudo rm -rf /tmp/hostpath-provisioner/magda'
kubectl get ns magda   # expect NotFound

For C5, also tear down the extdb namespace. For any case that used the shared gateway + API-key setup, stop minikube tunnel and remove any throwaway test user / API key you created.