MCP server pg_licht

pg-licht

A read-only PostgreSQL MCP (Model Context Protocol) server: it lets an AI assistant read a database's schema, statistics, plans, locks, replication and PgBouncer pools from the catalog. It never writes, and never returns rows from your tables.

Why another PostgreSQL MCP:

• It answers from the catalog and the statistics views and never returns your rows, so there is far less to leak: the model sees structure, sizes, plans and counters.
• Read-only is enforced, not promised: every call runs in its own READ ONLY transaction with a statement timeout, and no SQL is built from arguments. The one tool that executes a statement of yours refuses to when its plan writes or calls a volatile function.
• One process serves the whole fleet. Any number of connections live in one file, and a question can be put to every database of an instance, a replication group or a label at once.
• It answers what an operator asks — why is this slow, what is blocked, is the replica keeping up, does the pool keep up — and returns the evidence as PostgreSQL reports it.

Safety

Every catalog query is parameterized — no schema, table, or search-term argument is ever concatenated into SQL text. Every call to a database runs inside its own READ ONLY transaction, so even a bug that let a query attempt a write would fail rather than succeed silently. The pooler tools send a PgBouncer console only fixed SHOW commands, as the user you configure — one from its stats_users, which PgBouncer allows to run SHOW and nothing else. That guard is transaction-scoped rather than session-scoped, which is what makes it hold behind a connection pooler in transaction mode.

The same transaction sets statement_timeout, so no call can occupy a backend indefinitely. It defaults to two minutes and is per connection:

[billing_prod]
service              = billing-prod
statement_timeout_ms = 600000      ; 0 removes the ceiling

Most operations are catalog reads that never approach it. The ones that can are those whose cost scales with the server rather than with the query — tableBloat with exact: true, indexBloat on a btree or hash index, bufferCacheContents, and listTableSizes on a wide schema — so raise it for a connection where such a scan is the point of the call. Reaching it is reported as the ceiling being reached, naming the value and the key to change, rather than as an error.

pg_licht never writes and never returns rows from your tables. Values from your data can still reach the caller in a few named places: live and recorded statement text (currentActivity, currentLocks, statementStats), column statistics (tableStats, columnHistogram), plans that repeat a statement's literals, definitions as written, and settings such as a standby's primary_conninfo. When an explained statement fails, PostgreSQL's message is returned only where it is about the statement as written, since a data error quotes the value that failed. explainQuery with analyze returns no row, and its row counts can still confirm a value the caller proposes. No password pg_licht itself holds is ever returned. Connect as the narrowest role that answers the question; checkPrivileges reports what it can reach.

Full rationale, including the guarded exception for explainQuery and the complete list of what reaches the caller, is in the SECURITY CONSIDERATIONS section of man pg_licht_mcp; SECURITY.md summarises it and says how to report a vulnerability.

Quick start

brew tap sqlambda/pg-licht && brew install pg-licht

claude mcp add --transport stdio pg-licht \
  -e DATABASE_URL="postgresql://user:pass@host/dbname" \
  -- /usr/local/bin/pg_licht_mcp

man pg_licht_mcp

Other install channels — deb, rpm, tarball, source — are in INSTALL.md.

Remote databases

A tool call opens no connection of its own beyond the first: connections are held between calls and closed after 60 seconds idle, one per configured connection and at most 32 at a time. Startup opens none at all — the registry is validated without touching the network, so one unreachable database no longer prevents the server from starting.

The ceiling matters only above 32 configured databases, where the least recently used connection is dropped to make room and the next call to that database reconnects. It exists so a single wide sweep cannot hold one socket per member for a minute and exhaust a file-descriptor limit — macOS defaults to 256.

Fan-out sweeps visit up to 16 members concurrently, one connection per server. A thirteen-member replication group that took 449 ms sequentially takes 91 ms. The payload stays in configuration order regardless of which member answers first.

For databases across a WAN, a connection pooler in front of pg_licht is still worth running — it amortises the TLS handshake across restarts, and server_idle_timeout gives you the same idle policy at the pooler. Point every connections.ini entry at the local pooler and let its [databases] section carry the real hosts. Note that PgBouncer reads neither .pgpass nor service=, so that section has to be generated rather than shared, and in transaction mode it does not run server_reset_query at all by default.

