Security model

These limits are part of the design. Read them before you rely on locksql.

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".

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.

Transport security

The profile’s tls setting picks the mode, with libpq’s sslmode semantics:

The default is verify-full, except prefer when host is a Unix socket path or a loopback host (localhost or a loopback IP literal). production = true refuses disable and prefer unless the host is loopback or a socket path. tls_ca is a PEM bundle that replaces the system roots; it is refused with disable and prefer, and with require it verifies the chain, as libpq does. tls_server_name replaces host as the name verify-full checks. tls_cert and tls_key present a client certificate (the key must be mode 0600, in the console account’s files). A weaker tls or any change of tls_ca, tls_cert, tls_key or tls_server_name loosens the approved policy and needs the human’s confirmation. locksql doctor checks these files and their expiry, and, for a profile without ssh, fetches the server’s certificate with a TLS handshake that sends no secret, and reports a chain or a name that would not verify and a certificate close to its end.

Below verify-ca an active attacker on the path can intercept or downgrade the connection, read and change the traffic, and relay a login, but is not handed the password. On MariaDB/MySQL, a connection whose server is not authenticated (plain TCP, and TLS to a remote host under prefer or require without tls_ca) answers no request for the password in clear: an auth switch to mysql_clear_password or sha256_password, or a caching_sha2_password full authentication (the MySQL 8 default), is refused before the driver can answer. A MySQL 8 account therefore logs in over such a connection only while the server’s authentication cache holds it, and the refusal names the remedy (tls = "verify-full", with tls_ca for a private CA, or an account with mysql_native_password or ed25519). Under verify-ca and verify-full a full authentication sends the password inside the verified TLS connection, as the MySQL clients do. On PostgreSQL, a connection to a remote host under disable, prefer or require without tls_ca answers no clear-text or MD5 password request (libpq’s require_auth=!password,!md5), and the console names the remedy (tls = "verify-full", or a SCRAM-SHA-256 password for the account). A loopback host or a socket path is not guarded. verify-ca and verify-full prevent the impersonation. A plain or unverified connection is reported on the console and in the audit log.

A profile with an ssh table reaches its database through a bastion. The console opens the SSH connection in its own process: 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 bastion resolves the database’s host name. The bastion’s host key is checked against the console account’s ~/.ssh/known_hosts: 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. 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. Adding, removing or changing the ssh table loosens the approved policy. Out of scope: ProxyJump chains, ~/.ssh/config (never read), certificate host keys and forwarding to a Unix socket on the bastion.

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 returned column is resolved to its base columns; a target column returned by an INSERT or an UPDATE carries both its own provenance and that of the values stored in it. The rows are masked by that provenance, whatever label the engine reports, and carry no cell reference. Those parts follow the read rules; anything the parser does not read (MERGE, multi-table MySQL UPDATE/DELETE, PostgreSQL 18 RETURNING old/new, ...) is refused, and so is a PII filter, grouping or aggregate that needs a k-anonymity count, since the count would run the data-modifying CTEs a second time. The PII filter rules do not apply to the write’s own clauses (its WHERE, FROM/USING items, conflict target): 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 copies and planted values).

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; 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.

Masking is best effort

Column rules depend on the rules being right, and value detectors catch common formats only. Use a database account that cannot read what the agent must never see.

k-anonymity is a decision aid, not a guarantee

A query whose PII filter, group or aggregate covers fewer than k rows warns you on the approval screen; it is not refused, and in auto mode it runs with the warning on the console and in the audit log only. An approved warned query can single out one person. The counts remain a query-set-size control. 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. 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 one 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 check.

Equality still leaks equality

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

Placeholders leave room for a patient agent

Equality probing through cell references only raises a warning: an agent that spreads its probes under limits.reference_probe, across results or sessions, is not stopped. The unmasked columns of a targeted row still identify it, and free text without a detector is not masked.

Clear results stay on the console’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, 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.

Views and inherited tables

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. 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 reference. For the writes analysed like a read, these checks also follow provenance: a value stored from a query 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. 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

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

One console, no sharing

One console per profile and project, one request at a time. No remote or shared consoles, no data export.

Out of scope and limitations (full text) · Limitations in the README