dev-mcps/postgres-mcp

postgres-mcp

SQL access to PostgreSQL for a model, over MCP (streamable HTTP). Two modes, chosen by the catalog entry that opens the session and never by the model.

Tools

Tool
postgres_query	A SELECT in a read-only transaction. At most maxRows rows come back (default 100, never more than the server’s limit), with their column names; truncated says whether there were more.
postgres_execute	Anything that changes things - INSERT, UPDATE, DELETE, CREATE TABLE. Returns the command, the rows touched and the rows of a RETURNING. Without parameters it may hold several statements, which run as one transaction.
postgres_list_tables	The tables and views of a schema.
postgres_describe_table	The columns of one table, and its primary key.

Values are passed as params for $1, $2, …; they are bound, never spliced into the text. An object or an array is sent as JSON text, which a jsonb column takes.

Every statement is bounded: by the server’s own statement_timeout, by the number of rows and by the bytes of the answer.

Internal mode: a database for each execution

The session is opened with x-postgres-scope set to the execution’s id (the catalog entry postgres-mcp-internal does it). The server then makes, if they are not there:

• a user <prefix><execution id> - NOSUPERUSER NOCREATEDB NOCREATEROLE, a handful of connections at most;
• a database of the same name, owned by it, that PUBLIC cannot connect to;

and connects as that user, so what the model runs can touch nothing but its own database: it cannot switch to another role, make users or databases, read the server’s files or reach another execution’s database. That is checked against a real PostgreSQL, as a catalog user who is not a superuser, in test/integration.test.js.

The catalog entry sends, as pinned headers, the platform PostgreSQL’s address and the credentials of the user that makes databases and users (CREATEDB and CREATEROLE, nothing else):

Header
x-postgres-scope	the scope, normally ${{context.rootExecutionId}}; required
x-postgres-host, x-postgres-port	where the platform’s PostgreSQL is (port 5432 when left out)
x-postgres-database	the maintenance database that user connects to, to make the others
x-postgres-user, x-postgres-password	the user that makes databases and users
x-postgres-sslmode	require (the default), verify-ca, verify-full or disable
x-postgres-role-secret	at least 32 characters: the password of each execution’s user is derived from it

The password of an execution’s user is HMAC-SHA256(secret, "humainflow-exec-database:" + name) as hex, set every time a session opens. The workflow manager’s PostgreSQL storage nodes use the same convention (PostgresExecutionDatabase) and the same secret, from the executionRoleSecret of their own catalog entry, so a storage node and a model working on one execution see the same tables. Whoever opens the database first makes it; the other finds it there. Both test suites assert the same known name and password.

PostgreSQL has no expiry of its own. The databases are dropped, with their users, when they are older than the catalog entry’s executionDatabaseExpireDays (7 by default), by the workflow manager’s janitor. This server writes the time it made a database in the database’s comment, which is what the janitor reads.

Configuration, in the environment:

• POSTGRES_MCP_INTERNAL_DATABASE_PREFIX (default exec_). Not optional: it cannot be set empty, and it is up to 26 lowercase letters, digits or _, starting with a letter. It keeps a holder of the API key inside the databases this server made.
• POSTGRES_MCP_ALLOWED_HOSTS: when set, the platform’s host has to be on it. It is not held to the public-address rule of the external mode: the platform’s database is the operator’s, and may well be on a private network.

External mode

The host, the port and the database are the flow author’s: the catalog entry sends them as x-postgres-host, x-postgres-port and x-postgres-database, filled from the node’s own configuration, and they are pinned for the session. The model calls postgres_open_session with only the user and password it was given - it cannot choose where to connect nor which database - then names the returned postgresSessionId in every call. An empty port means 5432. The database has to exist: an external session never makes a database or a user, only an internal session makes those of its execution.

Without POSTGRES_MCP_ALLOWED_HOSTS an external session reaches public addresses only: a host written as a loopback, private, link-local or metadata address is refused, and a name is resolved and refused if any of its addresses is not public; the connection is then made to the address that was checked, with the certificate still verified against the name. With the list set, exactly the hosts on it are reachable, private ones included, and nothing else.

Environment

• POSTGRES_MCP_API_KEYS (required): comma-separated, optionally subject=token, each token at least 32 characters.
• POSTGRES_MCP_ALLOWED_HOSTS, POSTGRES_MCP_INTERNAL_DATABASE_PREFIX: see above.
• POSTGRES_MCP_MAX_ROWS (default 1000, at most 10000), POSTGRES_MCP_MAX_RESULT_BYTES (1 MiB, at most 16 MiB), POSTGRES_MCP_STATEMENT_TIMEOUT_MS (30000), POSTGRES_MCP_MAX_CONNECTIONS_PER_SESSION (4, external mode).
• POSTGRES_MCP_REQUEST_MAX_BYTES, POSTGRES_MCP_MAX_SESSIONS, POSTGRES_MCP_SESSION_TTL_SECONDS.
• POSTGRES_MCP_HOST (default 0.0.0.0), POSTGRES_MCP_PORT (default 3000) and POSTGRES_MCP_CORS_ORIGINS.

Tests

npm test. The integration tests start a throwaway postgres:17-alpine with Docker and are skipped without it (or with POSTGRES_MCP_SKIP_DOCKER=1).