Output format

A client that negotiates MCP revision 2025-06-18 or later receives structuredContent; every client receives the text block as well.

Both are sent by default, because a client can advertise a revision it does not fully implement and this server cannot tell. Set PG_LICHT_STRUCTURED_ONLY=1 to send only the structured payload once you have confirmed your client reads it, or PG_LICHT_MAX_PROTOCOL=2025-03-26 to refuse to negotiate anything newer.

Every tool declares an outputSchema to clients that can use it. The schemas describe but never reject: payloads here are version-conditional, and any tool may answer {error, hint} when an extension is absent.

Results that the protocol marks cacheable carry ttlMs and cacheScope. tools/list is about 171 kB (138 kB before MCP 2025-06-18, which carries no output schemas) and changes only when the connections file is reloaded, so it is hinted at one minute and scoped private — it varies by negotiated revision and names the configured default connection, so it must not be served to another caller from a shared cache. Catalog-derived results are hinted at one minute too.

List operations accept an opaque cursor and return nextCursor. The page size is larger than anything this server lists today, so a client that ignores cursors still sees every tool; resources/list is the one that grows, being connections × schemas.

Resources and prompts

Structure is also served as resources — documents a client can browse and pin into context without a tool call:

pglicht://{conn}/schemas
pglicht://{conn}/schema/{schema}
pglicht://{conn}/schema/{schema}/table/{table}
pglicht://{conn}/schema/{schema}/{functions,enums,types}
pglicht://{conn}/server/{roles,extensions,settings}

Readings stay tools, because the model has to decide when to take them. resources/list enumerates connections and schemas and reaches tables through a template, so a 10 000-table database does not produce a 10 000-entry response.

Thirteen prompts encode an order of investigation that is easy to get wrong: diagnose-slow-query, triage-lock-contention, diagnose-deadlock, triage-active-sessions, triage-disk-space, bloat-and-vacuum-review, buffer-cache-review, capacity-check, replication-slot-review, plan-schema-change, review-clustering, check-role-access and explain-and-fix. The triage- ones are written for a page rather than an investigation: they establish how long there is before they propose anything. They are static text and touch no database until the model acts on them, and each one whose tools are privilege-gated opens by calling checkPrivileges.

triage-disk-space is the one that arrives at night, and diskUsage is what makes it answerable without a shell on the server: WAL size, the archive backlog, the temp files present now, the log directory, and sizes per tablespace and per database. Only the headroom is unreachable — PostgreSQL exposes no function for free space. The prompt leads with what makes the obvious response wrong: VACUUM FULL needs free space equal to the table and its indexes before it releases any, so it is not a disk-full action.

Counts: 59 of 73 database operations for a bare login role, 67 with pg_monitor — the pg_ls_* directory reads diskUsage uses are part of what that role grants.

triage-active-sessions is for a server running more sessions at once than it has cores. Its first move is to stop trusting the word active: a backend waiting on a lock or a disk read is executing a statement, so only the ones with no wait event are competing for CPU, and parallel workers collapse into their leaders before anything is counted. Its second is that concurrency is arrival rate times duration — so it rises when statements get slower even though the load did not change, and more cores treat the symptom.

check-role-access answers whether a role can use a table, view, function or procedure — walking the gates in order, since the one people forget is USAGE on the schema and the resulting error names the table. It treats row-level security as a second gate that opens after the grants already said yes: RLS enabled with no applicable permissive policy is deny-all, so every grant checks out and the table returns nothing.

diagnose-slow-query is the hub — a slow statement can end in a plan fix, a vacuum change, an index, or a schema change, and it routes to the others accordingly. plan-schema-change answers "how should I apply this DDL here?" by measuring the table rather than prescribing a method — it carries no DDL recipes, because the same statement that is instant on an empty table is an outage at a billion rows, and only the row count, measured size, existing indexes and current lock waits can tell the two apart. Completions are offered for the conn, schema and table variables.

Tools

76 read-only operations, grouped as schema exploration, catalog search, cluster-wide objects, extensibility and text search, foreign data and replication, monitoring and statistics, diagnostics and query planning, topology, connections, and connection poolers. Highlights include tableDetails (columns, indexes, constraints, foreign keys in both directions, triggers, policies), searchTables (full-text search across names, descriptions, and enum values), and explainQuery (recover a slow statement from pg_stat_statements by queryid and get its EXPLAIN plan).

