SSH and database session recording: capture, retention, and alerts

TL;DR — For privileged access, record enough to answer who connected, to what, when, from where, what commands or queries ran, what changed, and whether the session was approved. The most common mistake is trying to store full keystrokes and full SQL result sets forever; in practice, keep strong session metadata and command/query logs broadly, reserve full TTY/video-style replay for high-risk paths, redact secrets aggressively, and alert on a small set of high-signal events like direct root login, disabled logging, bulk export, and schema/permission changes. Reading time: ~7 min

What it is and where it sits

Session recording for SSH and database access is the audit layer around privileged access: not just "someone authenticated", but the actual session context and activity. In a PAM-oriented architecture, it usually sits between the human operator and the target system as a bastion, proxy, or gateway. Sometimes it is host-local for SSH (for example, shell accounting or auditd on the target host), and sometimes it is database-native (for example, PostgreSQL logging or pgaudit on the server). The useful pattern is usually both: a control point in front, plus logs on the target.

What it replaces: shared jump-box accounts, ad hoc shell history, and incomplete database logs. What talks to it: engineers using ssh or a SQL client, an identity provider for authn/authz, the target Linux host or database, and a log/archive backend for immutable retention.

From a request/data-flow perspective, the recorder is not in your app's normal user request path. It is in the operator path for break-glass access, production debugging, migrations, and manual data repair.

[Engineer] ---- 1. ssh / psql ----> [Access Gateway]
[Engineer] <---- MFA / cert / token ---- [IdP]
[Access Gateway] ---- 2. proxied session ----> [Target Host]
[Access Gateway] ---- 3. proxied SQL ----> [DB Server]
[Access Gateway] ---- 4. metadata/events ----> [Log Store]
[Target Host] ---- 5. audit logs ----> [Log Store]
[DB Server] ---- 6. db audit/logs ----> [Log Store]
[SIEM/Alerts] <---- 7. detections ---- [Log Store]

For SSH, the capture points are usually:

• gateway/bastion session metadata
• TTY stream or command stream
• host-local audit trail such as execve events
• sudo logs for privilege escalation

For databases, the capture points are usually:

• connection metadata
• statement logs or audit extension output
• role/permission/schema change logs
• optional network proxy logs if you route DB access through a proxy

The architecture question is simple: if the gateway is bypassed, do you still have enough evidence on the target? If the target is compromised, do you still have an immutable copy off-host?

How it actually works

Walk one realistic example: an engineer uses SSH to reach a production app host, then runs psql from that host to a production PostgreSQL database to inspect and fix a bad permission.

Step 1: access starts at the control point

The engineer runs:

ssh -J ops-gateway.prod.example.com app-admin@app-01.prod.example.com

At session start, capture these fields immediately, before the shell opens:

• user identity from SSO or SSH certificate principal
• source IP and user agent-ish client details if available
• target hostname and environment
• auth method: MFA, SSH cert, hardware key, break-glass token
• ticket/change reference if your process requires one
• session ID and start timestamp

If your gateway cannot bind the session to a human identity and target, the rest of the recording is much less useful.

Step 2: shell activity is recorded at two levels

At the gateway, record either the full TTY stream or at least the command stream. On the target host, record process execution and privilege escalation.

The engineer runs:

whoami
hostname
sudo -l
sudo -u postgres psql -d appdb

What you want to retain from this phase:

• exact command lines with timestamps and exit codes where available
• whether sudo was used, to which account, and whether it succeeded
• TTY replay only for systems where you truly need forensic playback
• file transfer events: scp, rsync, shell redirection to files, clipboard paste if your gateway exposes it

Host-local evidence matters because shell history is not enough. history can be disabled, edited, or never flushed. auditd/kernel exec logging or equivalent catches execve regardless of shell history.

Step 3: database activity is logged as statements, not result sets

Inside psql, the engineer runs:

BEGIN;
SELECT rolname, rolsuper, rolcreaterole FROM pg_roles WHERE rolname = 'app_user';
ALTER ROLE app_user NOSUPERUSER NOCREATEROLE;
COMMIT;

For DB recording, capture:

• connection metadata: DB user, mapped human identity if proxied, database name, client IP, application name, session start/end
• statements for DDL, DCL, role changes, grants/revokes, schema changes, and administrative functions
• transaction boundaries and duration
• row counts where available
• errors and SQLSTATE codes

Do not broadly log full result sets for routine sessions. That explodes storage and turns your audit system into a second sensitive data store. Statement text plus metadata is usually enough. For especially sensitive tables, you may log access events and row counts, not row contents.

A realistic PostgreSQL log line shape might look like:

2026-10-30 14:22:11 UTC [42177] app_admin@appdb LOG:  statement: ALTER ROLE app_user NOSUPERUSER NOCREATEROLE;
2026-10-30 14:22:11 UTC [42177] app_admin@appdb LOG:  duration: 12.441 ms

If the command fails, keep the error and SQLSTATE:

2026-10-30 14:22:11 UTC [42177] app_admin@appdb ERROR:  permission denied to alter role
2026-10-30 14:22:11 UTC [42177] app_admin@appdb STATEMENT:  ALTER ROLE app_user NOSUPERUSER NOCREATEROLE;

Step 4: the recorder finalizes the session record

At session end, write:

• end timestamp and duration
• disconnect reason: normal close, network drop, idle timeout, forced termination
• summary counters: commands executed, files transferred, bytes uploaded/downloaded, SQL statements by class
• integrity metadata: hash/signature of the session artifact if you archive replay files

This is where alerting also hooks in. If the session touched production, used sudo, altered roles, or exported large data, emit an event immediately rather than waiting for daily review.

