lock family docs GitHub

Security · locksql

locksql security model

locksql lets an AI agent query a database while a human stays in control. This document states what it protects, against whom, and how. Report weaknesses as described in SECURITY.md.

Assets and actors #

  • Assets: database credentials; the data (in particular personal data); the availability of the database server; the integrity of the data and of the policy that governs access.
  • The human starts locksql console with lox run, answers it in a terminal (lox attach) and is trusted.
  • The agent (and anything that can steer it, such as text stored in the database or in files it reads) is not trusted. It runs the client commands and the MCP server.
  • Other local users are not trusted.

locksql runs in Linux containers, the documented setup being Docker (docker.md); the binary refuses to run on native Windows. Where the agent runs depends on the mode:

Mode Console runs as Agent runs as Boundary
Separated (the Docker image, /etc/locksql/system.toml) the locksql account (uid 10001), in its own container agent (uid 10002), member of locksql-clients, in another container sharing only the socket volume the kernel: separate uids, peer credentials on the socket, a root-owned binary and system file

Separation is mandatory. Without system.toml the console refuses to start and names locksql doctor; doctor reports same-user mode as a failure. Same-user threats (an agent reading the console's terminal, typing into it through TIOCSTI, attaching with ptrace or reading the keychain session) are therefore excluded, not mitigated by a warning.

The agent's account is assumed to have no password-less sudo (locksql doctor fails otherwise) and no other way to become locksql or root. Access to the Docker daemon is such a way: an agent that can run docker commands can attach to the console's container (What Docker does and does not separate).

Principles #

  1. Two processes, one key holder. The credentials and the only database connection live in the console process. The clients have neither.
  2. The console is the enforcement point. Skills, rules and tool descriptions are guidance. Every check that matters runs in the console.
  3. The AI tightens, the human loosens. Agents may add mask rules or request changes; every loosening needs the human's confirmation in the console.
  4. Fail closed. What cannot be classified with certainty is refused.
  5. Defence in depth. Server-side read-only and timeouts, a client-side classifier, a full parse and column analysis of reads, a privilege audit, an EXPLAIN gate, result caps, then human approval, informed by k-anonymity counts.
  6. Results are data, not instructions.

Threats and mitigations #

Threat Mitigation
The agent runs a query the human did not approve The console is the only connection holder. query.run needs a one-shot plan id (valid 10 minutes), then answers pending with a ticket: the statement runs only once lockhub's signed decision approves that ticket, or, for a read, once such a decision approved a grant that covers it. There is no approval without a signed human decision, and no socket method that approves: a grant is one decision covering a bounded scope.
A grant is used to read more than the human approved A grant's scope (database, tables, duration, budget) is the plan its ticket binds: the plan hash is the hash of the scope's canonical JSON, the ticket is risk grant and never_auto, and the console verifies the hub's signed decision before activating it. It covers only masked reads of the agent that asked for it, whose every table (CTEs and subqueries included) is in the scope, while its time lasts. Each run costs a statement before it runs and is capped at the rows and bytes left, which are charged after it: usage never passes the budget, so an export rebuilt piece by piece is bounded by it, and splitting it over several grants takes several human decisions. Writes, DDL, unmask and any read that filters, groups or aggregates on a PII column always get a ticket; the latter whatever its k-anonymity counts and budget say, so that "ran at once" versus "ticket" tells the agent nothing of a count. :revoke, the end of the session, any change of the approved policy (a tightening too, but for added mask rules: a covered run is always planned under the rules in force), or the end of its time end a grant; grants do not survive a restart. Every activation, covered run and end is audited with the grant id and the hub's signature of its decision.
The agent loosens its own policy The socket protocol has no method that loosens policy. Config and PII file edits are detected by a policy fingerprint diff; a loosening is held until the human confirms it. The approved policy is stored outside the repository.
Credential leak Secrets exist only in console memory or the OS keychain (the console account's, in separated mode). They never go through argv, environment, files, logs, the socket or error messages (errors are sanitised). Config keys named like secrets and DSNs with passwords are refused. Core dumps are disabled (RLIMIT_CORE=0, and PR_SET_DUMPABLE=0 on Linux); when that fails, the console warns, and refuses to open a production profile. credentials_ttl bounds how long one secret stays in use; limits.idle_timeout closes an idle connection, its tunnel and its secret (the next request asks a connect ticket, the secret typed in lox run and sealed to a one-use key of the console), and limits.max_session ends a long session (raising either is a loosening). doctor flags a secret left in the agent's keychain. With credentials = "vault" the login is read from Vault by the console account at every connection and kept in memory only; the Vault token is asked of the human at start, kept in console memory for the session and handed to the vault CLI in its environment only (never argv or disk), and doctor fails when the agent's account holds one.
Statement smuggling The classifier refuses several statements, comments, variables, bind parameters and unbalanced quotes, and classifies by keywords outside literals per dialect. Reads are limited to SELECT, WITH ... SELECT and EXPLAIN SELECT and parsed in full by a fail-closed parser: unknown syntax is refused. Multi-statements are off in every driver; PostgreSQL uses the extended protocol only. Tier read is also enforced by a read-only session and transaction on the server.
Dangerous functions and statements Reads may call only allowlisted functions (internal/sqlast/funcs.go); schema-qualified (possibly user-defined) functions and system schemas and relations are refused, and so, on PostgreSQL, is a call to an allowlisted name, or an operator, that a function or operator outside pg_catalog (not owned by an extension) also bears, since the server would pick a user-defined overload with a closer argument type; the lookup runs when the statement is planned and again just before it runs. At every tier, statements and constructs on a denylist are refused: file and OS access, engine escape hatches (ATTACH, load_extension, dblink), SQLite raw storage tables (sqlite_dbpage, dbstat), sleeps, benchmarks, advisory locks, FOR UPDATE, session tampering (SET ROLE, set_config, guarded SET targets), statements carrying credentials, server, replication, backup and statistics control functions, and DDL that reaches the server's filesystem, another server or native code (DATA DIRECTORY, remote storage engines, foreign servers and tables, tablespaces, LANGUAGE C and untrusted languages, SQLite file modules). Above tier read this denylist is the only function check, and it is best-effort: user-defined functions and extensions can be named anything (see the limitations); the privilege audit and the human approval are the controls there. A profile with write_functions = "allowlist" applies the read allowlist to every function call in INSERT, REPLACE, UPDATE and DELETE and refuses CALL, MERGE and the writes the parser does not read; turning it off is a policy loosening. On PostgreSQL a read's transaction is rolled back, so a SET smuggled through a function it calls does not outlive the statement.
Server overload READ statements need LIMIT n <= max_rows; EXPLAIN estimates the rows examined and refuses heavy plans, and explain_cost_refuse caps the engine's total cost (not on SQLite); k-anonymity counts are weighed too; under a PII filter the verdict is decided at run time: a REFUSE verdict is shown to the human on the console before the agent is told, and the agent only learns that the weight check refused; a server-side timeout plus a client-side cancel (on MySQL the server-side max_execution_time covers SELECT only: a write or DDL is ended by the client-side KILL QUERY at the deadline); one request at a time. A recursive CTE gets a WARN on PostgreSQL and SQLite, whose planners mark it and can only guess how often it iterates. The parser bounds the bytes a statement can build from literals and size functions (65 536 per value, at most 64 column-width inputs, through every CTE and derived-table layer); string_agg-like aggregates over large stored columns remain bounded only by statement_timeout.
PII exposure Column rules proposed from the schema at every start; per-rule mask modes (redact by default); quasi-identifiers proposed apart and masked only on acceptance; value detectors with checksums; unmasking is off unless the human allows it in the console (--allow-unmask at start, :unmask while it runs), then per query, shown in red and decided in lockhub. A profile that names a database serves that database only, the one its scan covers, so another database the account can see is never read without rules.
Alias or expression bypass Every output column of a read is resolved to its base source columns through aliases, functions, subqueries, CTEs (recursive ones by fixpoint), set operations, joins and *, and masked on that source, not on its label. Expressions and functions over PII columns are refused; aggregates other than COUNT are redacted (MIN and MAX included: which cell they return depends on how the values order). The result's column count and labels must match the analysis or the result is dropped; the engine's origin metadata is a second check.
Predicate oracles A PII column may only be compared for equality with a literal (=, IN (literals), IS NULL) in WHERE/HAVING, or joined with = (also IN (subquery), USING, NATURAL) to another PII column: a join with an unmasked column would copy its values where no mask applies. These atoms must be positive and reached through AND only: a PII atom under NOT, OR or XOR, and <>, !=, NOT IN, IS NOT NULL, IS DISTINCT FROM on a PII column, would select the complement of what the k-anonymity check counts and are refused. A scalar subquery returning a PII value cannot be a filter operand. A UNION, INTERSECT or EXCEPT between a PII column and a non-PII arm (a literal, an expression, an unmasked column) is refused, because INTERSECT, EXCEPT and the duplicate removal of UNION are equality tests no k-anonymity check counts; UNION ALL compares nothing and stays allowed, but DISTINCT, an aggregate's DISTINCT or GROUP BY over a column it mixed is refused for the same reason. LIKE, ranges, functions over it, ORDER BY, window PARTITION BY/ORDER BY, FILTER, DISTINCT ON and constant comparisons in JOIN ... ON are refused, so a query cannot compare it character by character with generated values.
Inference through aggregates and filters A filter, grouping, duplicate removal (DISTINCT) or aggregate on a PII column runs console-built COUNT queries (from the statement's own FROM, WHERE, GROUP BY and HAVING, shown on the approval screen) before the ticket goes to lockhub. Fewer than k_anonymity rows (default 5, production 10) puts a warning on the ticket: the statement could single out one person or reveal a masked value by cross-checking. Since v0.5.2 it no longer refuses: the human decides in lockhub, and the warning goes to the ticket and to the audit records' warnings. This is a decision aid, not a guarantee (see the limitations). The agent learns nothing of the counts: no refusal code and the same answer, whether a count is under k or not. Row estimates are hidden from the agent, a filter on a PII column has its weight verdict decided at run time on the console (query.plan answers OK; a REFUSE verdict is shown to the human on the console before the agent is told, without its reasons), and EXPLAIN is refused for such statements, since the planner's figures would answer the same question.
Inference by equality probing Placeholders ('${name}') and cell references ('${rN.R.C}') are not supported: a statement holding one is refused at plan time (E_PLACEHOLDER), before it is analysed, since requests are decided in lockhub, where no value is typed. Masked cells come back as plain <redacted>, and the console keeps no value of a result. The console warns (approval screen and audit warnings, never a refusal) when the agent wrote a clear value in its statement.
Timing and error channels Clients get a generic failure message, never the server's text: it is shown on the console and kept in the audit log, with the values it quotes redacted when the session masks (a mask rule or a detector); without masking the console and the audit log hold it as the server wrote it, secrets aside. A statement timeout answers <operation> timed out (limits.statement_timeout). No timings are returned, and query.run answers on a 250 ms quantum, success or failure, once the statement is approved; refusals before approval are not quantized, except a weight refusal under a PII filter, which waits for the human's decision in lockhub. A refusal's structured form (code, retry rule, hint, span) is written by locksql: its span points into the agent's own statement and names a keyword, an operator or a name, never a literal, the database's text or a substituted value; a weight refusal under a PII filter carries its code and the code's hint only.
Statement text Some server views hold the text of past statements, literals included: pg_stat_statements and pg_stat_activity on PostgreSQL; information_schema.PROCESSLIST, every performance_schema and sys relation (threads, events_statements_*, session, statement_analysis, ...), mysql.general_log and mysql.slow_log on MySQL and MariaDB. The read path refuses every system schema, these included; the write path (INSERT ... SELECT, ...) refuses these names while mask rules exist.
Prompt injection through data Rows are framed as untrusted data in every output; the generated agent instructions say so; control and bidirectional characters are escaped in client output and on the console screen, so data cannot spoof the approval prompt.
Type-ahead approval Pending input is flushed before each prompt (tcflush).
The agent mimics the human In separated mode the console refuses to start as any account but service_user, when that account may also connect as a client, when started through sudo/su from the login session (loginuid) of an account that may reach the console, or when its terminal belongs to such an account (an unknown uid counts as one); a machinectl shell session is refused unless every running machinectl shell process belongs to an account outside the client group, and an SSH login from this machine is refused, since both relay the caller's terminal while naming the console account; the session or terminal of an account outside the client group, which the agent's account cannot read, is accepted, so that an administrator can hand the console off. locksql console run from the agent's account does not hand off: it prints the command for the console account's own session. An X11 session, where any client can read and inject input, is refused on production profiles and with x11 = "refuse", and warned about otherwise. The binary and system.toml are root-owned; doctor fails on password-less sudo for the agent's account.
Hub mode: a ticket run by another agent, or on other rows A plan and its ticket are bound to the client's uid, the hash of the requesting agent's lea token, the policy fingerprint and the plan hash; another token is refused (CodeWrongRequester). Residual: agents of one account can read each other's token, so this catches mistakes, not a hostile agent of the same workspace. A write's run counts the rows it changed in its own transaction and rolls back past the bound shown on the ticket (stale, new ticket), and checks the plan hash before and after the statement in that transaction; residual: a CREATE OR REPLACE FUNCTION between the check after the statement and the COMMIT goes unseen (a very short window).
Approval spoofing through the socket A request is approved only by lockhub's signed decision, and the console's own prompts (:review, :unmask) are answered only at its terminal; no socket method approves, and no socket method loosens policy. The approval screen names the requesting uid and pid as the kernel reports them, and the process by its executable path when the kernel lets the console read it (a process of its own account), else by its self-reported name, labelled as such.
A client stuck or killed during an approval The approval is abandoned or times out after 5 minutes; the socket keeps serving.
A fake console In separated mode clients check through peer credentials that the socket is served by service_user, and the socket directory is owned by it.
Another local user The socket directory is service_user:client_group mode 2710 on Linux (0710 on macOS), the socket 0660, and each peer is checked with SO_PEERCRED (Linux) or LOCAL_PEERCRED (macOS): the console's own uid, a member of client_group or an allowed_uids entry.

Approval #

  • The approval screen shows the profile, host, database, user, tier, the requesting uid, pid and process, the exact SQL with the PII columns it touches highlighted, its class, the EXPLAIN summary and verdict, the relations read, the PII columns touched and in which clause, the masked outputs and their modes, the k-anonymity counts, the row cap and the PII state; then, just above the prompt, a warning for each k-anonymity count under k.
  • A request is approved only by lockhub's signed decision; the console's terminal answers only its own prompts (:review, :unmask).
  • locksql never rewrites SQL: what the human approves is what runs.
  • Production profiles need the profile name typed, not y. At console start it is typed once per approved policy: on the first approval and after a loosening, not on a restart of an unchanged policy.
  • REFUSE verdicts cannot be overridden from the console; the human has to raise the limits, which is itself a confirmed loosening.

Unmasked output #

Unmasked output is off unless the human allows it in the console: --allow-unmask at start, or :unmask while it runs. Both are console-only: no client or config file can turn it on, so an agent cannot ask for raw PII values by itself. While it is off, an unmask request is refused before parsing and audited; while it is on, each unmasked query is still decided by the human in lockhub, and its clear result is printed in the console, never in the audit log.

Console switches: clear results, unmasked output #

Two behaviours of the console switch while it runs, from its own keyboard only: Ctrl-O (or :results) prints the clear result of each masked query in the console, and :unmask allows or stops unmasked output. --show-results and --allow-unmask only set the starting state. There is no approval without a signed human decision: every request is decided in lockhub, and a grant is one such decision covering a bounded read scope. No client, config file or environment variable can set any of them: the keys and commands are read from the console's terminal, which the separated setup keeps away from the agent, and no socket method or config field reaches them (locksql status and locksql_status only report them). Each switch is audited (results and unmask records with the new state), announced on the console and shown on the status row fixed at the bottom of the terminal, so the state is visible at all times, not only at start.

The transitions:

Switch Change How Confirmation Audit record Takes effect
Clear results hidden → shown Ctrl-O, :results none (red warning on production); refused at levels 1 and 2 (nothing audited) results / shown the next run
Clear results shown → hidden Ctrl-O, :results none results / hidden the next run
Unmasked output off → allowed :unmask only, never a single key y to a prompt (anything else, or no answer within 5 minutes, leaves it off, unaudited) unmask / allowed the next query.plan
Unmasked output allowed → off :unmask none unmask / off at once: an open unmask plan is refused at query.run

Tightening transitions (results hidden, unmask off) need no confirmation. Allowing unmasked output is the one that widens what a client may ask for, so it is a typed command confirmed with y at the console's terminal (the console's own decision, not a request lockhub decides), never a key that an accidental press could trigger. Turning on the clear results is a single key because it does not widen what reaches the agent: the clear results are printed only on the console. A key or command that widens, pressed while one of the console's own prompts waits, is refused until it is answered.

Clear results change nothing for the agent, which still gets the masked cells: the human is the trusted party and already sees the SQL and holds the credentials. locksql status only reports it. It is accepted on production profiles with a red warning; every approval screen says the result will be shown in clear. The audit log still holds no row data. They are refused at desktop security levels 1 and 2 (the console runs in a desktop terminal of the console account): that window is on the agent's desktop, so whatever it prints in clear reaches the agent's account, which would hand it the very data masking keeps from it. --show-results is then a configuration error raised before any prompt or file write, and Ctrl-O or :results leave the results masked, with a message and no audit record.

Audit #

The console appends one JSONL record per event (login, policy change, refusal, ticket (pending), approval, denial, timeout, abandoned approval, run, catalog read, mode, results and unmask switch, logout) to <user state dir>/locksql/audit.log, mode 0600. Records contain the SQL and metadata, never secrets and never row data. The approval of a statement is written before anything runs, and a run record follows with the outcome (decision ok or failed, rows, affected, duration, error): a console stopped mid-run still leaves the approved statement on record. The records of a client request (refusal, decision, catalog read) name the requester from the kernel's peer credentials: peer_uid, peer_pid and peer_user (the account name, when it resolves). The process name the approval screen shows is the agent's choice (labelled self-reported name when the executable path cannot be read) and is not recorded. intent is the agent's one-line reason for a plan: untrusted agent text, stored raw like the SQL. reason is the reason the human typed with a denial. A refusal record holds its code and, when known, its span (line, column and token of the agent's statement).

The log is protected from the agent by the uid separation (the console account's state directory is mode 0700), not from the console account or root: the file is opened O_APPEND with no hash chain, so it is append-only by convention, not tamper-evident. On Linux, chattr +a makes it append-only for the console account too.

Out of scope and limitations #

  • The denylist above tier read is best-effort. Reads may call allowlisted functions only. Writes, DDL and admin statements may call any function not on the denylist (internal/sqlclass/classify.go), and a denylist cannot be complete: a user-defined function, an extension or a later server version can bring a name it does not hold. It catches the well-known escape hatches; what it misses is left to the account's privileges, which the console audits when it connects, and to the human, who approves every statement above tier read as written. write_functions = "allowlist" closes the gap for writes, not for DDL: a DDL tier can create functions, triggers and column defaults that later writes run.

  • Same-user mode is excluded. The console refuses to start without a separated setup, so an agent running as the console's account (reading its keychain session, attaching with ptrace, typing into its terminal) is not a supported configuration.

  • The console account. Malware running as locksql or as root, and an agent account that can become either, are out of scope. So is an X11 session on a non-production profile with x11 = "warn".

  • Transport security. Securing the connection to the database is the job of the driver and of the infrastructure (network, SSH tunnel, sidecar, mesh), not of the console. The profile's tls setting is passed to the driver, with libpq's sslmode semantics:

    Mode Encrypts Verifies chain Verifies host name
    disable no no no
    prefer if offered no no
    require yes, else the driver refuses only with tls_ca no
    verify-ca yes, else the driver refuses yes no
    verify-full yes, else the driver refuses yes yes (host, or tls_server_name)

    The default is prefer, whatever the host. tls_ca is a PEM bundle that replaces the system roots (with require it verifies the chain, as libpq does), tls_server_name replaces host as the name verify-full checks, and tls_cert and tls_key present a client certificate (the key must be mode 0600, in the console account's files). locksql checks only that the values are well formed: it refuses no profile because of its tls (production = true included), refuses no authentication method the driver and the server agree on, and a change of a tls* setting does not loosen the approved policy. locksql doctor reports the mode as information and never fails on it.

    locksql's one TLS behaviour is a warning: when the established TCP connection is plaintext (disable, or prefer against a server that offers no TLS), the console shows warning: the connection to the database is NOT encrypted (tls = ...) at session start and records it in the audit log ("decision":"notice"); the session proceeds. A TLS connection gets no warning, whatever its verification mode, and neither does a Unix socket. Below verify-ca an active attacker on the path can intercept or downgrade the connection, read and change the traffic, relay a login, and obtain the password when the server's authentication method sends it (MySQL mysql_clear_password or a caching_sha2_password full authentication, PostgreSQL password or md5); verify-full (with tls_ca for a private CA), or a tunnel to the database's own host, prevents it.

    A profile with an ssh table reaches its database through a bastion. The console opens the SSH connection in its own process (golang.org/x/crypto/ssh): no ssh binary, no secret in argv, and no local listening port, so another local account (the agent's included) cannot use the tunnel; the driver dials the database through an SSH channel and the bastion resolves the database's host name. The bastion's host key is checked against the console account's ~/.ssh/known_hosts, negotiating only the key types recorded there (Ed25519 first when none is): an unknown key is shown with its SHA256 fingerprint and trusted only when the human types yes (on a production profile, the fingerprint's last 8 characters), and a changed key is refused with no override. The key passphrase or SSH password follows the same rules as the database password (ask or the keychain item ssh:<host>:<port>, never in argv, environment, files or logs). The SSH leg is encrypted and the bastion authenticated; the leg from the bastion to the database is protected only by tls, unless the database runs on the bastion (host loopback). When that leg is plaintext the warning reads encrypted by SSH to <bastion>; NOT encrypted from the bastion to <db host>: .... Each tunnel is audited ("decision":"tunnel" with ssh_host and ssh_host_key), as is a key trusted on first use ("decision":"hostkey-added"). Adding, removing or changing the ssh table loosens the approved policy (except ssh.credentials set to ask). Out of scope: ProxyJump chains, ~/.ssh/config (never read), certificate host keys (@cert-authority) and forwarding to a Unix socket on the bastion.

    A profile with proxy_command reaches its database through a command whose stdin and stdout carry each connection (docker exec, kubectl exec, ssh ... socat). The argv is in the approved policy: the agent, which can write the project config, cannot add or change it without the human approving a loosening that shows it quoted, and an approved change ends the running session. The command runs without a shell, as the console account, in a new session with no controlling terminal (it cannot read the console's keystrokes or prompt there), with an environment reduced to an allowlist that keeps no secret variable; the database password goes over the connection, never to the command. A relative program path is refused, as is any argument naming a path inside the project (which the agent could rewrite after the approval), wherever it appears in the argument, through a symbolic link or ~, and the command runs in an empty private directory of its own rather than the project or the home that may hold it; .., ~user, ssh %-tokens, $VAR and command substitutions are refused. A shell script inside the argv (sh -c, the remote command of ssh) is not interpreted: the human reads it at approval. The command's stderr is shown on a failed connection as one sanitized line of at most 512 bytes. tls applies beyond the command's end as for a direct connection. Out of scope: what the command itself trusts (its own configuration, ~/.ssh/config, the Docker or Kubernetes credentials of the console account), which the human approves with the argv.

  • Write tiers. Above tier read, the human's approval is the gate. The classifier assigns the class shown on the screen, but the server does not restrict what an approved write statement does within the account's privileges. While mask rules exist, a write whose rows come back (a RETURNING list on PostgreSQL, MariaDB or SQLite, or a PostgreSQL WITH whose data-modifying CTEs feed an outer SELECT) or that stores a query's rows (INSERT/REPLACE ... SELECT, a query in a CTE or in VALUES) is parsed and analysed like a read before it runs. Every RETURNING item and every output of the outer SELECT is resolved to its base columns, through aliases, stars, FROM/USING items, subqueries and the RETURNING list of a CTE; a target column returned by an INSERT or an UPDATE carries both its own provenance and that of the values the statement stores in it. The rows are masked by that provenance, whatever label or origin the engine reports. Those parts follow the read rules: an expression over a masked column, an ORDER BY of one, a set operation that compares one, a function outside the allowlist, a whole-row reference, an aggregate in RETURNING and anything the parser does not read (MERGE, multi-table MySQL UPDATE/DELETE, ONLY, WHERE CURRENT OF, PostgreSQL 18 RETURNING old/new or RETURNING WITH, a subquery assigned to a column list, ...) are refused. A PII filter, grouping, DISTINCT or aggregate that needs a k-anonymity count is refused too: the count would run the data-modifying CTEs a second time. The write's own clauses (its WHERE, FROM/USING items, ORDER BY, conflict target and the query an INSERT reads) are resolved and checked against the function allowlist, but the PII filter rules do not apply there: they decide which rows change, which the affected-row count reveals anyway, and the human approves them. A write that returns no rows and stores no query's rows is checked by column name only (see the copies and planted values below).

  • Size bound. The statement's size bound counts bytes built from literals and string functions; numeric arithmetic (1e131071, ^) and formatting of stored data (jsonb_pretty) can still make the server compute and send a value of hundreds of megabytes, bounded by statement_timeout. The console keeps at most 64 KiB of each cell (cut_cells tells the agent which were cut): PostgreSQL columns are cut while they are read off the wire and the rest is dropped unread into memory; MariaDB and MySQL rows are read whole by the driver, up to the server's max_allowed_packet, and cut before they are copied; SQLite runs in the console's process, so a value is cut after the driver has copied it out of the database file.

  • Clear results on the human's screen. With the clear results on (Ctrl-O, --show-results) and for an unmasked run, the clear rows stay in the scrollback of the console's terminal, and are visible to anyone who sees that screen (screen sharing, recording, a terminal that logs its output). That terminal is already the trusted party; locksql does not clear it.

  • Masking is best effort. Column rules depend on the rules being right, and value detectors catch common formats only: phone needs +, 00 or a trunk 0 unless phone_loose is enabled, full-width letters and other Unicode compatibility forms are not folded, and a float column is scanned only for integral values. Use a database account that cannot read what the agent must never see.

  • Probing under the threshold. The clear-value warning does not refuse: an agent that writes a value it knows in an equality filter learns whether it matches, under the k-anonymity warning; the human reading the ticket is the control. The unmasked columns of a row the agent targets can identify it, and free text without a detector is not masked.

  • Duplicate removal on chosen rows. DISTINCT or COUNT(DISTINCT ...) over a PII column compares its rows with each other, so on rows the agent picks with a non-PII filter the result size tells whether those subjects share a value. Like GROUP BY, it is counted: fewer than k rows warns. A set of at least k rows passes, and an agent that already knows how the other values of the set compare still learns whether the chosen subjects share theirs; the human reading the filter on the approval screen is the control.

  • k-anonymity is a decision aid, not a guarantee. Since v0.5.2 a count under k refuses nothing: it warns the human on the ticket, who may approve anyway. A human who approves a warned statement lets the agent single out one person: whether an email is present, one customer's total. The structural rules (equality only on PII, no LIKE, range or negation, masking, no placeholders or cell references) are unchanged and still refuse. The counts remain a query-set-size control, and what follows bounds what a warning can tell the human. Each constant PII filter is counted twice: the subjects of the column in its own base table (a join cannot multiply them; a column of a view, which has no base table to count in, cannot be filtered on), and the rows the statement's own FROM/WHERE selects. IN (literals) counts each value apart and takes the smallest count: an IN list is an OR, and one count over the whole list would pass on values the agent knows exist and tell whether one more does. A join still multiplies the second count: WHERE u.id = 3 AND u.salary IN (...) joined with a large table passes when at least k users earn each of those salaries, and tells the agent that user 3 is among them. It does not stop differencing attacks: two approved queries whose sets differ by one row (all customers of a city, then the same minus one email) still reveal that row's other columns. The approval screen shows each PII filter so the human can spot such sequences; --unmask queries skip the counts.

  • Equality still leaks equality. A filter WHERE email = 'x@example.com' tells the agent whether that value exists. Under k rows the human is warned before approving; once approved, the answer is the agent's. That is the purpose of this feature.

  • Joins between PII columns. An equality between two PII columns (ON a.email = b.email, a.email IN (SELECT b.email ...), USING, NATURAL) is allowed: it compares stored values only. But a value the agent knows can be stored in one of them (through a view matched by name, a trigger, data written before the mask rule, another client, or an approved unmasked write), and the join then tells the agent which subjects share it. The analyser cannot see which stored values the agent knows. Such a plan is not counted: a join on an identifier matches one subject per value by design, so a per-value count would fall under k on nearly every legitimate join and teach the human to ignore the warning. Instead the ticket warns joins PII columns: <columns> (reads and writes alike, column names only, never a value), the row estimates and EXPLAIN stay on the console as under a PII filter, and a grant never covers it. The human reading the join on the approval screen is the control. A PII column joined with itself and a partner that may be a literal are still refused.

  • Relations that project another relation. A view, a materialized view or a foreign table is masked by column name only: a view that renames a rule column (firstname AS contact) needs its own rule for the new name. The same holds for a classic-inheritance table (INHERITS) read through a parent or a child the rule does not name. A PostgreSQL partition is resolved to the root of its partition tree, so a rule on the partitioned table covers its partitions.

  • Copies and planted values at higher tiers. A write (INSERT ... SELECT, UPDATE ... SET, MERGE) that copies a rule column into another column is refused, with or without RETURNING, and so is a write that stores into a rule column anything but NULL or DEFAULT: a masked column holds only values the agent never chose, since a planted value would come back masked yet known to the agent, and joining that column with = to another PII column, which no k-anonymity count covers, would tell it whose value matches. Columns match by name, so an updatable view that renames a rule column, a trigger, or a value written before the rule existed (or by another client) can still put a value the agent knows behind a masked cell. For the writes the analysis above reads, these checks also follow provenance: a value stored from a query (INSERT ... SELECT, a subquery in SET or VALUES, an ON CONFLICT update) that derives from a masked column through an alias, a table alias column list, a CTE or a derived table is refused, and a masked target never receives a query's values. A table alias column list that renames columns by position (FROM users u(i, x)) is refused in any write. DDL such as CREATE TABLE ... AS SELECT, CREATE VIEW, a column default or a column rename is not checked that way: at tier ddl and above the human's approval is the gate.

  • Catalog metadata (database, table, column and index names) is returned without approval.

Upstream recommendation #

locksql masks what leaves the database; the database still holds plaintext, and any other client of it (a backup, a replica, a reporting tool) sees it. Where you control the schema, store personal data encrypted at rest with a blind index: the application encrypts the value with a key the database never holds, and stores next to it a keyed HMAC of the normalised value for equality lookups and joins. The database then holds no plaintext to leak. locksql's masks are the downstream half of the same idea: they hide values in query results, whatever the database stores.