Two arguments narrow an answer by a string, and they match differently:

argument	how it matches	taken by
pattern	a literal, case-insensitive substring of a name — _ and % match themselves, nothing is stemmed, so user_ finds user_x and not users	listSchemas, listTables, listTableStats, listTableSizes, listFunctions, listSequences, listRoles, serverSettings, poolerConfig, listConnections, listTopology
web_search	full-text search (websearch_to_tsquery, English) — words are stemmed, names are split into words at _ and capitals, "a phrase", or and -word work, and a fragment of a word matches nothing	searchTables, searchFunctions, searchEnums

To find part of a name, use a listing's pattern; to find a word anywhere in names, comments or source, use a search.

checkPrivileges reports which of them the current role can actually use on a given connection. Most work for any role that can connect, since the catalog is world-readable: measured on PostgreSQL 18 with every extension present, a bare login role runs 59 of the 73 database operations at full fidelity, the monitoring role 67. What remains for the monitoring role reads row data or plans against it, apart from replication origin progress, which only the superuser can read. Worth calling first against an unfamiliar connection — a privilege-filtered answer is easy to mistake for an empty one, since tableStats on a role without SELECT returns columns with null statistics, exactly like a table that was never analyzed.

evaluateIndex plans a statement as if the indexes were different, using hypopg: create tests a candidate index without building it, hide plans without an existing one — which is how to ask whether an index is safe to drop. Nothing is built, nothing is locked, and the statement is never executed. It reports whether the planner actually used each index, which is the answer a cost figure hides.

Three tools read PgBouncer, the pooler in front of the database, rather than the database behind it. poolerStatus answers whether the pools can keep up — clients waiting for a server, the longest wait, servers in use against each database's pool_size — poolerConnections lists the clients and server connections, and poolerConfig the settings that differ from PgBouncer's defaults. They connect to PgBouncer's admin console, declared as its own section:

[pooler_prod]
kind  = pgbouncer
host  = pooler01
port  = 6432
user  = pglicht_stats   ; in PgBouncer's stats_users
group = poolers

The console refuses transactions, so these send only fixed SHOW commands, and the user should be one from stats_users, which PgBouncer allows to run SHOW and nothing else — pg_licht cannot check which list a user is in, so that second guard is yours to set up. statement_timeout_ms in the section bounds each SHOW, cancelled from this side, and a command an older PgBouncer does not know is named under unavailable while the rest is answered. Database tools refuse a pooler section and pooler tools refuse a database; a group holding both is swept by each kind of tool over its own members. A database tool called with no connection goes to the first section that is not a pooler; a pooler tool, to the only console configured, and with several it must be told which.

A database reached through a console says so with pooler = <section>, since nothing else can: through PgBouncer the server's address is the pooler's own hop. verifyTopology then checks it against the console's routing and names the direct connection that is the same database; the pooler tools accept the database's name and answer about its pool; and a per-database sweep answers that database once, skipping the pooled twin only when the console confirms it is the same database, reached as the same user.

[orders_pooled]
host   = pooler01
port   = 6432
dbname = orders
pooler = pooler_prod

A section may set application_name, which replaces pg_licht's own pg-licht-cpp/<version>. Where several people or assistants share one database role, a different name in each one's file tells them apart in pg_stat_activity and in the server log. It is a name the client chooses, not authentication. Other libpq keys, such as sslmode and connect_timeout, are passed through as written; options is refused.

Editing the connections file (or the budgets.ini in use at startup) needs no restart: the change is picked up at the next request, and SIGHUP forces it. A budgets file created after startup is read at the next start. A file that fails to load leaves the running configuration in place and says so under reload_error in listConnections; the client is sent list_changed notifications, since the tool and resource lists name connections.

A section that does not validate — an unknown kind, a console given an instance — is skipped with a warning instead of stopping the server: the other connections keep working, listConnections and verifyTopology list it under invalid with the reason, and a call naming it gets the reason back. A skipped [default] stays the default, so a call naming no connection gets its reason instead of going to another database. What still stops startup is what is not one section's: an unreadable or group-accessible file, a malformed line, a duplicate header, a name used on two topology axes, an unclaimed [instance:…] section, or a file with no usable section.

