NLQueries GitHub

Blog  / 

Letting an LLM query production safely: read-only roles, statement policy and timeouts

Published . Written for nlqueries-core 0.3.

security postgresql mcp

LLM database security comes down to one uncomfortable fact: the SQL that runs against your database is written by a model, and nobody controls what a model writes. Checking the statement first helps, but a checker is a program with bugs in it. So the answer is layers, ordered by how little each one depends on the others being right. This post goes through the layers nlqueries-core 0.3 uses, starting with the one that matters most and depends on nothing in the application at all.

Layer 1: a database identity that cannot write

The only boundary that holds regardless of what the model writes or the validator misses is the database's own permission system. Give NLQueries a login that cannot write and cannot read anything you would not put in an answer. Every other control here sits on top of that.

For PostgreSQL, Hardening the database walks through it in six steps. In short:

  • a dedicated role with NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;
  • CONNECT on one database, no TEMPORARY, and no CREATE on public;
  • USAGE and SELECT granted per schema, with default privileges so new tables are covered;
  • no EXECUTE for PUBLIC on functions, and no membership of pg_read_server_files, pg_write_server_files, pg_execute_server_program or pg_read_all_data (the last one quietly defeats every per-schema grant);
  • a fixed search_path, plus statement_timeout and default_transaction_read_only set on the role itself.

You do not have to remember to check it. Each time the PostgreSQL connector's pool opens a connection, it reads the login's privileges and logs a warning for a superuser, BYPASSRLS or membership of a file-reading role, and nlqueries health reports what it found. It reports. It does not refuse the connection.

Why the role matters more than the connector

The connectors do ask the engine for a read-only execution where they can, but what they can enforce differs a lot between engines (see What each connector enforces):

  • PostgreSQL and Redshift run each query in a READ ONLY transaction, which refuses writes outright.
  • SQLite and DuckDB open the file read-only and close off file access (ATTACH, read_csv_auto(), COPY ... TO) in code that no setting reopens. A :memory: database cannot be opened read-only, but the file-access restrictions still apply.
  • Snowflake runs the query inside BEGIN ... ROLLBACK. That undoes an INSERT, but a CREATE TABLE is not transactional there and stays.
  • MySQL and MariaDB, reached through the generic SQLAlchemy connector, get the rollback only. They commit implicitly around DDL, so a CREATE or DROP stands, and a MyISAM or MEMORY table has no transaction to roll back.
  • BigQuery has no transaction at all. A non-SELECT job is logged after it has run, which is an audit signal, not a control.

A rollback is not a refusal: the statement still ran. The SQL policy below refuses writes and DDL on every engine, but it is application code. On the database side, the grant is the only control for BigQuery, for DDL on Snowflake, MySQL and MariaDB, and for MyISAM and MEMORY tables. The database pages cover each engine's setup.

Encrypt the connection too. On PostgreSQL, the connector refuses a server with no TLS by default, and configuring ssl_ca_cert without an explicit ssl_mode selects verify-full, the only mode that checks both the certificate chain and the hostname. The same applies to a libpq PostgreSQL URL on the generic connector when ssl_* settings are configured. Other connectors differ: Redshift turns TLS on without CA options, and the SQL Server connector sets no TLS options of its own. See Transport security.

Layer 2: the SQL statement policy

Before any generated statement runs, NLQueries parses it with sqlglot and checks the whole tree, not only the first keyword. A statement is refused unless:

  • it is exactly one statement. A second statement hidden after a comment counts as two. When the model returns more than one, only the first is kept and re-checked, and the rest never run;
  • its root is a SELECT or a UNION;
  • nothing anywhere in it, including inside a CTE or subquery, is an INSERT, UPDATE, DELETE, MERGE, CREATE, DROP, ALTER, TRUNCATE, COPY, SET, GRANT, SELECT ... INTO or a row lock such as FOR UPDATE;
  • every function in it is one sqlglot recognises, or is on a short per-dialect allowlist. Vendor and extension functions such as pg_read_file fail this.

A statement that cannot be parsed, is larger than 100,000 bytes or is nested deeper than 100 levels is refused, not let through. The same policy is applied to SQL replayed from the semantic cache, where no model is involved, and to the corrected statement after a database error.

