Reference

This page holds the tables you look things up in. pick works at three levels, called tools, guardrails and screenings, and every refusal and every doctor line below says which level it belongs to. A refusal that belongs to the guardrails level can only happen when guardrails are on.

What belongs to each level

The file .picker.json holds up to three sections, one for each level, and nothing else at the top. The tools level is always on, and the file must name at least one server in its tools section. Each of the other two levels is on when the file has its section.

Here is a full file that uses all three levels:

{
  "tools":      { "servers": { "main": { "connect": "env:MAIN_SQL" } } },
  "guardrails": { "servers": { "main": { "databases": {
                    "Sales": { "can": ["list", "select", "describe"],
                               "scopes": [ { "object": "dbo.Customers", "can": ["list", "select"],
                                             "columns": ["-ssn", "-dob"] },
                                           { "object": "audit.*", "can": [] } ] } } } } },
  "screenings": { "models": "../screening-models",
                  "screen": { "Sales": ["identifiers"], "Sales/dbo.Customers": true } }
}

• connect is env:NAME, which reads the connection string from an environment variable with that name. A literal string may go in .picker.local.json, but the checked-in file refuses one that holds a password.
• can lists the verbs that are allowed, out of list, select and describe. An empty list allows nothing, and a scope must always state its can.
• columns is true for every column, a plain list for only those columns, or a list of names with a leading minus for every column except those. pick refuses a list that mixes the two forms.
• A screen value is true for every category, false for none, or a list of the category words to screen for.
• With guardrails off, every server is open, and its databases are the online ones that the login can enter, apart from the four system databases. Each of them grants list select describe, and a screen key may only name a whole database.

Configuration refusals

pick reads both files before every call, and if either file breaks a rule, pick refuses to load it. The call then ends with exit code 2, invalid, and the message names the file. An unknown word anywhere in a file is refused too, and the message lists the words that are allowed in that place.

At every level

Tools level

Guardrails level

Screenings level

The old shape, rewritten

Take a file in the shape that releases before 2.1.0 read:

{ "servers": { "main": { "connect": "env:MAIN_SQL",
    "databases": { "Sales": { "can": ["list", "select"], "screen": ["identifiers"] } } } } }

pick refuses it, and the message ends with the rewrite. The message is one line, which is shown here wrapped:

pick: .picker.json has "servers" at the top, which is the shape this version no longer
reads. Rewrite it with three sections, one per level. Your entries, rewritten:
{"tools":{"servers":{"main":{"connect":"env:MAIN_SQL"}}},"guardrails":{"servers":{"main":
{"databases":{"Sales":{"can":["list","select"]}}}}},"screenings":{"screen":{"Sales":["identifiers"]}}}
(C:\github\app\.picker.json)

The rewrite moves each connect under tools and each database under guardrails. It moves models and every screen under screenings, where a scope's screen gets a key made of the database, a slash and the scope's pattern.

How a name resolves

These rules decide which table a name in a query means. The first group applies at the tools level, so it always applies.

• A three-part name, such as Sales.dbo.Customers, is looked up in the databases that are there, and any other database answers the same as a table that does not exist.
• A two-part or bare name, such as dbo.Customers or Customers, is searched for across those databases, and pick never asks the connection for a default database.
• If two databases both hold a match, pick refuses the query and names both of them, and you fix it by writing the full name.
• A name defined by a CTE in the same query wins over a table with the same bare name. A CTE is a named subquery written at the start of a query with WITH.
• A four-part name is refused, because it points at a linked server, which is a second server reached through the first.
• A query may join databases on one server, but it may not span two servers.

The second group applies at the guardrails level, so it applies only when guardrails are on.

• An object whose scope grants no list is hidden. It is missing from objects and from the bare-name search, and when you name it, pick refuses it in the same words as an object that does not exist.
• An object that is listed but not granted select is refused plainly, and the refusal names what its scope does grant, because the listing already showed that the object exists.

The verbs, in detail

All five verbs belong to the tools level. They work the same way at the command line and through MCP, and the MCP answer equals the answer the command line gives with --json. The flag --config PATH names the file to use, and without it the nearest .picker.json at or above the current folder applies.

