ADR-0033: SQL database access (PostgreSQL, MySQL, Aurora, Redshift)

• Status: Accepted (the owner asked for the implementation on 2026-10-17; built as proposed except where the amendment below says otherwise)
• Date: 2026-10-17

Context

Phase 6 delivers limen db connect: a developer runs psql or mysql against a loopback port and reaches a database they are allowed to use, with no password, every statement audited. ADR-0015 already fixed the credential model (one IAM-authenticated DB role per policy role, bootstrapped by the worker). ADR-0018 fixed Redshift as a PostgreSQL variant. Design §5.2 sketches the data path. What is not decided is how the worker handles the wire protocols, what it enforces, where bootstrap runs, and how we test any of it without an AWS account.

Today the data side exists as stubs only: policy accepts the postgres, mysql, and redshift kinds and the db grant, and limen db connect answers “planned for Phase 6”. No SQL wire code exists.

Decision

1. Slices

Each slice is its own PR and keeps CI green. Nothing later is started early.

Slice	Content	Done when
6a	PostgreSQL: CLI listener, session kind db, worker pgwire handling, db.query audit, password mode against compose Postgres	psql through limen db connect; denied role or database is audited and refused
6b	Bootstrap reconciler for PostgreSQL	Run twice, second run changes nothing; drift is reported
6c	MySQL: codec on mysql_common, audit, bootstrap	The 6a and 6b criteria with mysql
6d	IAM auth tokens (RDS and Aurora, PostgreSQL and MySQL)	Token signing tested against AWS’s published SigV4 vectors. Unverified against real service
6e	Redshift	PostgreSQL conformance tests that Redshift supports pass; difference list documented. Unverified against a real cluster

Aurora needs no code of its own: the writer and reader endpoints are separate resources of kind postgres or mysql (design §5.2). It gets a guide section and an example, nothing more.

2. Data path

• The CLI opens 127.0.0.1:<port> (or a 0600 unix socket) and turns each accepted connection into one Limen session of a new kind db, over the existing proxy mTLS hop. The proxy authorizes (resource, db.roles, db.databases) and routes to the worker exactly as it does for SSH. Nothing in the proxy parses SQL.
• The worker is a message-aware relay, not a full protocol implementation. It speaks the client side of the startup and authentication itself (this is where the credential lives and where the user’s requested database and role are checked), then relays post-authentication traffic frame by frame, parsing only the frames needed for audit and for the guards in section 4. Result sets are streamed through and never inspected beyond the completion frame.
• Toward the client: PostgreSQL SSLRequest is answered N and GSSENCRequest is refused, because the outer hop is already mTLS. Authentication is accepted without a password: the stream is already authenticated and authorized. MySQL gets a minimal server handshake that accepts any client auth response.
• Toward the target: TLS is mandatory with verify-full against a configured CA bundle (the RDS bundle for AWS targets). A resource may name a plain-text target only in the local compose profile, behind an explicit insecure_dev: true, which the control plane refuses outside the dev profile.

3. Credentials to the target

• IAM mode (RDS, Aurora, Redshift): as ADR-0015 and design §5.2. The token is generated by the worker at connect time and never logged.
• Secret mode (any self-hosted or non-AWS database, and the local demo): the worker reads the DB role’s password from a secret reference on the resource, holds it as Secret<String>, and rotates nothing itself. This is new relative to ADR-0015, which only covered IAM; it exists because every other path is untestable locally and because ADR-0018 requires working with self-hosted servers. The user never sees it in either mode.
• Both modes set application_name (PostgreSQL) or the limen_session connection attribute (MySQL) to limen:<session_id>, per ADR-0015.

4. What the worker enforces

Enforcement is by database grants, not by parsing SQL (ADR-0012: sqlparser is for classification and normalization, never for allow or deny). The worker adds only guards that the wire protocol makes cheap and unambiguous:

• The database in the PostgreSQL startup message or MySQL handshake must be in the grant’s databases. PostgreSQL cannot change database in-session. MySQL COM_INIT_DB, USE, and COM_CHANGE_USER are refused unless the target is also granted.
• COM_CHANGE_USER, PostgreSQL replication-mode startup (replication=...), and GSSENCRequest are refused.
• SET ROLE and SET SESSION AUTHORIZATION are not blocked (a client can write them in ways a prefix check misses). The defense is that the limen_* login role has no membership in other roles. Bootstrap enforces that and reports a violation as drift. This is stated in the threat model rather than hidden.
• PostgreSQL CancelRequest is forwarded using the BackendKeyData the worker saw, mapped per session so a client cannot cancel another session.
• Per-resource limits: maximum frame size, idle timeout, and the session max_session_ttl from the role.

5. Audit

• One db.query event per statement, as in design §5.2: user, roles, DB role, database, text, type, start, duration, rows affected, error.
• Fails closed, like ssh.exec: the event is written to the spool before the statement is forwarded, and the statement is refused if the write fails. Duration, rows affected, and error are sent in a second db.query.end event keyed by the first. If the end event cannot be written, the session ends.
• Simple protocol: one event per statement in the message (split by sqlparser when it parses, otherwise the whole message as unknown). Extended protocol: text at Parse, an execution event at Execute with parameters redacted unless audit.log_parameters: true. COPY is logged as written, with byte counts at the end and no row data.
• session.start and session.end carry the recording-less variant of the SSH fields (no recording path). Phase 6 produces no replayable recording; statement text in audit is the record. Result data is never stored.
• The audit schema gains the two event kinds; the console and Grafana “privileged commands” panels learn them. Statement text can contain secrets, so it is subject to the same retention and access rules as other audit and is flagged in the threat model.

6. Bootstrap

• The reconciler is a library module in the worker with two entry points: limen-worker db-bootstrap --once (a one-shot, with its own IAM role and admin secret, suited to a separate task) and an opt-in in-process loop (db.bootstrap.interval, default off). The ADR-0015 default of “every 10 minutes inside the worker” is therefore changed to opt-in, because it keeps the admin secret off the long-running data-path process by default, which the threat model already names as the preferred option. The owner is asked to confirm this below.
• It only creates, alters, or grants for roles with the limen_ prefix, runs in one transaction per role, and diffs before writing so a second run issues no DDL. Revoking a grant that is no longer declared is part of reconcile.
• The admin role is a dedicated limen_admin, never the RDS master user.

7. Redshift

Slice 6e reuses 6a unchanged except for authentication (GetClusterCredentials, GetClusterCredentialsWithIAM, redshift-serverless:GetCredentials, or Secret mode) and a compatibility profile (no assumption of extended-protocol features it lacks, catalog differences, version string). No Redshift emulator exists, so “passes the conformance tests it supports” means: the shared PostgreSQL suite is parameterized by profile, runs against vanilla PostgreSQL, and the difference list is derived from AWS documentation. Everything Redshift-specific is labelled unverified against a real cluster.

8. Test and demo plan

• Compose gains postgres-target and mysql-target (separate from the control plane’s own database) seeded with a small schema and two limen_* roles.
• Unit tests: frame parsing for both protocols; fuzz targets (cargo-fuzz) for the MySQL codec and the PostgreSQL frame splitter, as ADR-0012 already promises for owned codecs.
• Integration tests: allowed and denied role and database; SET ROLE has no effect because of grants; cancel; COPY; multi-statement; prepared statements; audit-write failure refuses the statement; idempotent bootstrap; token signing against vectors.
• demos/demo-phase6.sh: logs in, runs psql and mysql through limen db connect, shows the audited statements, shows a denied database, runs bootstrap twice.
• Docs: docs/guides/databases.md, runbook database-onboarding.md, a threat-model update (new listener, statement text in audit, admin secret, SET ROLE), ADR-0012 rows for new dependencies.

9. Dependencies

Expected: pgwire (already adopted) for frame types, mysql_common and sqlparser (already adopted), a SigV4 signer for tokens (to be chosen after maintenance and advisory review), and cargo-fuzz as a dev tool. Each is added to ADR-0012 in the slice that first uses it. No dependency is added by this PR.

Questions for the owner (answered by the build; revisit in review)

