Postgres MCP Server: Give an Agent Read-Only Database Access

A Postgres MCP server should answer one question: what do these rows say. It should not be able to change them. Give the agent a read-only database role that can only SELECT, and make the tool reject anything that is not a single SELECT or WITH before the SQL reaches Postgres. The keyword filter is not enough on its own. It can be bypassed, for example with dollar-quoting, so the read-only role has to be there as well.

By Tayyab Javed, AI agent developer

What is a Postgres MCP server?

It is an MCP server with a tool that runs SQL and returns rows as text. The model never holds the database password. The process does. That split only helps if the process itself cannot write. The rest of this cluster is on the MCP servers hub. A server that writes needs the bearer-token gate in MCP server authentication, not a wider database role.

How do you keep the query read-only?

Two gates, and you need both. The role is the one that counts: also use a read-only database role, because the keyword filter can be bypassed (dollar-quoting is one way, spelled out below). The Python check only stops an obvious write from reaching the database. I created agent_reader, granted CONNECT on the database, USAGE on public, and SELECT on orders, then set default_transaction_read_only on the role. After I turned that session setting off, INSERT still failed with permission denied for table orders.

pip install "mcp[cli]==2.3.0" "psycopg[binary]==3.3.6"

import os
import re

import psycopg
from mcp.server import MCPServer
from mcp.server.mcpserver.exceptions import ToolError

mcp = MCPServer("postgres-readonly")
DATABASE_URL = os.environ["DATABASE_URL"]

# Keyword checks can be bypassed, for example with dollar-quoting.
# DATABASE_URL has to be a read-only role or a bypassed check can write.
WRITE = re.compile(
    r"\b(insert|update|delete|drop|alter|create|grant|revoke|truncate|copy|call|do|merge|vacuum|listen|notify)\b",
    re.IGNORECASE,
)

def read_only_sql(sql: str) -> str:
    text = sql.strip()
    if text.endswith(";"):
        text = text[:-1].rstrip()
    if ";" in text:
        raise ToolError("Send one statement. A second statement is rejected.")
    if not re.match(r"(?is)\s*(select|with)\b", text):
        raise ToolError("Only SELECT or WITH is allowed.")
    stripped = re.sub(r"'(?:''|[^'])*'", "''", text)
    if WRITE.search(stripped):
        raise ToolError("That statement is not read-only.")
    return text

@mcp.tool()
def query(sql: str) -> str:
    """Run one read-only SELECT against the agent database and return the rows."""
    statement = read_only_sql(sql)
    try:
        with psycopg.connect(DATABASE_URL) as conn:
            conn.execute("SET statement_timeout = '5s'")
            with conn.cursor() as cur:
                cur.execute(statement)
                if cur.description is None:
                    raise ToolError("The statement returned no rows.")
                cols = [d.name for d in cur.description]
                rows = cur.fetchall()
    except psycopg.Error as exc:
        message = exc.diag.message_primary or "The database rejected that statement."
        raise ToolError(message) from exc
    lines = ["\t".join(cols)]
    for row in rows:
        lines.append("\t".join("" if value is None else str(value) for value in row))
    return "\n".join(lines)

if __name__ == "__main__":
    mcp.run()

Python 3.11 or newer. Put the URL in DATABASE_URL. On the call I made, SELECT id, status FROM orders ORDER BY id returned the header and two rows, 1 paid and 2 open. A filter comparing status to the word insert, inside quotes, still returned those rows. A bare INSERT came back as Only SELECT or WITH is allowed. Two statements separated by a semicolon were rejected. A WITH that hid an INSERT was rejected as not read-only. SELECT * FROM secret_table returned relation "secret_table" does not exist. SELECT pg_sleep(6) returned canceling statement due to statement timeout.

What does the database role still have to block?

Also use a read-only database role. The keyword filter can be bypassed, and dollar-quoting is one way. The check strips ordinary single quotes, then looks for words such as DELETE. A ' that sits inside $tag$...$tag$ still looks like the start of a string to that regex, so a later DELETE can disappear from the check while Postgres still runs it. A WITH whose main statement is that DELETE has no second semicolon, so the one-statement check does not catch it either. I did not install dblink. Do not grant this role superuser, and do not install file-reading extensions into the database it can see. If the filter misses, permission denied from the read-only role is the backstop. The failure modes for a tool that writes, once you actually need one, are in the MCP production guide.

In practice

Frequently Asked Questions