With --json, the catalogs answer adds a levels object. It also gives each server an open field, which is true when guardrails are off.

The query gate

The gate belongs to the tools level, so it is always on, and every refusal in the table below is a tools-level refusal. The gate parses every statement before anything reaches the server, which means it breaks the text into its parts and checks each one. It checks the parts against a list of the ones it knows, and anything else refuses the whole statement. So a new T-SQL feature is refused until the gate learns about it.

pick rewrites a statement that passes before it runs it. Every table and view becomes its full three-part name in square brackets, so the statement can only mean what the gate approved. The star in SELECT * becomes the list of columns you may read, and the answer shows that list. The gate walks CTEs, subqueries, derived tables, APPLY and set operations in the same way, and it approves a query whole or refuses it whole.

Column scoping, everywhere a column can appear

Column rules belong to the guardrails level. A scope's columns entry says which columns exist for the caller, and Guardrails shows how to write one. This section says where pick enforces the rule.

• A hidden column is checked everywhere it can appear. That covers the select list, WHERE, ON, GROUP BY and ORDER BY, and the inside of expressions and subqueries. A filter on a hidden column would reveal its values one guess at a time, so it is refused too.
• The refusal uses the same words as a column that does not exist, and it names no table, so nobody can use it to learn that the column is there.
• The star cannot reach a hidden column. It becomes the list of allowed columns before the server sees it, and the answer lists them.

DESCRIBE, and what a view will not say

The describe verb belongs to the tools level. It answers from the system catalog with the columns, their types and whether each may be empty, and then the indexes and foreign keys. It never runs the object it describes. With guardrails on, hidden columns are missing from all of it. An index or key that uses a hidden column is left out whole, because a note saying "one column not shown" would show that the column exists.

Every answer carries a shape hash, a short code computed from the table's shape that changes when the shape changes. A view answers with its columns and never with its definition. The text of a view names the tables under it and how they are joined, which may be exactly what the scopes hide. So pick never returns the CREATE VIEW text of any view.

Caps, stated in the answer

The caps belong to the tools level, and the configuration file cannot change them. Each answer says so when a cap cut it short.

• Rows: an answer holds at most 100 rows by default, and --limit N raises or lowers that number. A capped answer says how many rows it left out.
• Text: one cell holds at most 2,000 characters and one answer at most 40,000, and pick always says when it cut text.
• Time: a query may run for 30 seconds, and pick stops a slower one and reports it as timed-out, which is its own outcome.

doctor's lines

The doctor verb prints one line for each check. A healthy line starts with ok and a failed one with !!, and the three level lines always come first. A level that is off gets one line, and doctor runs none of that level's checks.

The doctor verb never prints a connection string or a password. It says whether the test connection opened, and not how it signed in.

Exit codes

Each kind of failure has its own exit code, so a script can branch on $LASTEXITCODE without reading any text. pick uses fettle's numbers wherever the words match, so one script can drive both programs. The gaps in the numbers are fettle outcomes that pick cannot produce, such as a stale hash, and no number is reused for a different word. A check in this repository holds this table to the source code, row by row.

The outcomes denied and refused are kept apart on purpose. If a login failure looked like a gate refusal, you would spend time debugging rules that are working.

What this defends against, and what it does not

• It guards against an assistant's mistakes, and not against a determined person. Whoever owns the machine can edit .picker.json and holds the real passwords, and that is by design.
• The server never runs the caller's own text. It runs the gate's rewrite, with full names in brackets, the star replaced by columns, and only parts the gate knows. A statement the gate cannot parse never leaves pick.
• The login is a second check, and the server enforces it. Connect as a login that is granted only SELECT and VIEW DEFINITION, so that even a fault in pick can reach nothing more. Declaring a database grants the login nothing, and doctor reports a connection that fails.
• It does not defend against everything. A hostile server administrator is out of its reach, and so is a catalog that reports false facts. Data that an allowed column holds by design is the screen's job, and a clean screen is evidence, not proof.