What the policy does not do is decide what may be read. A well-formed SELECT against a table the role can reach passes. That is the job of layer 1, which is why it comes first.

Layer 3: statement timeouts and result caps

A read-only SELECT can still hurt: a cross join over two large tables, or a full scan at the busiest hour. Two budgets bound it.

Time. CONNECTOR_STATEMENT_TIMEOUT_SECONDS (120 by default) is applied to each query by the engine's own mechanism: SET LOCAL statement_timeout on PostgreSQL, job_timeout_ms on BigQuery, a per-call timeout on Snowflake, and a watchdog that interrupts SQLite and DuckDB. SQL Server applies it as the driver's connection-wide query timeout and ignores a shorter per-call budget. Set a timeout on the role in the database as well (the PostgreSQL ALTER ROLE ... SET statement_timeout above), so the limit holds even for a connection that bypasses the application. On Redshift, keep REDSHIFT_SOCKET_TIMEOUT_SECONDS above the statement timeout; the Redshift notes explain why.

Size. A connector stops reading a result at 10,000 rows (CONNECTOR_MAX_FETCH_ROWS) or roughly 64 MB (CONNECTOR_MAX_RESULT_BYTES), whichever comes first, so SELECT * FROM events is not turned into millions of Python rows. PostgreSQL streams the result; other drivers may still buffer it in the client, and the byte figure is estimated from the first rows. NLQueries does not add a LIMIT to your SQL, because that would change what an aggregate means. It stops reading instead. The CLI's query then shows at most 200 rows, next to the number of rows the connector read. In 0.3 the MCP server's query tool generates SQL without running it, so it returns no rows.

Add a cost or slot cap in the warehouse too. A timeout bounds how long a query runs, not what it costs while it runs.

Layer 4: authentication on the MCP server

Over stdio, the default for desktop clients, the MCP server is a process the user launched. The caller already has whatever the process has, so there is nothing to authenticate. That changes the moment you run it over the network for other people. Even though the query tool does not execute SQL in 0.3, the server hands out your schema, your connector hosts and the questions people have asked, and it spends your LLM budget.

nlqueries mcp-server start --sse binds 127.0.0.1 by default and refuses a wildcard address unless you set NLQ_ALLOW_INSECURE_BIND=1. In 0.3 a networked transport also refuses to start without authentication. Configure one of:

  • an identity provider: NLQ_MCP_OIDC_DISCOVERY_URL and NLQ_MCP_OIDC_CLIENT_ID. Tokens are checked for signature, expiry, audience and issuer;
  • a pre-shared token: NLQ_MCP_STATIC_TOKEN (at least 32 characters, e.g. openssl rand -hex 32), or NLQ_MCP_STATIC_TOKEN_FILE to read it from a mounted secret.

Either way, set NLQ_MCP_RESOURCE_URL and a grants file in NLQ_MCP_GRANTS_FILE. A subject with no grant is denied, and grants are per agent and per action, so an analyst can be allowed query:execute on one agent and be refused it on the others. agents:list is not per agent, though: it still lists every agent's ID. Each principal is limited to 60 calls a minute and 8 in flight by default (NLQ_MCP_RATE_LIMIT_PER_MINUTE, NLQ_MCP_MAX_CONCURRENT). With a shared token, everyone is one principal and shares that allowance. Every decision, allowed or denied, goes to the nlqueries.audit logger. Authenticating the MCP server has the grants format and the full list of actions. The Docker Compose quickstart already requires NLQ_MCP_STATIC_TOKEN and will not start without it (getting started).

NLQ_ALLOW_UNAUTHENTICATED_MCP=1 restores an unauthenticated server and logs a warning on every start. Do not use it for anything other people can reach.

A checklist

  1. Create the read-only role and run nlqueries health against it.
  2. On PostgreSQL, use verify-full TLS with a CA certificate.
  3. Keep the default statement timeout, set one on the role as well, and cap cost in the warehouse.
  4. Use nlqueries ask to preview SQL without running it while you build trust in an agent. See ask.
  5. Before the MCP server leaves your machine, configure a token or an identity provider and a grants file.

The application-side checks are written by people who also write bugs. The role is the one control that does not depend on them. For how a question becomes a query in the first place, see Text-to-SQL, explained.

All posts · RSS feed · Documentation