When to use it (and when not to)

Use session recording when a human can directly affect production state. Do not default to maximal capture everywhere.

You probably do not need full session replay if all privileged changes happen through reviewed automation and direct shell/DB access is disabled except rare break-glass events. In that model, the important logs are deployment, migration, and IAM audit logs.

Trade-offs

Every benefit has a cost.

• Better forensic evidence after an incident
  • Cost: storage, indexing, and review burden. TTY/video-style replay is expensive and rarely reviewed unless something goes wrong.
• Stronger deterrence for risky manual changes
  • Cost: developer friction and trust concerns. Be explicit that this is for privileged production access, not surveillance of normal work.
• Faster incident reconstruction
  • Cost: secret leakage risk. Commands often contain tokens, connection strings, kubectl manifests, or pasted private keys.
• Centralized access path through a gateway
  • Cost: operational dependency and possible outage domain. If the gateway is down, prod access may be down too.
• Database statement logging
  • Cost: performance overhead and log volume. On busy systems, verbose statement logging can be material.
• Long retention for compliance
  • Cost: your audit archive becomes a high-value target. You need encryption, access controls, immutability, and deletion policy.
• Human identity mapped to DB sessions
  • Cost: more plumbing. Native DB users often do not preserve the original human identity unless you add proxy/session variables or force per-user auth.

A practical compromise in 2026 is: broad metadata retention, selective full replay, and targeted alerts. Record more for production and break-glass, less for lower environments.

In practice

Example 1: Linux host-side command auditing with auditd

⚠️ Bad audit rules can generate a lot of events and fill disks or overwhelm your log pipeline. Test on one host first and watch /var/log/audit/audit.log growth before rolling out fleet-wide.

sudo apt-get update
sudo apt-get install -y auditd audispd-plugins
sudo bash -c 'cat >/etc/audit/rules.d/privileged-access.rules <<"EOF"
-a always,exit -F arch=b64 -S execve -k exec
-a always,exit -F arch=b32 -S execve -k exec
-w /etc/sudoers -p wa -k scope
-w /etc/sudoers.d/ -p wa -k scope
-w /var/log/auth.log -p wa -k authlog
EOF'
sudo augenrules --load
sudo systemctl restart auditd
sudo auditctl -l

This records every executed command (execve) and watches changes to sudo policy. The gotcha: execve on busy hosts is noisy; filter by user ID ranges, groups, or target hosts if you only need privileged admin sessions.

To inspect events for one command:

sudo ausearch -k exec -ts recent | tail -n 20

Typical output shape:

type=EXECVE msg=audit(1793371401.421:8421): argc=3 a0="sudo" a1="-u" a2="postgres"
type=SYSCALL msg=audit(1793371401.421:8421): arch=c000003e syscall=59 success=yes exit=0 auid=1001 uid=0 gid=0 euid=0 tty=pts0 ses=221 comm="sudo" exe="/usr/bin/sudo" key="exec"

Example 2: PostgreSQL logging for admin activity

⚠️ Turning on broad statement logging in production can increase I/O and expose sensitive SQL text. Start with DDL/DCL/admin coverage, not log_statement = 'all' on a busy OLTP database.

# postgresql.conf
logging_collector = on
log_destination = 'stderr'
log_line_prefix = '%m [%p] user=%u,db=%d,app=%a,client=%h '
log_connections = on
log_disconnections = on
log_duration = on
log_min_duration_statement = 500ms
log_statement = 'ddl'

This gives you connection lifecycle, slow statements, durations, and all DDL. The gotcha: ALTER ROLE, GRANT, and some admin actions may warrant more than plain DDL logging; on PostgreSQL, many teams add pgaudit for session/object audit coverage.

After editing, reload safely:

sudo -u postgres psql -c 'SELECT pg_reload_conf();'
sudo -u postgres psql -c "SHOW log_statement; SHOW log_line_prefix;"

Expected output shape:

 pg_reload_conf 
----------------
 t
(1 row)

 log_statement 
---------------
 ddl
(1 row)

If you use pgaudit, the config typically looks like:

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'role,ddl,write'
pgaudit.log_relation = on
pgaudit.log_parameter = off

This focuses on role changes, DDL, and writes, while avoiding parameter logging that can leak secrets. The gotcha: shared_preload_libraries requires a PostgreSQL restart, not just reload.

What to retain and alert on

A sane baseline retention policy:

• Session metadata: 1 year or your compliance minimum
• Command/query audit logs for production: 90-180 days hot, then archive
• Full TTY replay artifacts: 30-90 days for routine prod, longer for break-glass or regulated systems
• Dev/staging: 7-30 days unless policy says otherwise

Alert on these, not everything:

• SSH direct root login or login to disabled/shared accounts
• Session recording disabled, audit agent stopped, log destination changed
• sudo su -, visudo, changes under /etc/sudoers*, SSH daemon config changes
• Database ALTER ROLE, CREATE ROLE, GRANT, REVOKE, CREATE EXTENSION, schema drops, mass deletes/updates
• Bulk export patterns: COPY ... TO, \copy, large SELECT from sensitive tables, unusually high egress bytes
• Access outside approved windows, from new geographies/IPs, or without linked ticket/approval

Keep alerts high-signal. A pager that fires on every SELECT is a pager people mute.

Further reading

• Linux auditd and audit.rules man pages
• PostgreSQL documentation: "Runtime Configuration / Logging" chapter
• pgAudit documentation
• NIST SP 800-53: AU (Audit and Accountability) and AC (Access Control)
• The OpenSSH sshd_config and ssh_config man pages

This article was written by an AI system and published pending human review. Verify anything you intend to act on.