Locks · locksql

Configuring locksql

Profiles, PII rule sets, sharing a configuration and the company's managed configuration. Setting a project up: docker.md; what each setting does at run time: usage.md.

Profiles #

Profiles live in .locksql/config.toml at the project root (found by walking up from the current directory) and in <user config dir>/locksql/config.toml. A project profile wins over a user profile of the same name. No file ever holds a secret: keys named like password, passwd, pwd, secret or token, and DSNs with an embedded password, are refused.

Breaking change (v0.15). locksql no longer enforces transport security: the tls* settings are passed to the driver, a profile with no tls setting gets the drivers' default prefer whatever the host (it was verify-full for a remote host since v0.4), production = true no longer requires TLS, and no authentication method is refused because of the TLS mode. A plaintext connection gets one warning at session start. To keep verifying the server certificate, set tls = "verify-full".

[profiles.uat]
engine      = "mariadb"             # mariadb | mysql | postgres | sqlite
host        = "db.uat.example.com"  # or path = "app.db" for sqlite
port        = 3306                  # default 3306, or 5432 for postgres
user        = ""                    # empty: asked at console start
database    = ""                    # empty: chosen per query (--db)
credentials = "ask"                 # ask | keychain | vault
tls         = "verify-full"         # disable | prefer (default) | require | verify-ca | verify-full, as libpq's sslmode
tls_ca      = ""                    # optional PEM bundle replacing the system roots; "~/db-ca.pem" is in the console account's home
credentials_ttl = "20m"             # optional: ask for the secret again
tier        = "read"                # read | write | ddl | admin
production  = false
detectors   = ["email", "phone", "iban", "card", "be_niss"]

[profiles.uat.limits]
statement_timeout   = "30s"
explain_rows_warn   = 100000
explain_rows_refuse = 1000000
max_rows            = 200
max_cell_chars      = 200
max_output_bytes    = 65536
k_anonymity         = 5
explain_cost_refuse = 0             # engine cost units; 0 = off
idle_timeout        = "20m"         # the connection closes after this long idle
max_session         = "4h"          # ... and after this long in all
grant_max_duration  = "1h"          # the longest grant; "0" turns grants off
grant_max_statements = 200          # a grant's budget at most: statements,
grant_max_rows      = 10000         # rows returned
grant_max_bytes     = 1048576       # and bytes returned
tickets_per_window  = 30            # tickets a human decides per window
ticket_window       = "5m"
max_pending         = 30            # tickets pending at once

With credentials = "vault" the console runs vault read -format=json <vault_path> at every connection, as the console account and with its environment (VAULT_ADDR), and connects with the username and password of the secret. The console asks for the Vault token at start and keeps it in memory for the session. Nothing is stored. See Credentials from HashiCorp Vault.

[profiles.reporting]
engine      = "postgres"
host        = "db.internal"
tls         = "verify-full"
credentials = "vault"
vault_path  = "database/creds/reporting-ro"   # or a KV secret: "secret/data/reporting"

A database reachable only from a bastion gets an ssh table: the console opens the SSH connection itself, with no local listening port, and verifies the bastion against the console account's ~/.ssh/known_hosts (asking on first use, refusing a changed key). host is then the database as seen from the bastion; the bastion-to-database leg is protected by tls only, unless the database runs on the bastion itself. See Reaching a remote server through SSH. A database that publishes no port (an internal Docker network, a server bound to its container's loopback) is reached with proxy_command, an argv whose stdin and stdout carry the connection, such as ["docker", "exec", "-i", "shop-db", "socat", "-", "TCP:127.0.0.1:3306"]; see Databases that publish no port.

[profiles.prod]
engine      = "postgres"
host        = "db.internal"         # as seen from the bastion
tls         = "verify-full"
production  = true

[profiles.prod.ssh]
host        = "bastion.example.com"
port        = 22                    # default 22
user        = "deploy"
auth        = "key"                 # key | agent | password
key         = "~/.ssh/id_ed25519"   # auth = "key" only; the console account's home
credentials = "ask"                 # passphrase or SSH password; default: the profile's

PII rules #

PII column rules live in named rule sets, one file per set: pii/<set>.toml next to the config, so .locksql/pii/<set>.toml inside a project and <user config dir>/locksql/pii/<set>.toml (~/.config/locksql/pii/ on Linux) elsewhere. Each profile uses the set named by its pii_rules key, by default its own name, so a profile never sees the rules of an unrelated database. Profiles that point at isomorphic databases share a set on purpose:

[profiles.shop-dev]
pii_rules = "shop"
[profiles.shop-prod]
pii_rules = "shop"        # rules added from either console reach both
production = true
[profiles.billing]        # no pii_rules: the set "billing", its own

locksql pii and :pii act on the set of the profile in use, and the approval screen shows the set next to the profile. The console warns at start when a profile without pii_rules shares its set by accident: with a profile whose name differs only by case, with a profile whose pii_rules names it, or because it is named default. pii_rules is read at start; pointing a profile at another set is a loosening, approved again at the next start.

The single pii.toml of earlier versions becomes the set default at the first start (moved to pii/default.toml, with a one-time notice). The profiles approved before keep using it until they get a pii_rules key; any other profile uses its own set.

A set file:

[[mask]]
column = "app.users.email"            # db.table.column (schema.table.column on PostgreSQL), * per segment
mode   = "email"                      # j***@example.com
[[mask]]
column = "app.users.customer_ref"
mode   = "partial"                    # j***(12)
[[mask]]
column = "*.*.recipient_reference"    # <redacted> (default)
[[allow]]                             # explicit exception: never mask
column = "app.templates.name"
[[mask]]                              # '*' prefix/suffix inside a segment:
column = "*.*.*email*"                # contact_email, email_backup, Email

