Blog / 2026-10-27

Connect Claude Desktop to your PostgreSQL database with MCP

Published 2026-10-27. Written for nlqueries-core 0.4.

mcp postgresql claude-desktop

Connecting Claude Desktop to a PostgreSQL database over MCP takes a few commands and one JSON entry. This post walks through it with nlqueries-core 0.4, where the MCP server's query tool generates SQL, runs it read-only and returns the rows. The order matters: the database role comes first, then the connection, then the knowledge base, and only then Claude Desktop. The last section covers what changes when the server is used by more than one person.

Step 1: a role that cannot write

Claude will be asking your database questions on your behalf. Whatever the model writes, the login it runs as decides what can actually happen. So start with a dedicated PostgreSQL role that owns nothing and can only read what you mean to expose.

Hardening the database has the full script in six steps. The short version:

CREATE ROLE nlqueries_readonly;
CREATE ROLE nlqueries LOGIN PASSWORD 'use-a-generated-secret' IN ROLE nlqueries_readonly;
ALTER ROLE nlqueries
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS;

GRANT CONNECT ON DATABASE mydb TO nlqueries_readonly;
GRANT USAGE ON SCHEMA analytics TO nlqueries_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO nlqueries_readonly;

ALTER ROLE nlqueries SET statement_timeout = '30s';
ALTER ROLE nlqueries SET default_transaction_read_only = on;

The full version also revokes TEMPORARY and CREATE, removes EXECUTE on functions from PUBLIC, fixes the search_path, and checks that the role is not a member of the file-reading roles. Do those too. Step 6 of that page, Check what you built, gives three statements to confirm it worked. If its CREATE TABLE succeeds, the role is not read-only, whatever else the script did.

Step 2: install and connect

nlqueries-core needs Python 3.11 or later. The base install includes the PostgreSQL driver and the MCP server:

pip install nlqueries-core
export ANTHROPIC_API_KEY=sk-ant-...    # or OPENAI_API_KEY
nlqueries health

nlqueries connect postgres --host db.example.com --database mydb \
    --user nlqueries --alias dev

Leaving out --password gives a hidden prompt, which keeps the password out of your shell history. connect tests the connection, writes the connection details to ~/.nlqueries/connectors.yaml and stores the password in your OS keychain rather than in that file. It registers the database as postgres:db.example.com:mydb. The alias saves typing that ID on every later command.

One thing to know before you run it: the postgres connector requires TLS by default, and connect tests the connection under that rule, so a server that offers no TLS cannot be registered. Certificate settings such as ssl_ca_cert are added by hand to the connector's entry afterwards. See Transport security.

Step 3: build the knowledge base

The MCP server answers from a knowledge base, not from a live look at the catalogue, and an agent does not exist until its knowledge base has been exported. Three commands build it:

nlqueries extract-schema dev
nlqueries process-history dev --days 30 --annotate
nlqueries export-kb dev

process-history is what makes the knowledge base more than a schema dump. It reads pg_stat_statements, deduplicates and clusters the queries your team actually runs, and keeps them as annotated query templates: examples of how your schema is really used. Without the extension it stops with ✗ Query history unavailable: and the reason. You can enable history capture or skip to export-kb, which still writes the full schema. Why your schema isn't enough explains what the history adds.

Run export-kb again whenever you re-run process-history. Before involving Claude, check the agent from the command line:

nlqueries query dev "How many orders shipped last month?"

The MCP server runs the same pipeline, so a problem found here is easier to fix here than through Claude.

Step 4: add the server to Claude Desktop

Over stdio, Claude Desktop starts the MCP server itself and talks to it over the process's own pipes. This is the configuration from the CLI reference, for claude_desktop_config.json:

{
  "mcpServers": {
    "nlqueries": {
      "command": "nlqueries",
      "args": ["mcp-server", "start"]
    }
  }
}

nlqueries mcp-server start with no flags is the stdio transport. Two details decide whether it works the first time:

Claude Desktop has to find nlqueries. If you installed into a virtual environment, put the full path to its nlqueries executable in command.
Claude Desktop has to pass your LLM key. nlqueries reads settings from environment variables, or from a .env file in the directory it is started in, and only that directory. Claude Desktop starts the server in a directory you do not choose, so give the key to the server through the env block Claude Desktop accepts for each server entry.

The connectors file and the knowledge base are read from ~/.nlqueries, and the password from your OS keychain, so nothing else needs passing.

Restart Claude Desktop and the server's nine tools appear: list_agents, get_agent_schema, query, submit_feedback, health, invalidate_cache, list_connectors, get_query_history and get_cache_stats.

Asking questions

Ask Claude something your database can answer: "Which ten customers spent the most this quarter?" A typical exchange calls list_agents to find the agent and query to answer. The agent ID is the knowledge base's file name, so postgres:db.example.com:mydb appears as postgres_db.example.com_mydb. The server resolves either form to the same connector.

What query returns:

the answer;
the generated SQL;
the rows as a table, saying how many were shown when the result was larger;
the agent type and latency.

Two optional arguments help when you want to check its work. paraphrase adds a plain-English reading of the SQL, produced without an LLM, so you can confirm what it does. explain adds provenance: the route taken, cache hit or miss, how much knowledge was injected, and the checks that ran. get_agent_schema shows the tables and columns the agent knows about, which is the quickest way to see why a question was misunderstood.

The SQL runs read-only. The query tool runs it under the read-only execution policy, and on PostgreSQL each statement runs in a READ ONLY transaction. The tool waits 45 seconds and gives the database 40 of them for the statement, so a slow query is stopped rather than left running after Claude has given up. If the first question times out, the usual cause is the embedding model loading inside the server process. Start the daemon once with nlqueries embed-server start and later calls use it.

Everything above is single-user. Over stdio the caller already has whatever the process has, so there is nothing to authenticate, and the calls are still authorised and audited against the process owner. That changes as soon as the server listens on a network port for other people.

nlqueries mcp-server start --sse binds 127.0.0.1 and will not start without authentication. Configure either an identity provider (NLQ_MCP_OIDC_DISCOVERY_URL and NLQ_MCP_OIDC_CLIENT_ID) or a pre-shared token (NLQ_MCP_STATIC_TOKEN, at least 32 characters), plus NLQ_MCP_RESOURCE_URL. With authentication on, it also needs a grants file in NLQ_MCP_GRANTS_FILE. A subject with no grant is denied, and running SQL is a separate action (query:execute) granted per agent, so a colleague can be allowed to read one agent's schema and nothing more. Authenticating the MCP server has the format, and Letting an LLM query production safely covers the layers underneath.

Try it

pip install nlqueries-core, then follow the four steps above against a read-only role. The source and issue tracker are on GitHub. For how a question becomes SQL in the first place, see Text-to-SQL, explained.

All posts · RSS feed · Documentation