Access log for protected customer data

Shopify order events carry protected customer data: the buyer label in raw.customer_display (see Shopify connection). The data lives in the TimescaleDB table platform_event_logs, and on its way there briefly in Redis (pub/sub and the dead-letter list). The main PostgreSQL holds the order ids of Shopify customer data requests and redactions (shopify_compliance_requests.order_ids), next to the personal data of Lumio's own users. The extensions PostgreSQL holds the data extensions write. The audit log records changes made through the app. It does not record a person who reads the data directly on the host. This page describes the log that does, how long it is kept and how it is reviewed. It covers Shopify's Level 2 requirement "keep an access log to protected customer data".

The setup files are in dev-stack/staging/access-log/. The host runs its own copies of the compose files (see Data layer), so every step below runs on each host that holds the data. Today that is staging. The production databases do not exist yet; they get the same setup when they are built, before the Shopify app goes live.

What is logged

Source	What it records	Where	Kept
pgaudit on TimescaleDB	Every statement of every session except the app's (reads, writes, DDL, role changes), with role, source and client name	/var/log/lumio-tsdb/postgresql-YYYY-MM-DD.log in the container, TSDB_LOG_DIR on the host	365 days
Statement log on the main and extensions PostgreSQL	The text of every statement of every session except the apps', with role, database, source and client name	/var/log/lumio-pg/postgresql-YYYY-MM-DD.log in each container, PG_LOG_DIR / EXT_PG_LOG_DIR on the host	365 days
PostgreSQL connection log	Every login to all three databases, the apps' included: role, database, source address, client name	the same files	365 days
systemd journal	SSH logins (accepted and failed), sudo and su	journald, persistent	1 year
auditd	Every run of the docker CLI (docker exec, docker cp, docker compose exec) with the login user behind sudo and the full command line	/var/log/audit/audit.log*	365 days
Review record	Who reviewed which month, when, the counts and the finding	/var/log/lumio-access-review.log	never deleted

Which database sessions are audited

A login trigger (ops_audit_login, from tsdb-access-audit.sql) switches on pgaudit.log = 'read, write, ddl, role' for every session except the app login (TSDB_USER, default lumio) over TCP. The app connects over TCP from its containers, and its pooled sessions would make the log too large to review. Every other way in is audited:

• docker exec staging-tsdb psql … uses the Unix socket, so it is audited with any role, the app role included.
• A personal role over an SSH tunnel is audited, because it is not the app login.
• The nightly backup (docker exec … pg_dump) is audited. Its reads show up as tsdb_backup_reads in the review.

The trigger compares the role baked into its function body, not a setting a session could override at login. A non-superuser cannot switch the audit off: SET pgaudit.log needs superuser rights. The trigger exists only in the lumio_tsdb database, which is the only database that holds platform_event_logs.

pgaudit.log_relation = on logs each table a statement touches, so a read of a single hypertable chunk (_timescaledb_internal._hyper_…) is logged under that chunk's name. pgaudit.log_parameter = off and pgaudit.log_catalog = off keep bind values and psql's catalog lookups out of the log. Statements typed by hand are logged as written, so a value in a WHERE clause ends up in the log. That is why the log files are mode 0640 and owned by the database user.

Main and extensions PostgreSQL

Both run on postgres:18-alpine, which ships no pgaudit. Moving them to an image with pgaudit would change the C library (musl to glibc) under an existing en_US.utf8 database, which changes text sort order and needs every text index rebuilt. So the same kind of login trigger (pg-access-audit.sql) sets log_statement = 'all' instead, for every session except the app logins over TCP:

Database	App logins over TCP (not logged)	Trigger created by
main (lumio)	lumio	lumio (bootstrap superuser)
extensions (lumio_extensions)	lumio_ext, lumio_ext_api, lumio_ext_worker	postgres (bootstrap superuser)

The log holds the statement text as typed, not the tables a statement resolves to. That is enough for the review: every direct access to shopify_compliance_requests names the table. A non-superuser cannot switch it off (SET log_statement needs superuser rights), and postgres on the extensions database is logged even over TCP, because no app uses it. Slow app queries keep appearing as duration: … statement: lines; the review counts only statement: lines.

What is not logged, and the limits