A column covered by several rules with different modes is redacted. Changing a mode is a loosening unless the new mode is redact.

mode = "hash" is no longer a mode: a rules file that uses it is rejected ([[mask]] entry N: pii rule: unknown mode (want redact, partial or email)).

On PostgreSQL the first segment is the schema (public.users.email).

Share a configuration with your team #

locksql config export writes the profiles of your user config (or of the project with --project) and the PII rule sets they use to one JSON file; locksql config import FILE applies it on another machine:

locksql config export --output team.json                 # all profiles, their PII rule sets
locksql config import team.json --dry-run                # show what would change
locksql config import team.json --local uat.ssh.key=~/.ssh/id_ed25519

• Never exported: secrets (none is in a config file; a value that looks like one fails the export), user (unless --include-user), files of one machine (tls_ca, tls_cert, tls_key, ssh.key: listed in needs_local, given on import with --local NAME.KEY=VALUE), proxy_command (a command line: kept from your own config, never imported), SQLite and Unix-socket profiles, approved policies, the audit log and console flags. --no-pii leaves the rule sets out.
• Import: a strict format (unknown fields, newer formats and invalid values are refused, nothing is written); imported profiles replace those of the same name but keep your user and local files; rule sets are merged (a mode conflict keeps redact); --replace replaces the profiles and the imported sets instead. A backup (*.bak) is kept.
• Loosening: any change that loosens the policy (new profile or host, higher tier, higher limits, a removed mask rule, a new allow rule, another rule set, ...) is refused unless you pass --allow-loosen, and the console still asks you to approve it at its next start.

The file describes your databases: keep it in a private repository.

Managed configuration #

A company can bound every locksql of a machine with one file its admin installs: /etc/lockfamily/managed.toml (Linux) or /Library/Application Support/lockfamily/managed.toml (macOS), owned by root and writable by root only, optionally signed (the family's format, signing and deployment: lockfamily docs/managed.md). A user may tighten what it allows, never loosen it. locksql reads its [locksql] section:

version = 1
organisation = "Acme"

[locksql.bounds]
tier = "write"
tls = "verify-full"
credentials = ["vault", "ask"]
hosts = ["*.db.acme.internal"]
detectors = ["fr_nir"]
auto = { ddl = "deny" }
limits = { max_rows = 500, max_session = "2h" }

[[locksql.bounds.pii_mask]]
column = "crm.customers.email"
mode = "email"

[locksql.defaults]
limits = { max_rows = 100, max_session = "2h" }

[locksql.profiles.prod-erp]
engine = "postgres"
host = "erp.db.acme.internal"
database = "erp"
production = true
credentials = "vault"
vault_path = "database/creds/erp-ro"

The precedence is: the hard-coded floor > the managed bounds and profiles > the project file > the user file > the managed defaults > the built-in defaults. The merged result is checked against the bounds at the console's start and at every policy poll (every 2 s): a profile beyond them is refused with no prompt (no human can approve past it), one line each, audited refused-managed:

blocked by company policy: locksql.bounds.tier = write (profile dev: tier = ddl)

A managed file that cannot be read (badly owned, invalid, badly signed) is a refusal to start, never ignored. Once the console serves, a stricter file is applied at the next poll: the policy in force is replaced by the files' policy when that one is within the new bounds, else the session ends. The start screen shows Managed by <organisation> (sha256:…), the approved policy records managed_sha256 and every audit record managed.

Bounds #

[locksql.bounds] takes these keys; each is optional. "Looser" is what the bound refuses.

• credentials, engine and hosts are allowlists: a value outside one is refused naming the key (blocked by company policy: locksql.bounds.hosts does not allow db.other.com (profile dev: host + db.other.com)).
• [family] vault = { required = true } acts as credentials = ["vault"] on every network profile, cited as family.vault.required.
• A managed mask takes its mode over a rule of the same column in the rule set. A user [[allow]] that could clear a managed mask (*.*.email over crm.customers.email) is refused at load; a user may add mask rules, never remove or weaken a managed one.
• A bound on a duration or a size is never clamped silently: a profile beyond it is refused, and you lower it in the config file.

Defaults and profiles #

• [locksql.defaults] takes the keys of a profile, limits.* included. A key a profile leaves unset takes it, before the built-in defaults; a value of the config file wins (within the bounds). The limits table is merged key by key, ssh and auto are taken whole. A default beyond the bounds is a load error.
• [locksql.profiles.<name>] has exactly the keys of [profiles.<name>], is served as written (with the defaults), and wins as a whole: a project or user profile of its name is refused (profile "prod-erp" is provided by the company's managed configuration and cannot be redefined in the project config: rename yours). A managed profile beyond the bounds is a load error.

Commands #

• locksql managed show: the file's path, organisation, fingerprint and signature, then the active bounds, defaults and managed profile names. no managed configuration (exit 0) when there is none; a refused file prints the error (exit 3).
• locksql managed check [--profile NAME]: the profiles of the config files against the bounds, as the console checks them at start, without a connection or a credential: one blocked by company policy: … line per violation (exit 3), else ok: N profiles within the company policy (…) (exit 0). lox runs it before it starts the lock.
• locksql doctor has a managed line: the file as above, no managed configuration, or the error that refuses it.