Citus. On a Citus worker the shards are ordinary tables that Citus hides from any client whose application_name does not match citus.show_shards_for_app_name_prefixes, so pg_licht sees only the empty distributed shells there, and the size and statistics tools report a worker holding gigabytes as holding nothing. pg_licht cannot set this for itself: it is a superuser setting. On the workers, for the role pg_licht connects as:

ALTER ROLE pglicht_ro SET citus.show_shards_for_app_name_prefixes = 'pg-licht';

Three optional extensions that keep their counters in shared memory, all keyed by the same query_id as statementStats, each get a tool. waitEventProfile reads pg_wait_sampling: what the instance has spent its time waiting on, which a single currentActivity sample cannot say. statementKernelStats reads pg_stat_kcache: the CPU time and the bytes actually read from storage per statement, which is how a page cache hit and a device read stop looking alike. predicateStats and suggestIndexes read pg_qualstats: which predicates filter the most rows, and the extension's own index advisor, whose CREATE INDEX suggestions go straight into evaluateIndex. pg_qualstats records the literal of every predicate it samples; neither tool ever returns one. All three need shared_preload_libraries, and the tools say so rather than answer empty when a library is installed without it.

Tables are covered by three tools rather than one, split by what the answer costs and how fast it goes stale:

	tools	reads
structure — changes only on DDL	tableDetails, listTables, searchTables	catalog
statistics — changes continuously, free to ask	tableStats, listTableStats	catalog and the statistics collector
measured size — changes continuously, costs a lock	tableSize, listTableSizes	stat() per segment, under AccessShareLock

The structure tools return no sample column values and no counters, so the payload an agent reads most often to understand a schema carries neither row data nor anything that moves under it. tableStats carries the pg_stats histograms — including most_common_vals, which is literal values sampled from the column — and a free size_estimate from relpages, dated by estimated_from so its staleness is visible. Reach for tableSize only when that estimate is too stale to act on: it opens each relation with AccessShareLock, so during a rewrite it waits behind the ALTER TABLE.

For operational triage there are wraparoundStatus (XID and multixact headroom, per database and per table, TOAST tables included), checkpointStats (timed against requested checkpoints, backend-written buffers, WAL volume — normalized across the PostgreSQL 17 pg_stat_checkpointer split), progressStats (every running VACUUM, CREATE INDEX, COPY… with a completion percentage), ioStats (pg_stat_io per backend type and context, 16+), tableIOStats (cache hit ratio per table and per index), duplicateIndexes (identical and prefix-redundant indexes, compared by column expression, opclass, collation and sort order), indexBloat (per-index physical statistics, dispatched on the access method — btree leaf density and fragmentation, GIN pending list against the fastupdate settings that bound it, hash bucket and overflow pages), and hostCapacity (memory settings against the host's actual RAM and vCPU count — see below).

bufferCacheSummary and bufferCacheContents read pg_buffercache. The summary needs extension version 1.4, which ships with PostgreSQL 16; on 14 and 15 it says so and points at bufferCacheContents, which reads the view and works everywhere. tableIOStats counts only what shared_buffers served, so a miss there may still have come from the OS page cache at RAM speed; these are the only in-core view of that split. Read the usage-count histogram rather than a hit ratio: mass at 2–5 is a stable working set, everything at 0–1 with no unused buffers is clock-sweep churn, and those are the same ratio with opposite diagnoses. bufferCacheContents aggregates per relation and fork, never raw per-buffer rows.

listTopology and verifyTopology cover the connection topology — see below.

The monitoring tools take optional pid and query_id filters, so a symptom can be followed to its cause rather than read out of a full dump:

statementStats → query_id → currentActivity(query_id) → pid → currentLocks(pid)
                                                            → progressStats(pid)
                                                            → ioStats(pid)
                                                            → explainQuery(query_id)

currentLocks(pid) resolves the transitive blocking chain and tags each row with chain_depth, so the backend at the root of a pile-up is the one with the highest depth.

Each one is documented, with its arguments, in man pg_licht_mcp under OPERATIONS. Every operation also accepts an optional connection argument to select one of several configured databases, and most accept instance, replication_group or group to run across several at once — see below.