• Redis contents. Redis has no read log. Reaching it needs a shell on the host and docker exec staging-redis redis-cli, so the SSH log and the auditd docker record show who did it and when, with the full command line.
• A superuser who switches the log off. A superuser can connect with options='-c event_triggers=off' or change the server configuration. The connection itself is still logged (connection authorized with source and client), and a changed configuration file shows up in auditd. The log is tamper-evident, not tamper-proof. Database superuser access is limited to the founder.
• docker logs of the three databases shows only the start-up lines. Every later server log line, slow queries included, goes to the log files.

Setting it up on a host

Run this on the host as root, once per host. The commands use the container names of the reference compose file (staging-tsdb, staging-postgres, staging-extensions-postgres); the staging host's own copy names them tsdb, postgres and extensions-postgres, so replace the names there. Step 3 restarts each database for a few seconds, so pick a quiet time. Chat messages wait in the Redis chat buffer meanwhile (see Data layer); back up first, as for any TimescaleDB change.

1. Check the image and the paths. Each command prints a number or a path:

   docker exec staging-tsdb sh -c 'ls /usr/lib/postgresql/*/lib/pgaudit.so | wc -l'   # 1
   command -v docker                                                                  # /usr/bin/docker
   docker exec staging-tsdb psql -U lumio -d lumio_tsdb -Atc 'SHOW data_directory'
   cat /etc/os-release | grep -c -E 'ID=(debian|ubuntu)'                              # 1

2. Create the log directories next to the data directories and set them in the project .env. TimescaleDB runs as uid 1000, the alpine images as uid 70. On staging the data lives under /root/data:

   install -d -o 1000 -g 1000 -m 750 /root/data/tsdb-log
   install -d -o 70 -g 70 -m 750 /root/data/postgres-log /root/data/extensions-postgres-log
   printf '%s\n' TSDB_LOG_DIR=/root/data/tsdb-log PG_LOG_DIR=/root/data/postgres-log \
     EXT_PG_LOG_DIR=/root/data/extensions-postgres-log >> .env

3. Update the host copy of infra.docker-compose.yml with the three database services of the repo file: on timescaledb pgaudit in shared_preload_libraries and the pgaudit.* flags; on all three the logging_collector and log_* flags and the log mount. Then recreate the containers:

   docker compose -f infra.docker-compose.yml up -d timescaledb postgres extensions-postgres
   docker exec staging-tsdb psql -U lumio -d lumio_tsdb -Atc 'SHOW shared_preload_libraries'
   # timescaledb,pg_textsearch,pg_stat_statements,pgaudit
   docker exec staging-postgres psql -U lumio -d lumio -Atc 'SHOW logging_collector'                       # on
   docker exec staging-extensions-postgres psql -U postgres -d lumio_extensions -Atc 'SHOW logging_collector'  # on

4. Install the login triggers. The scripts are idempotent and print the trigger with evtenabled = A:

   docker exec -i staging-tsdb psql -v ON_ERROR_STOP=1 -v app_role=lumio \
     -U lumio -d lumio_tsdb < dev-stack/staging/access-log/tsdb-access-audit.sql
   docker exec -i staging-postgres psql -v ON_ERROR_STOP=1 -v app_roles=lumio \
     -U lumio -d lumio < dev-stack/staging/access-log/pg-access-audit.sql
   docker exec -i staging-extensions-postgres psql -v ON_ERROR_STOP=1 \
     -v app_roles=lumio_ext,lumio_ext_api,lumio_ext_worker \
     -U postgres -d lumio_extensions < dev-stack/staging/access-log/pg-access-audit.sql

5. Keep the journal for a year and install auditd with the docker rules:

   install -D -m 644 dev-stack/staging/access-log/journald-retention.conf \
     /etc/systemd/journald.conf.d/lumio-access-log.conf
   systemctl restart systemd-journald
   apt-get install -y auditd
   install -m 640 dev-stack/staging/access-log/lumio-access.rules /etc/audit/rules.d/
   sed -i 's/^max_log_file_action.*/max_log_file_action = keep_logs/' /etc/audit/auditd.conf
   augenrules --load
   auditctl -l | grep -c lumio-docker                                                 # 1

   keep_logs rotates audit.log without deleting old files; the prune timer deletes them after RETENTION_DAYS.

