Security Understand the database, filesystem, and MCP boundaries before using production data.
Version
Current
0.6.0
Status
pre-1.0

Security model

SQL Server permissions decide what Skiff can read. Skiff narrows how reviewed queries become a repo-owned DuckDB and protects publication from partial output; it does not replace database authorization, secret management, host isolation, or encryption.

Database authorization

Give every configured source a dedicated login with SELECT permission only on the required tables or views. Skiff marks its SQL Server connection read-only and rejects write-shaped SQL, but the login's grants remain the authoritative data boundary.

Skiff rejects multiple statements, SELECT INTO, non-query statements, and three- or four-part relation names. This prevents the checked-in query contract from writing data or naming another database or linked server through ordinary relation syntax.

Do not enable trust_cert in production. It disables certificate verification.

Credential handling

skiff.toml stores the name of each password environment variable, never its value. The launching shell, service manager, or MCP host supplies the value to the process.

Skiff is not a secrets manager. Protect environment configuration, process inspection, logs, shell history, and service definitions according to your threat model. Keep real .env files out of source control.

Repository confinement

Output, query, and check paths must be relative and remain inside the repository after canonicalization. Skiff rejects absolute paths, .. traversal, and symlink escapes. An existing destination must be a regular file with one hard link.

The manifest denies unknown fields, which turns misspelled or unsupported configuration into an error instead of silently ignoring it.

Query and resource limits

Each query has a timeout and, unless max_rows = -1, a row ceiling. A violation fails the private generation before publication. These controls bound accidental extraction; they do not replace database workload governance. Disabling the row ceiling should be an explicit review decision for a known query.

Optional checks run against the assembled private DuckDB and must return zero rows. Use them to enforce data invariants that SQL permissions cannot express.

Publication integrity

Skiff loads private per-source staging files, assembles a private generation, runs checks, checkpoints the database, sets owner-only file permissions, and atomically renames it over the destination.

Failures before rename leave the existing output untouched. The destination lock prevents concurrent Skiff refreshes, but it does not stop another program from writing the DuckDB. Skiff must be the only writer; consumers should open the file read-only.

Schema-drift reporting is advisory. It helps operators see table and column changes but does not block publication.

Consistency choices

Sources read without a transaction by default. Schedule refreshes outside source load windows and add checks for cross-table invariants.

Set snapshot = true when one source's queries must share a consistent database snapshot. SQL Server must have ALLOW_SNAPSHOT_ISOLATION enabled, which has server-wide write costs. This does not create a transaction across multiple sources.

MCP boundary

The MCP server loads a fixed plan at startup and exposes only a no-argument automatic refresh. A caller cannot provide SQL, paths, table names, sources, full-rebuild flags, or backfill bounds through that tool.

This boundary applies only to the MCP interface. An agent with shell or write access to the repository can alter files or invoke other commands. Remove those capabilities or use operating-system isolation when they are outside the intended trust boundary.

Plaintext output

The published DuckDB is plaintext. Protect it with operating-system permissions, full-disk encryption, backups, retention rules, and access controls appropriate to the copied data. Skiff does not encrypt the file or create a public attestation of its contents.

Production checklist