ghcr.io/immich-app/postgres

#!/usr/bin/env bash
# Workload exerciser for the postgres reference image.
#
# Connects to a running postgres container and drives a representative set
# of operations — schema create, insert, select, drop — followed by a
# SIGHUP-triggered config reload. Returns 0 if every step succeeded.
#
# Required env: PGCONTAINER (target container name or id).
# Optional env: PGPASSWORD (defaults to "postgres"), PGUSER (defaults to
# "postgres"), PGDATABASE (defaults to "postgres").
set -euo pipefail

: "${PGCONTAINER:?PGCONTAINER must be set}"
: "${PGPASSWORD:=postgres}"
: "${PGUSER:=postgres}"
: "${PGDATABASE:=postgres}"

# Probes exec as the postgres user, never root — a root probe's own needs
# would pollute the derived minimum (the rabbitmq lesson).
psql_in_container() {
    docker exec --user postgres -e "PGPASSWORD=${PGPASSWORD}" "${PGCONTAINER}" \
        psql -v ON_ERROR_STOP=1 -U "${PGUSER}" -d "${PGDATABASE}" "$@"
}

# Wait until pg_isready.
deadline=$((SECONDS + 60))
until docker exec --user postgres "${PGCONTAINER}" pg_isready -U "${PGUSER}" -q; do
    if (( SECONDS >= deadline )); then
        echo "postgres did not become ready in 60s" >&2
        exit 1
    fi
    sleep 1
done

psql_in_container -c 'CREATE TABLE csd_smoke (id INT, label TEXT);'
psql_in_container -c "INSERT INTO csd_smoke VALUES (1, 'a'), (2, 'b'), (3, 'c');"
psql_in_container -c 'SELECT count(*) FROM csd_smoke;'
psql_in_container -c 'DROP TABLE csd_smoke;'

# Trigger a config reload (SIGHUP path).
docker kill --signal=SIGHUP "${PGCONTAINER}"
sleep 1
psql_in_container -c 'SELECT 1;'

# CORRECTNESS: the server (PID 1) completed the root->postgres drop. Load-bearing:
# with SETUID/SETGID dropped the entrypoint's gosu re-exec fails — a query-only
# check must not bless a server still running as root. /proc is parsed tool-free
# (no awk/cut — not guaranteed on minimal bases).
uid="$(docker exec "${PGCONTAINER}" sh -c \
    'while read -r k v _; do [ "$k" = "Uid:" ] && { echo "$v"; break; }; done </proc/1/status')"
if [ "${uid:-0}" = 0 ] || [ -z "${uid}" ]; then
    echo "postgres server is running as ROOT (privilege drop failed; uid='${uid}')" >&2
    exit 1
fi

#!/usr/bin/env bash
# Workload exerciser for the postgres reference image.
#
# Connects to a running postgres container and drives a representative set
# of operations — schema create, insert, select, drop — followed by a
# SIGHUP-triggered config reload. Returns 0 if every step succeeded.
#
# Required env: PGCONTAINER (target container name or id).
# Optional env: PGPASSWORD (defaults to "postgres"), PGUSER (defaults to
# "postgres"), PGDATABASE (defaults to "postgres").
set -euo pipefail

: "${PGCONTAINER:?PGCONTAINER must be set}"
: "${PGPASSWORD:=postgres}"
: "${PGUSER:=postgres}"
: "${PGDATABASE:=postgres}"

# Probes exec as the postgres user, never root — a root probe's own needs
# would pollute the derived minimum (the rabbitmq lesson).
psql_in_container() {
    docker exec --user postgres -e "PGPASSWORD=${PGPASSWORD}" "${PGCONTAINER}" \
        psql -v ON_ERROR_STOP=1 -U "${PGUSER}" -d "${PGDATABASE}" "$@"
}

# Wait until pg_isready.
deadline=$((SECONDS + 60))
until docker exec --user postgres "${PGCONTAINER}" pg_isready -U "${PGUSER}" -q; do
    if (( SECONDS >= deadline )); then
        echo "postgres did not become ready in 60s" >&2
        exit 1
    fi
    sleep 1
done

psql_in_container -c 'CREATE TABLE csd_smoke (id INT, label TEXT);'
psql_in_container -c "INSERT INTO csd_smoke VALUES (1, 'a'), (2, 'b'), (3, 'c');"
psql_in_container -c 'SELECT count(*) FROM csd_smoke;'
psql_in_container -c 'DROP TABLE csd_smoke;'

# Trigger a config reload (SIGHUP path).
docker kill --signal=SIGHUP "${PGCONTAINER}"
sleep 1
psql_in_container -c 'SELECT 1;'

# CORRECTNESS: the server (PID 1) completed the root->postgres drop. Load-bearing:
# with SETUID/SETGID dropped the entrypoint's gosu re-exec fails — a query-only
# check must not bless a server still running as root. /proc is parsed tool-free
# (no awk/cut — not guaranteed on minimal bases).
uid="$(docker exec "${PGCONTAINER}" sh -c \
    'while read -r k v _; do [ "$k" = "Uid:" ] && { echo "$v"; break; }; done </proc/1/status')"
if [ "${uid:-0}" = 0 ] || [ -z "${uid}" ]; then
    echo "postgres server is running as ROOT (privilege drop failed; uid='${uid}')" >&2
    exit 1
fi