6. Install the review script and the daily prune timer:

   install -D -m 755 dev-stack/staging/access-log/access-log-review.sh /opt/lumio-access-log/access-log-review.sh
   install -m 600 dev-stack/staging/access-log/lumio-access-log.env.example /etc/default/lumio-access-log
   # edit the *_LOG_DIR paths and the app roles
   install -m 644 dev-stack/staging/access-log/lumio-access-log-prune.{service,timer} /etc/systemd/system/
   systemctl daemon-reload && systemctl enable --now lumio-access-log-prune.timer

7. Check that it works. Read one row as a person, then look at the report:

   docker exec staging-tsdb psql -U lumio -d lumio_tsdb -Atc 'SELECT count(*) FROM platform_event_logs'
   docker exec staging-postgres psql -U lumio -d lumio -Atc "SELECT to_regclass('public.shopify_compliance_requests')"
   docker exec staging-extensions-postgres psql -U postgres -d lumio_extensions -Atc 'SELECT 1'
   /opt/lumio-access-log/access-log-review.sh report "$(date +%Y-%m)" \
     | grep -E 'tsdb_protected_reads|pg_protected_statements|ext_person_statements|docker_exec_cp'

   All four are at least 1. The main-database check uses to_regclass, so it also works before the Shopify migration has run; the statement names the table either way. Post the date and these numbers on the setup ticket.

Monthly review

On the first working day of each month, the reviewer runs the report for the previous month and records it:

/opt/lumio-access-log/access-log-review.sh record 2026-10 "Raphael" "none"

The report prints the counts (person sessions, protected reads, backup reads, SSH logins and failures, sudo/su, docker runs) and lists every person's protected read and every person's database session. For each line the reviewer answers one question: was this access needed for a known task (an incident, a support case, a data request)? An access without a reason is a finding. The third argument records it ("none" when there is none), and a finding is handled under the incident response policy.

tsdb_backup_reads, pg_backup_statements and ext_backup_statements are expected: about one block per night from the backup job. A month with none means the backup did not run.

record appends one line per review to REVIEW_LOG. That line is the record of who reviewed which month and when.

Rollback

docker exec staging-tsdb psql -U lumio -d lumio_tsdb \
  -c 'DROP EVENT TRIGGER IF EXISTS ops_audit_login' -c 'DROP SCHEMA IF EXISTS ops_audit CASCADE'
docker exec staging-postgres psql -U lumio -d lumio \
  -c 'DROP EVENT TRIGGER IF EXISTS ops_audit_login' -c 'DROP SCHEMA IF EXISTS ops_audit CASCADE'
docker exec staging-extensions-postgres psql -U postgres -d lumio_extensions \
  -c 'DROP EVENT TRIGGER IF EXISTS ops_audit_login' -c 'DROP SCHEMA IF EXISTS ops_audit CASCADE'

Then restore the previous host copy of infra.docker-compose.yml and run docker compose -f infra.docker-compose.yml up -d timescaledb postgres extensions-postgres. The log files stay on the host until the retention deletes them.

If a broken login trigger blocks every login, a superuser still gets in with the trigger switched off for that one session (same for the other two databases):

docker exec -it staging-tsdb psql "dbname=lumio_tsdb user=lumio options='-c event_triggers=off'" \
  -c 'DROP EVENT TRIGGER ops_audit_login'

TimescaleDB extension updates

ALTER EXTENSION timescaledb UPDATE must be the first statement of its session. The login trigger runs before it and loads the old extension version, so the update fails with cannot be updated after the old version has already been loaded, even with psql -X. Run it with the trigger switched off for that one session, in every database that has the extension:

docker exec -e PGOPTIONS='-c event_triggers=off' staging-tsdb \
  psql -X -U lumio -d lumio_tsdb -c 'ALTER EXTENSION timescaledb UPDATE'

pgaudit does not log that session. Its connection is still in the connection log (see What is not logged, and the limits). The trigger stays enabled for every other session.

Log volume

The audited sessions are rare, so the volume comes from the connection log. The compose healthcheck (pg_isready every 10 s) logs two lines per check: about 3 MB a day per database, or about 3.3 GB a year for the three databases on one host. The nightly pg_dump adds one line per table and chunk on TimescaleDB and one per statement on the other two.