docker.io/library/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

# postgres minimum security profile (default root-then-gosu-drop invocation),
# derived by drop-test. Two dimensions, applied as a unit:
#  - filesystem: read_only:true + tmpfs:[/run/postgresql]. All runtime writes land
#    on the data VOLUME; the socket dir is a startup-only write (attach-window-
#    blind to observation), established by verification — postgres FAILS under
#    read_only:true with no tmpfs ("could not create lock file
#    /var/run/postgresql/...: Read-only file system").
#  - capabilities: cap_drop:[ALL] + cap_add:[CHOWN, DAC_OVERRIDE, SETGID, SETUID].
#    All four are STARTUP caps (gosu root->postgres drop + initdb) invisible to
#    runtime observation. Derived under the filesystem dimension's recommendation
#    (read_only + tmpfs), because the profile is applied as a unit: the root-owned
#    tmpfs socket dir must be chowned to the postgres user → CHOWN, which is not
#    required when caps are derived on a writable rootfs. The combined profile
#    (both dimensions together) is workload-verified.
#
# run_config records the invocation each minimum was derived under: the default
# (no `user:` override), so the root->postgres drop happens. Run with `user:` set,
# postgres skips the drop and the capability minimum does not apply.
schema_version: "1.5"
image: docker.io/library/postgres
reference_url: https://tmatens.github.io/container-security-profiles/profiles/docker.io/library/postgres.html
applies_to:
  tags: ["16"]
status: validated
dimensions:
  filesystem:
    read_only: true
    tmpfs: [/run/postgresql]
    derivation:
      tool: container-sec-derive
      tool_version: "0.6.0"
      sidecar_schema_version: "1.5"
      observer: drop-test
      validated_image: docker.io/library/postgres@sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20
      validated_date: "2026-07-17"
      duration_seconds: 60
      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: /run/postgresql
            required: true
            observed: "postgres did not become ready in 60s"
  capabilities:
    cap_drop: [ALL]
    cap_add: [CHOWN, DAC_OVERRIDE, SETGID, SETUID]
    derivation:
      tool: container-sec-derive
      tool_version: "0.6.0"
      sidecar_schema_version: "1.5"
      observer: drop-test
      validated_image: docker.io/library/postgres@sha256:33f923b05f64ca54ac4401c01126a6b92afe839a0aa0a52bc5aeb5cc958e5f20
      validated_date: "2026-07-17"
      duration_seconds: 60
      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: CHOWN, required: true, observed: "workload failed / not ready"}
          - {removed: DAC_OVERRIDE, required: true, observed: "workload failed / not ready"}
          - {removed: FSETID, required: false, observed: "correct: workload passed, server uid=999 (non-root)"}
          - {removed: FOWNER, 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)"}