# immich's postgres (a custom pgvector/vectorchord image) — minimum capabilities,
# derived by drop-test against immich's own deployment invocation.
#
# capabilities: cap_drop:[ALL] + cap_add:[CHOWN, DAC_OVERRIDE, SETGID, SETUID].
# All four are STARTUP caps, invisible to runtime observation: the entrypoint
# starts as root, chowns a FRESH data dir (CHOWN) it doesn't yet own
# (DAC_OVERRIDE for the ensuing writes), and drops to the postgres user (uid 999)
# via gosu (SETUID/SETGID). The declared cap_add additionally grants FOWNER, which
# is a genuine over-grant — it is not exercised even on a fresh, foreign-owned data
# dir (see the criteria doc's drop-test evidence).
#
# run_config records immich's invocation: root (no `user:` override), a persistent
# data-dir bind, the POSTGRES_* env. The minimum was derived under a fresh,
# foreign-owned data dir (a real first deploy), which is what makes CHOWN required;
# an already-initialised, postgres-owned volume skips the chown and needs fewer
# startup caps. See criteria/ghcr.io/immich-app/postgres.md.
# filesystem: read_only:true, tmpfs:[/etc/postgresql, /var/run/postgresql].
# postgres creates its socket + lock in /var/run/postgresql, and immich's
# entrypoint copies a tuned postgresql.conf into /etc/postgresql — both must be
# writable under a read-only rootfs. The data dir /var/lib/postgresql/data is a
# persistent VOLUME. /tmp was drop-tested and is NOT required. Derived by drop-test.
#
schema_version: "1.5"
image: ghcr.io/immich-app/postgres
reference_url: https://tmatens.github.io/container-security-profiles/profiles/ghcr.io/immich-app/postgres.html
applies_to:
  tags: ["14-vectorchord0.4.3-pgvectors0.2.0"]
status: validated
app_tier_verified:
  service: immich
  service_version: v2.7.5
  method: container-sec-derive scripts/apptier_verify.sh
  check: >-
    immich's released stack (immich-server v2.7.5 + valkey + this postgres) brought
    up with the derived minimum applied, driven via immich's real REST API — admin
    sign-up, login, upload a photo, read it back, metadata search — all passing.
  verified_date: "2026-07-04"
  result: pass
  over_hardening:
    applied: "dropped SETUID from the database"
    result: "database unhealthy (could not switch to the postgres user) — immich never starts"
dimensions:
  capabilities:
    cap_drop: [ALL]
    cap_add: [CHOWN, DAC_OVERRIDE, SETGID, SETUID]
    derivation:
      tool: container-sec-derive
      tool_version: "0.7.0"
      sidecar_schema_version: "1.5"
      observer: drop-test
      validated_image: ghcr.io/immich-app/postgres@sha256:bcf63357191b76a916ae5eb93464d65c07511da41e3bf7a8416db519b40b1c23
      validated_date: "2026-07-04"
      duration_seconds: 240
      confidence: high
      workload: profiles/workloads/postgres.sh
      workload_sha256: "7b8ad40aba2d709ae935fef8fa6aaac3c5c36a54bd9666524d746f0a2c0f6fe1"
      validated_via: [drop-test, ci-smoke]
      observation_backend:
        ig_version: v0.51.0
        gadgets: []
      run_config:
        user: ""
        command: []
        entrypoint: ""
        network: ""
        pid: ""
        devices: []
        security_opt: ["no-new-privileges:true"]
        mounts: ["${DB_DATA_LOCATION}:/var/lib/postgresql/data"]
        env: [POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB, POSTGRES_INITDB_ARGS]
      drop_test:
        checks:
          - {removed: CHOWN, required: true, observed: "workload failed / not ready"}
          - {removed: DAC_OVERRIDE, required: true, observed: "workload failed / not ready"}
          - {removed: FOWNER, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: FSETID, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: MKNOD, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: NET_RAW, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: SETGID, required: true, observed: "workload failed / not ready"}
          - {removed: SETUID, required: true, observed: "workload failed / not ready"}
          - {removed: SETFCAP, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: SETPCAP, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: NET_BIND_SERVICE, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: SYS_CHROOT, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: KILL, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: AUDIT_WRITE, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
  filesystem:
    read_only: true
    tmpfs: [/etc/postgresql, /var/run/postgresql]
    derivation:
      tool: container-sec-derive
      tool_version: "0.7.0"
      sidecar_schema_version: "1.5"
      observer: drop-test
      validated_image: ghcr.io/immich-app/postgres@sha256:bcf63357191b76a916ae5eb93464d65c07511da41e3bf7a8416db519b40b1c23
      validated_date: "2026-07-16"
      duration_seconds: 72
      confidence: high
      workload: profiles/workloads/postgres.sh
      workload_sha256: "7b8ad40aba2d709ae935fef8fa6aaac3c5c36a54bd9666524d746f0a2c0f6fe1"
      validated_via: [drop-test, ci-smoke]
      observation_backend:
        ig_version: v0.51.0
        gadgets: []
      run_config:
        user: ""
        command: []
        entrypoint: ""
        network: ""
        pid: ""
        devices: []
        security_opt: []
        mounts: []
        env: [POSTGRES_PASSWORD]
      drop_test:
        checks:
          - {removed: /var/run/postgresql, required: true, observed: "postgres did not become ready in 60s"}
          - {removed: /etc/postgresql, required: true, observed: "container failed to start: cp: cannot create regular file '/etc/postgresql/postgresql.conf': Read-only file system"}
          - {removed: /tmp, required: false, observed: "(1 row)"}