1. Secret mode (section 3): accept it as part of Phase 6? Without it the phase can only be demonstrated by stubbing IAM.
2. Bootstrap default (section 6): opt-in loop plus one-shot, instead of always-on every 10 minutes?
3. Fail-closed audit for every statement (section 5): it adds one spool write before each statement. Acceptable, or batch for read-only statements?
4. Order: PostgreSQL, then MySQL, then IAM, then Redshift as above, or MySQL before IAM?
5. No SQL-level blocking (section 4): is “enforced by DB grants” enough for Phase 6, leaving statement-level policy (for example deny DROP) to a later phase?

Consequences

• + Users keep native tools; one relay design covers PostgreSQL, Redshift, and MySQL.
• + Most of the phase is testable locally; only IAM and Redshift specifics are unverified, and they are labelled.
• − Message-aware relay means we parse enough of two protocols to audit them; the MySQL codec in particular is our own code and a fuzzing target.
• − Without SQL-level policy, anything the DB role may do, the user may do.
• − Statement text in audit can hold sensitive data; normalize_literals mitigates it per resource.
• Supersedes nothing. Amends ADR-0015 on the bootstrap default and on Secret mode if accepted.

Amendment: as built (2026-10-17)

All five slices are implemented. The five questions were answered as proposed: secret mode is in; bootstrap is a one-shot task plus an opt-in loop (off by default); every statement is audited before it is forwarded; the order was PostgreSQL, bootstrap, MySQL, IAM, Redshift; enforcement inside a session is the database’s grants. Where the build differs from the text above:

• Dependencies. No pgwire and no mysql_common: both wire formats are small enough that the frame and packet layers are written by hand (db/pg.rs, db/mysql.rs). sqlparser is used for its tokenizer only (statement splitting, the first keyword, literal normalization). SigV4 is hand-written (db/sigv4.rs) and checked against AWS’s published examples instead of adding a signing crate. New: md-5 (Redshift’s MD5 login) and aws-credential-types (with aws-config, already in the tree) for the default credential chain. ADR-0012 has the rows.
• Audit.
  • db.query.end is written when the target says it is ready for the next statement (PostgreSQL) or when the final packet of the answer is about to be forwarded (MySQL). An error in a batch is attributed to the first statement that has no answer yet.
  • Parameter values are never logged: audit.log_parameters is not built.
  • Commands the worker refuses (MySQL USE, KILL forms, COM_CHANGE_USER, …) are audited as db.query with phase: refused.
  • Bootstrap writes db.bootstrap events (before each change), not part of the proposal.
• Limits. The idle timeout is a fixed hour (not per resource) and frames read in full are capped at 64 MiB. A per-resource limit is not built.
• Bootstrap.
  • The admin credential is a file (admin_password_file). admin_secret_arn (Secrets Manager) is rejected with a clear message; supporting it needs the Secrets Manager client and is deferred.
  • MySQL admins usually can’t read mysql.user, so the grants of a user are read by logging in as that user and running SHOW GRANTS; IAM mode (no password) uses the admin’s SHOW GRANTS FOR instead.
  • MySQL uses a single ensure_user step (create if missing, then align the password to the file) so the audit event precedes the change.
• Session details.
  • A login_user is separate from the policy role: Redshift IAM logs in as IAM:<role>.
  • The MySQL client is shown a Limen connection id, and KILL [QUERY|CONNECTION] <id> is translated for the same user; the PostgreSQL cancel key is likewise Limen’s. This is how Ctrl-C works in mysql and psql.
  • The client’s FOUND_ROWS, IGNORE_SPACE, INTERACTIVE, and MULTI_STATEMENTS flags are passed on to MySQL, so affected-row counts and multi-statement behavior match what the driver asked for.
  • tls_ca_file is required (no system roots), with tls_server_name for certificates that carry another name.
• Not verified. RDS and Aurora IAM tokens and AWSAuthenticationPlugin, the Redshift credential APIs, MD5 against a real Redshift server, and Redshift behavior in general. The PostgreSQL rds_iam role is a stub in tests; the MySQL IAM plugin does not exist in vanilla MySQL and is untested.
• Deferred. Fuzzing the MySQL codec and the PostgreSQL frame splitter (ADR-0012 promises it), cursors, compression, LOCAL INFILE, GSSAPI, and statement-level policy.