Accepted — 2026-05-16
Before this change, the application connected to PostgreSQL as the cluster superuser created by the postgres Docker image's POSTGRES_USER environment variable. A superuser bypasses row-level security, can drop any table, and has full schema control. That blast radius is unacceptable: a single SQL-injection vulnerability, a compromised container, or even an application bug could exfiltrate or destroy the entire database.
GitHub issue: #13 security: remove database superuser from application config
Split the application's DB access into three roles, none of which is a superuser:
| Role | Purpose | Privileges |
|------|---------|-----------|
| europelogin_migrate | Owns the schema. Runs Flyway DDL at app startup. | ALL ON SCHEMA public — can create/alter/drop tables. |
| europelogin_app | Runtime user. Used by Spring's primary datasource for all SELECT/INSERT/UPDATE/DELETE queries. | USAGE on schema. SELECT, INSERT, UPDATE, DELETE on tables created by europelogin_migrate. No DDL. |
| europelogin_ro | Read-only reporting/analytics. | USAGE on schema. SELECT on tables created by europelogin_migrate. |
Spring Boot's spring.flyway.user / spring.flyway.password properties point at europelogin_migrate. The main spring.datasource.username / spring.datasource.password point at europelogin_app. So migrations run once at startup as the privileged migrate user, then the connection pool re-binds as the app user for the remainder of the process lifetime.
ALTER DEFAULT PRIVILEGES FOR USER europelogin_migrate IN SCHEMA public is used so that every future table created by a new Flyway migration is automatically readable/writable by europelogin_app (and readable by europelogin_ro) without a per-migration grant step.
db/init/01-create-app-users.sh — mounted into the postgres container at /docker-entrypoint-initdb.d/. Runs once on first DB initialization. Reads passwords from env vars.docker-compose.yml and docker-compose.local.yml — mount the init script, set the three role passwords, point the backend at the app user, point Flyway at the migrate user.helm/europe-login/values.yaml and helm/europe-login/templates/deployment-backend.yaml — split the europe-login-secrets keys into database-app-username/database-app-password and database-migrate-username/database-migrate-password. The user must update the live Kubernetes Secret (via 1Password → Sealed Secrets) before deploying this version.backend/src/main/resources/application.yml — add spring.flyway.url/user/password and default spring.datasource.username to europelogin_app.Positive
Negative / migration cost
/docker-entrypoint-initdb.d/ runs only on a freshly-created data directory, so production migrations must be performed manually once via psql. A follow-up runbook in ocs/europeLogin/processes/ will document the steps.database-app-* and database-migrate-* keys. The deploy will fail-closed if they are missing, which is intentional.
Reacties