Blog /
An NLQueries MCP Server Setup Checklist: Tools, SQL Checks and Authorization
Published . Written for nlqueries-core 0.4.
mcp security authorization
It usually starts on one laptop. An engineer adds a database tool to Claude Desktop, asks "what was paid revenue for tenant A in October?", and gets back a number, the SQL that produced it, and a tidy table of rows. It feels finished. Then a teammate asks to use it too, and somebody proposes running the same server on a shared box so the whole analytics channel can point their clients at it.
That's when the setup stops being a convenience and becomes an access-control decision. A working tool call proves your client can reach the server. It doesn't prove who's calling, what they may ask, or what the database login behind the server can read. This post works through the MCP server that ships with NLQueries Core 0.4.1: the local stdio setup, the authenticated network setup, and the tests that tell you the controls do what you think.
It's a configuration checklist built from the project's documentation and source at a pinned commit. A few components were checked in isolation, such as the server's registered tool list and how its authorizer decides the grants file shown later. No client handshake, token verification or network denial was run, so treat every config here as an untested prototype and every expected behavior as an acceptance test you still owe.
Three boundaries that a working tool call doesn't prove
The Model Context Protocol (MCP) is a standard way for an AI client to discover and call tools exposed by a server. It says nothing about authorization, so separate three layers before you touch a config file. Client connectivity: can the client start or reach the server and list its tools? Server identity and grants: does the server know who's calling, and may that caller take this action on this agent? Database permission: what can the login behind the server read, and what would the database refuse even if every check above it failed?
NLQueries' docs draw the same line. The authentication guide points out that the SQL policy constrains what may be run and never who may run it. The database hardening guide goes further: a validator is a program with bugs in it, so the only boundary that doesn't depend on the validator or the model being correct is the database's own permission system.
Get the agent answering before any client sees it
In NLQueries, an agent is a knowledge-base file under KB_PATH (which defaults to ~/.nlqueries/knowledge_base/), and the MCP server lists only those, so the agent has to exist first. You register the database with nlqueries connect and build its knowledge base with nlqueries export-kb, as the client guide describes.
Then ask the running example's question with nlqueries query before you configure any client. The server runs the same pipeline, and a problem is easier to read in a terminal than in a chat window. To see the SQL without running it, the CLI's ask command generates and validates SQL without executing it (CLI reference), though with literal grounding on, the default, it may still open the connector read-only to check string values. "Preview" doesn't automatically mean zero database reads.
The running example uses a small fixture with a known answer: three customers across two tenants, six orders including a cancelled one, one outside the month and one with a NULL amount, and an order_items table where order 101 has two lines:
-- Synthetic fixture (SQLite syntax). Not real data.
CREATE TABLE customers(id INTEGER PRIMARY KEY, tenant_id TEXT NOT NULL, region TEXT);
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id),
order_date TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE order_items(id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id), sku TEXT);
INSERT INTO customers VALUES (1,'A','East'),(2,'A','West'),(3,'B','North');
INSERT INTO orders VALUES
(101,1,'2026-10-01',10000,'PAID'),(102,1,'2026-10-02',5000,'CANCELLED'),
(103,2,'2026-10-31',20000,'PAID'),(104,3,'2026-10-02',70000,'PAID'),
(105,1,'2026-11-01',40000,'PAID'),(106,2,'2026-10-15',NULL,'PAID');
INSERT INTO order_items VALUES (1,101,'a'),(2,101,'b'),(3,103,'c');
-- Reference answer: tenant A, paid orders, October 2026
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o JOIN customers AS c ON c.id = o.customer_id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Against this fixture the reference query returns (30000, 3) on SQLite 3.45.3: 30,000 cents, three paid orders, the NULL-amount order counted but not summed. That's SQL arithmetic, not a model-generated answer. And tenant_id = 'A' is an expected-answer filter, not an authorization boundary; nothing below turns it into one.
The local setup: stdio and two details that bite
For one person on one machine, use the stdio transport: the client launches the server process and talks to it over pipes, which is what nlqueries mcp-server start with no flag gives you. There's no token, because the caller already has whatever the process has, including the knowledge base, the connector file and the stored credentials; the docs call a token there theatre. Calls are still authorized and audited, against a principal that is the process owner, and for the same reason stdio has no rate or concurrency limits.
Here's the shape of a Claude Desktop entry on Windows (untested sketch; replace every path):
{
"mcpServers": {
"nlqueries": {
"command": "C:/path/to/venv/Scripts/nlqueries.exe",
"args": ["mcp-server", "start"],
"env": {
"ANTHROPIC_API_KEY": "<your provider key>",
"NLQ_STATE_DIR": "C:/path/to/project/nlq-state",
"KB_PATH": "C:/path/to/project/nlq-state/knowledge_base"
}
}
}
}
Two details from the client guide decide whether this works the first time. command needs the full path to the executable, because the client doesn't start from your shell and won't find an nlqueries that's only on a virtual environment's PATH. And env has to carry your settings: NLQueries reads .env only from the directory it starts in, and the client chooses that directory, so a key in your project's .env isn't seen. Cursor reads the same shape of entry from its mcp.json. Keep provider keys in your client's secure configuration, not in shared files.
Once it connects, list the tools before you call any. At this commit the server registers nine: list_agents, get_agent_schema, query, submit_feedback, health, invalidate_cache, list_connectors, get_query_history and get_cache_stats. Introspecting the Core 0.4.1 tool registry returns exactly those nine, but that's the server's own view, not what a client received over a real handshake. Capturing the list from your client is your first acceptance test, and the cheapest way to catch a version mismatch.
What the query tool really does
This is the part most often misread, so check the server source rather than guessing. The signature is:
# From nlqueries/mcp_server/server.py at Core 0.4.1 (signature as inspected, abbreviated)
async def query(
question: str,
agent_id: str,
dialect: str | None = None,
explain: bool = False,
paraphrase: bool = False,
) -> str: ...
The input schema the server registers matches: question and agent_id are required, the other three optional. There's no preview flag. The tool executes: it calls the orchestrator with a read-only execution policy (ExecutionPolicy.execute_read_only()) and returns the answer, the generated SQL and the rows. The dialect argument is accepted and ignored. The SQL is always written for the database the agent's connector points at, and a client asking for a different dialect gets a warning in the server log, once per agent and dialect. That's deliberate, since the SQL policy can only vouch for a statement it parses with the grammar of the engine that will run it. explain=True appends provenance: route, cache hit or miss, knowledge injected and checks run. paraphrase=True adds a plain-English reading of the SQL produced without a model.
The tool budgets its own work, and it's easy to read too much into the numbers. The MCP caller wait is budgeted at 45 seconds, and a supported database statement gets a 40-second timeout. The turn deadline gates correction attempts. These are separate clocks, so cancellation and any remaining database work must be tested for the connector you pick. The source explains the split: cancelling the wait doesn't stop a statement already running in its worker thread. The sync runner notes that the statement timeout bounds one statement, not the model call, and the connector base class that connectors without a statement-level timeout ignore it.
So when you call query(question="For tenant A, what were paid-order revenue and paid-order count in October 2026?", agent_id="sales", explain=True), expect rows back, not a proposal. If a human must approve SQL before it runs, that happens through the CLI or Python path, not a flag on this tool.
The SQL checks, and the wrong answer they won't catch
Before a generated statement reaches a connector, the SQL policy parses it with sqlglot in the connector's dialect. It allows only the constructs and functions it lists for that dialect, and it refuses things a read query has no use for, such as session variables and CURRENT_USER (database hardening; sql_policy.py). On PostgreSQL, the connector also opens every query in a read-only transaction.
That's a floor, and the docs are candid about where it stops. A read-only transaction still permits pg_sleep, advisory locks, file-reading functions if the role holds the privilege, and SELECT on anything the role can reach. Read-only isn't least privilege; you still want a dedicated login that can't write or see anything you wouldn't put in an answer.
The policy can't tell you whether the answer is right. Join order_items into the reference query, a plausible move for a model when a question mentions "orders", and order 101 is counted once per line item:
-- Fan-out variant: valid, read-only, policy-friendly, and wrong
SELECT SUM(o.amount_cents) AS revenue_cents, COUNT(*) AS paid_order_count
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS i ON i.order_id = o.id
WHERE c.tenant_id = 'A' AND o.status = 'PAID'
AND o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01';
Against the same fixture on SQLite 3.45.3, this variant returns (40000, 3). Revenue is inflated by 10,000 cents because order 101 now appears twice. The count still reads 3 only by coincidence: order 106, the NULL-amount order, has no line items, so it drops out of the inner join while 101's duplicate takes its place. Nothing about the statement is unsafe, so no read-only check will object. explain=True and the visible SQL are how a person spots the extra join, and it's why "the tool connected and the policy passed" should never be reported as "the answer is correct."
Going over the network: identities, grants and limits
To serve more than one person, switch to SSE with nlqueries mcp-server start --sse --port 8000. The server won't start that way without a verifier, and the refusal names what's missing (authentication guide). There are two options, and setting both is refused.
With an identity provider, you set NLQ_MCP_OIDC_DISCOVERY_URL, NLQ_MCP_OIDC_CLIENT_ID and NLQ_MCP_RESOURCE_URL. According to the docs, tokens are checked against the provider's keys for signature, expiry, audience and issuer, and the token's sub becomes the caller's identity. The resource URL is advertised to clients, not checked in the token, so give the MCP server a client registration of its own. All of that is documented behavior that still needs a run against your provider. With a pre-shared token, you set NLQ_MCP_STATIC_TOKEN (at least 32 characters), or NLQ_MCP_STATIC_TOKEN_FILE to read it from a mounted file, and optionally NLQ_MCP_STATIC_SUBJECT, which defaults to static-token. Either way, NLQ_MCP_GRANTS_FILE is mandatory, and the server refuses to start without it rather than serving something that looks up but denies everything.
A narrow grant for the running example looks like this:
# grants.yaml: subject must equal the verified OIDC `sub`
# or the configured static-token subject.
grants:
- subject: "analyst-test"
agents: ["sales"]
actions: ["agents:list", "schema:read", "query:execute"]
- subject: "schema-browser-test"
agents: ["sales"]
actions: ["schema:read"]
Loaded into Core's allowlist authorizer, this file gives the expected decisions: analyst-test may read the schema and run queries on sales, schema-browser-test gets the schema but is denied query:execute, and an unknown subject is denied both. That checks decision logic alone, with no token verifier or network transport in front, so the same cases belong in your end-to-end tests.
Each tool maps to an action: query:execute covers query, schema:read covers get_agent_schema, agents:list covers list_agents, and others cover history, feedback and cache operations. A subject with no matching grant is denied, and there's no deny list. Lists must be real YAML lists, because a bare string such as "prod-*" would iterate into characters, one of which is *. Two subtleties: query:execute also covers document sources if the agent lists any, and the shipped Docker Compose stack writes a grants file that gives its token's subject every action on every agent, which is fine for a quickstart but isn't per-user security.
A warning about the pre-shared token: every accepted token resolves to the same subject, so the whole team is one principal. Audit records can't tell people apart, and the default limits of 60 calls a minute and 8 in flight are shared by everyone holding the token. Those limits are kept per process, so three replicas behind a load balancer triple them; it isn't a distributed limiter. To know who asked what, use the identity provider.
The server binds 127.0.0.1 by default and refuses a wildcard address even with authentication on, unless you set NLQ_ALLOW_INSECURE_BIND=1. For remote access, the docs recommend a proxy in front of the loopback port, with NLQ_MCP_RESOURCE_URL set to the address clients use so the Host and Origin checks accept them. Don't reach for NLQ_ALLOW_UNAUTHENTICATED_MCP=1 as a shortcut: it grants every caller everything, including query, which runs SQL against your database.
The denial tests to run before anyone else connects
A setup you've only seen succeed is one you haven't tested. Run the checks that should fail through a real client against the running server, and keep what you see.
Start with identity: a request with no token, or a wrong one, should be refused before any tool runs. Then grants. schema-browser-test should be able to call get_agent_schema on sales and should get Not authorized to perform query:execute on 'sales'. when it tries query. analyst-test should be refused on any agent other than sales. The refusal names the action and agent but never the reason, so a caller can't learn which agents exist from the wording.
Then check the record. The full decision goes to the nlqueries.audit logger, with denials at WARNING, and to a file if you set NLQ_AUDIT_LOG (configuration; observability). Each statement sent to the database gets a query_execution event under the same request ID, carrying a digest of the SQL, never the SQL, rows or credentials. One catch: that event is built only when the audit logger would emit at INFO or NLQ_AUDIT_LOG is set. Raise the server's log level above its INFO default without the file and the events are silently skipped.
Operational errors should look different from authorization failures. An unknown agent should say so without listing others. A failed query should return only the first line of the error plus a short reference you can match in the server log, since a driver error's later lines can quote the statement or a connection string. That's narrower exposure, not a secret scrubber; the first line itself can still carry sensitive text. Finally, send a deliberately slow request. The client should get the timeout reply when the 45-second wait runs out; watch the database's own activity too, and try a statement that starts late in the turn and one that triggers a correction, so you know what's still running after the client has been told it timed out.
None of this proves row isolation. Agent grants decide whether a caller may query an agent, not which rows that caller sees. If two people with query:execute on sales should see different tenants, that has to be enforced below MCP, in the database role or in separate agents with separate credentials.
Write down the contract you actually verified
The result worth keeping isn't "it works in Claude Desktop." It's a short record you can rerun after every upgrade: - the exact NLQueries version and client build; - the tool list the client received; - the grants file; - which denial cases you ran and what each returned; - the database role the connector uses, and what a slow statement did on it; - whether audit events are reaching somewhere durable.
Build the six-row fixture as its own agent, ask the October question with nlqueries query until you get 30000 and 3, then connect it over stdio and capture the tool list your client receives. Start the SSE server on loopback with two test subjects, one schema-only and one with execute. Don't hand the URL to your team until the wrong-token, schema-only and wrong-agent requests have all been refused and each refusal shows up in your audit file.
References
- NLQueries Core 0.4.1. Connecting MCP clients. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Authenticating the MCP server. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. MCP server implementation (
server.py). Commit 2fbf276. Accessed 2026-11-05. - NLQueries Core 0.4.1. Sync runner (
sync_runner.py). Commit 2fbf276. Accessed 2026-11-07. - NLQueries Core 0.4.1. Connector base class (
connectors/base.py). Commit 2fbf276. Accessed 2026-11-07. - NLQueries Core 0.4.1. SQL policy implementation (
sql_policy.py). Commit 2fbf276. Accessed 2026-11-04. - NLQueries Core 0.4.1. Hardening the database NLQueries reads. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. Observability. Commit 2fbf276. Accessed 2026-11-04.
- NLQueries Core 0.4.1. CLI reference. Commit 2fbf276. Pinned copy inspected 2026-11-05.
- NLQueries Core 0.4.1. Configuration reference. Commit 2fbf276. Pinned copy inspected 2026-11-05.
- NLQueries. Documentation, v0.4.1. Accessed 2026-11-05.