{"id":"6482fccb-86ea-449f-b86e-f833e349e4ea","slug":"sql-guard-mcp","kind":"mcp","name":"sql-guard-mcp","summary":"An MCP server that lets an LLM query your database without being able to damage it. Read-only enforcement by real SQL parsing, planner cost limits, PII masking and an audit trail.","owner":"Jagdish1998","repoUrl":"https://github.com/Jagdish1998/sql-guard-mcp","license":"MIT","licenseTier":"permissive","trust":"community","stars":0,"match":null,"fewStars":true,"format":"mcp","createdAt":"2026-09-07T14:51:48.000Z","addedAt":"2026-10-21T06:44:58.360Z","logoUrl":"https://avatars.githubusercontent.com/u/51652308?u=7be8f6e22ad34f1bfcfb2e49c07dc1f0c4d327b4&v=4","linkOnly":false,"collection":null,"skillPath":null,"categories":["databases","cloud-infra","memory-knowledge"],"tags":["ai-agents","database-security","java","llm","postgresql","spring-ai","spring-boot","sql-injection"],"runtime":"binary","local":true,"cloudCapable":false,"cloudCapability":"unknown","cloudReadyOnPlatform":false,"needsApiKey":false,"reviewed":false,"syncedAt":"2026-10-23T03:06:04.140Z","sample":false,"url":"https://conduits.dev/tools/sql-guard-mcp","language":"Java","transports":["stdio"],"install":{},"requirements":{"env":[],"upstreamMayCharge":false},"tools":null,"readme":"# sql-guard-mcp\n\n[![CI](https://github.com/Jagdish1998/sql-guard-mcp/actions/workflows/ci.yml/badge.svg)](https://github.com/Jagdish1998/sql-guard-mcp/actions/workflows/ci.yml)\n[![License: MIT](https://img.shields.io/badge/licence-MIT-blue.svg)](LICENSE)\n[![Java 21](https://img.shields.io/badge/Java-21-orange.svg)](https://openjdk.org/projects/jdk/21/)\n[![Spring Boot 4.1](https://img.shields.io/badge/Spring%20Boot-4.1.1-6DB33F.svg)](https://spring.io/projects/spring-boot)\n\nAn [MCP](https://modelcontextprotocol.io) server that lets a language model query a\nrelational database without being able to damage it.\n\nPoint it at PostgreSQL, add it to your MCP client, and the model can explore the schema and\nrun `SELECT` statements. It cannot write, cannot read database internals, cannot lock rows,\ncannot run a query the planner says is too expensive, and cannot see the contents of columns\nthat hold credentials or personal data.\n\n---\n\n## Why\n\nHanding an LLM a database connection is the obvious way to let it answer questions about your\ndata, and it is also how you end up with `DELETE FROM orders` in production. The usual\nmitigations are weaker than they look:\n\n- **\"I told it to only read.\"** A prompt is a preference, not a permission.\n- **\"I check the SQL starts with SELECT.\"** `WITH gone AS (DELETE FROM orders RETURNING *)\n  SELECT * FROM gone` starts with `WITH`, is a `SELECT` at its top level, and deletes every\n  row.\n- **\"I block keywords.\"** `pg_sl/**/eep(10)` is not `pg_sleep(10)` to a string matcher, and\n  is exactly that to PostgreSQL.\n\nThis server parses the SQL with a real grammar, walks the parse tree for writes hiding inside\nreads, asks the planner what a query will cost before running it, and then executes what\nsurvives inside a read-only transaction on a read-only role. The layers are ordered\ndeliberately: the parser exists to give the model an error it can act on, and the database\npermissions exist to make a write impossible.\n\n---\n\n## Install\n\n### With an MCP client\n\nBuild the jar, then register it. Works with Claude Desktop, Kiro, Cursor, or anything else\nthat speaks MCP over STDIO.\n\n```bash\ngit clone https://github.com/Jagdish1998/sql-guard-mcp.git\ncd sql-guard-mcp\n./mvnw -DskipTests package\n```\n\nAdd to your client's `mcp.json`:\n\n```json\n{\n  \"mcpServers\": {\n    \"sql-guard\": {\n      \"command\": \"java\",\n      \"args\": [\"-jar\", \"/absolute/path/to/sql-guard-mcp/target/sql-guard-mcp-0.1.0-SNAPSHOT.jar\"],\n      \"env\": {\n        \"SQLGUARD_DB_URL\": \"jdbc:postgresql://localhost:5432/your_database\",\n        \"SQLGUARD_DB_USER\": \"sqlguard_readonly\",\n        \"SQLGUARD_DB_PASSWORD\": \"your_password\"\n      }\n    }\n  }\n}\n```\n\nThat is the whole install. STDIO needs no port and no network listener: the client starts the\nserver as a child process and talks to it over pipes.\n\n### Create the database role first\n\nThe server assumes it is connecting as a role that cannot write. Do not skip this and connect\nas a superuser; the application's checks are a usability layer, not the security boundary.\n\n```sql\nCREATE ROLE sqlguard_readonly WITH LOGIN PASSWORD 'change_me';\nGRANT CONNECT ON DATABASE your_database TO sqlguard_readonly;\nGRANT USAGE ON SCHEMA public TO sqlguard_readonly;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO sqlguard_readonly;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO sqlguard_readonly;\nALTER ROLE sqlguard_readonly SET default_transaction_read_only = on;\n```\n\n[`docker/initdb/03-readonly-role.sql`](docker/initdb/03-readonly-role.sql) is a fuller\nversion with comments on what is withheld and why.\n\n---\n\n## Tools\n\n| Tool | What it does |\n| --- | --- |\n| `list_tables` | Every table and view the server is permitted to read. The starting point when the model does not know the schema. |\n| `describe_table` | Columns, types, nullability, primary key. Flags which columns will arrive masked, so the model does not build a query around a value it cannot see. |\n| `explain_query` | Vets a statement against policy and returns the planner's cost estimate, without running it or returning data. |\n| `run_query` | Runs one read-only `SELECT` and returns rows. |\n| `recent_activity` | The audit trail: recent decisions, allowed and refused. |\n\nEvery tool is advertised with `readOnlyHint: true`, so clients that ask for confirmation\nbefore destructive calls know these are safe.\n\n---\n\n## What it looks like\n\nReal responses, captured from [`smoke-stdio.ps1`](smoke-stdio.ps1).\n\nA normal query, capped and reported honestly:\n\n```json\n{\n  \"effectiveSql\": \"SELECT id, status FROM orders ORDER BY id LIMIT 3\",\n  \"columns\": [\"id\", \"status\"],\n  \"rows\": [\n    { \"id\": 1, \"status\": \"SHIPPED\" },\n    { \"id\": 2, \"status\": \"SHIPPED\" },\n    { \"id\": 3, \"status\": \"PENDING\" }\n  ],\n  \"rowCount\": 3,\n  \"truncated\": true,\n  \"appliedLimit\": 3,\n  \"redactedColumns\": [],\n  \"elapsedMillis\": 49\n}\n```\n\n`truncated` and `appliedLimit` are there so the model never mistakes a clipped result for a\ncomplete one and tell you a customer has three orders when they have thirty.\n\nSensitive columns come back masked, and the masking is declared:\n\n```json\n{\n  \"effectiveSql\": \"SELECT name, email FROM customers ORDER BY id LIMIT 3\",\n  \"rows\": [\n    { \"name\": \"Alice Kumar\", \"email\": \"a***@example.com\" },\n    { \"name\": \"Bob Mensah\",  \"email\": \"b***@example.com\" },\n    { \"name\": \"Carol Silva\", \"email\": \"c***@example.com\" }\n  ],\n  \"redactedColumns\": [\"email\"]\n}\n```\n\nThe domain survives so grouping by it still works. A refusal explains the rule rather than\njust saying no:\n\n```\nQuery refused [NOT_READ_ONLY]. Only SELECT statements are allowed. This server cannot\nrun INSERT, UPDATE, DELETE, DDL, or any other write. Detail: statement type was Delete\n```\n\nThat is deliberate. A bare \"denied\" invites the model to retry the same query in a slightly\ndifferent shape; a stated rule lets it correct course on the next turn.\n\n---\n\n## What it refuses\n\n| Attempt | Refusal |\n| --- | --- |\n| `DELETE FROM orders` | `NOT_READ_ONLY` |\n| `WITH gone AS (DELETE FROM orders RETURNING *) SELECT * FROM gone` | `DATA_MODIFYING_CTE` |\n| `SELECT 1; DROP TABLE orders` | `MULTIPLE_STATEMENTS` |\n| `SELECT * INTO copy FROM orders` | `SELECT_INTO` |\n| `SELECT * FROM orders FOR UPDATE` | `LOCKING_CLAUSE` |\n| `SELECT pg_sleep(10)` | `BLOCKED_FUNCTION` |\n| `SELECT pg_read_file('/etc/passwd')` | `BLOCKED_FUNCTION` |\n| `SELECT pg_sleep/**/(10)` | `BLOCKED_FUNCTION` |\n| `SELECT * FROM information_schema.tables` | `SYSTEM_CATALOG` |\n| `SELECT * FROM private.secrets` | `SCHEMA_NOT_ALLOWED` |\n| `SELECT * FROM employee_salaries` | `BLOCKED_TABLE` |\n| a plan the planner prices above budget | `COST_LIMIT_EXCEEDED` |\n| `GRANT ALL ON orders TO PUBLIC` | `PARSE_FAILED` |\n\nThat last row is the important one. JSqlParser has no grammar for `GRANT`, so it does not\nparse, so it is refused. Anything the parser cannot read is denied by default, which means a\ngap in the grammar can never become a way in.\n\n---\n\n## How it works\n\n```\nrun_query(sql)\n      |\n      v\n  ReadOnlySqlValidator ......... parse with a real grammar; reject non-SELECT,\n      |                          statement stacking, writing CTEs, SELECT INTO,\n      |                          locking clauses, blocked functions, system\n      |                          catalogs, blocked tables, disallowed schemas\n      v\n  RowLimitRewriter ............. rewrite the parse tree so the query cannot\n      |                          return more than max-rows\n      v\n  SqlDialect.estimate() ........ EXPLAIN (FORMAT JSON); refuse if the plan is\n      |                          over budget, before any work is done\n      v\n  QueryExecutor ................ read-only connection, read-only transaction,\n      |                          statement timeout, driver-level row cap,\n      |                          rollback on the way out\n      v\n  PiiRedactor .................. mask sensitive columns by result-set label\n      |\n      v\n  AuditLog ..................... record the decision either way\n```\n\nA few decisions worth explaining:\n\n**Parsing, not pattern matching.** Text checks lose to comments, case, whitespace, and string\nliterals. Once the statement is a parse tree, \"is this a SELECT\" stops being a guess. The\nchecks that do run over text run over the statement *re-serialised from the tree*, so comments\nare already gone and the text being scanned is the text the database will execute. This is why\n`pg_sleep/**/(10)` is caught.\n\n**Cost before execution, not a timeout.** A statement timeout only helps after the expensive\nwork has started. Asking the planner first means an over-budget query costs nothing. Where no\nestimate is available the response says so rather than reporting zero, because zero reads as\n\"free\".\n\n**Clamping, not refusing, on row count.** A model that asks for a whole table usually wants to\nlook at the table. Returning the first 200 rows and saying so moves the conversation forward;\na refusal just costs a turn.\n\n**Masking by result label, not by query text.** The model can write `SELECT * FROM users` or\n`SELECT password AS notes`. Checking the label the driver reports covers both. Checking the\nquery text would be bypassed by an alias.\n\n**The audit log is not in the audited database.** That database is read-only, so the server\ncould not write there anyway, and a trail an attacker can reach through the same tool is not a\ntrail. Entries go to a bounded in-memory ring plus SLF4J, for a real deployment to ship\nsomewhere durable.\n\n---\n\n## Configuration\n\nDefaults are strict. Widening a limit should be a decision, not an oversight.\n\n| Property | Default | Purpose |\n| --- | --- | --- |\n| `sqlguard.max-rows` | `200` | Row ceiling. Over-broad queries are clamped to it. |\n| `sqlguard.query-timeout` | `5s` | Server-side statement timeout. |\n| `sqlguard.max-estimated-rows` | `5000000` | Refuse plans estimating more rows. `-1` disables. |\n| `sqlguard.max-estimated-cost` | `5000000` | Refuse plans costing more. `-1` disables. |\n| `sqlguard.allowed-schemas` | `[public]` | Schema allow-list. Empty defers to the role's grants. |\n| `sqlguard.blocked-tables` | `[]` | Tables to hide even where the role can read them. |\n| `sqlguard.blocked-functions` | see [`SqlGuardProperties`](src/main/java/io/github/jagdish1998/sqlguard/config/SqlGuardProperties.java) | Filesystem, sleep and outbound-connection helpers. |\n| `sqlguard.redaction.enabled` | `true` | Column masking on or off. |\n| `sqlguard.redaction.rules` | see source | Ordered regex-to-strategy rules; first match wins. |\n\nMasking strategies are `FULL`, `LAST_FOUR`, `EMAIL` and `HASH`. `HASH` is a stable SHA-256\npseudonym, so counting and grouping still work.\n\nEnvironment variables: `SQLGUARD_DB_URL`, `SQLGUARD_DB_USER`, `SQLGUARD_DB_PASSWORD`,\n`SQLGUARD_DB_POOL_SIZE`, `SQLGUARD_LOG_FILE`.\n\n---\n\n## Hosted mode\n\nFor a deployment rather than a local client, run the `http` profile to serve MCP over\nStreamable HTTP.\n\n```bash\ndocker build -t sql-guard-mcp .\ndocker run -p 8080:8080 \\\n  -e SQLGUARD_DB_URL=jdbc:postgresql://host:5432/db \\\n  -e SQLGUARD_DB_USER=sqlguard_readonly \\\n  -e SQLGUARD_DB_PASSWORD=... \\\n  -e SQLGUARD_HTTP_AUTH_TOKEN=\"$(openssl rand -hex 32)\" \\\n  sql-guard-mcp\n```\n\n**The server refuses to start in HTTP mode without a bearer token.** Spring AI's HTTP\ntransports are authentication-agnostic by design: the auto-configuration publishes the\nJSON-RPC endpoint and enforces nothing, so any client that can reach the port could otherwise\nenumerate and call every tool. For a server whose tools read a database, that is the entire\nsecurity boundary, so a missing token is a startup failure rather than a warning in a log\nnobody tails. `sqlguard.http.allow-unauthenticated=true` overrides it for a localhost\ndevelopment run, and is never appropriate for a deployed one.\n\nToken comparison is constant-time. `/actuator/health` stays open so a load balancer can probe\nit.\n\nOne caveat if you deploy this to a scale-to-zero platform: a cold JVM takes tens of seconds to\nanswer the first request. Either keep a minimum instance warm or build a GraalVM native image,\nfor which Spring Boot already has AOT support.\n\n---\n\n## Local development\n\n```bash\ndocker compose up -d          # PostgreSQL with demo data and a read-only role\n\nSQLGUARD_DB_URL=jdbc:postgresql://localhost:5432/sqlguard_demo \\\nSQLGUARD_DB_USER=sqlguard_readonly \\\nSQLGUARD_DB_PASSWORD=readonly_password \\\n./mvnw spring-boot:run\n```\n\nThe demo schema includes a `private` schema and an `employee_salaries` table specifically so\nyou can watch the allow-list and the blocklist exclude them by two independent mechanisms.\n\n### Tests\n\n```bash\n./mvnw verify\n```\n\n134 tests, of which 125 run anywhere with no Docker daemon and no database. Integration tests\nuse H2 in PostgreSQL compatibility mode, and that trade-off is confined to one seam, the\n`SqlDialect` interface. The remaining 9 skip locally and run in CI (see below).\n\nThe claims that need a real engine are covered separately, by the `postgres` job in CI:\nthat `EXPLAIN (FORMAT JSON)` yields usable numbers, and that a read-only transaction refuses a\nwrite **handed straight to the executor with the validator bypassed**. That last test is the\none that earns the layered design its keep. It is guarded by\n`@EnabledIfEnvironmentVariable(SQLGUARD_TEST_PG_URL)`, so it skips on a laptop with no\ndatabase and runs against a Postgres service container in CI.\n\n`smoke-stdio.ps1` drives the real JSON-RPC protocol end to end: initialize, `tools/list`, then\n`tools/call` for both a successful query and a refused one. Booting a Spring context proves\nthe beans wire up; only this proves an `mcp.json` install works.\n\n---\n\n## What this does not do\n\nBeing clear about the edges matters more than the feature list.\n\n- **It does not detect sensitive data by looking at values.** Masking is decided from column\n  names. An email address sitting in a free-text `notes` column is not caught. This is a\n  guardrail against casual exposure of obviously sensitive columns, not a DLP engine.\n- **`SELECT email AS contact` defeats the mask.** Redaction keys on the label the driver\n  reports, and an alias changes it. Masking the underlying column instead would need full\n  column-lineage analysis through arbitrary expressions and joins.\n- **An unqualified table name cannot be schema-checked.** `SELECT * FROM orders` resolves\n  through the connection's `search_path`, which belongs to the database. Only qualified names\n  are checked against the allow-list; the role's grants are what constrain the rest.\n- **The function blocklist is a blocklist.** It covers the known escape hatches, and a\n  blocklist is never complete. The read-only role is what holds.\n- **Cost estimates are estimates.** A planner working from stale statistics can be wrong in\n  both directions.\n- **Full WCAG-style assurance is not claimed anywhere** because there is no UI; this is a\n  protocol server.\n- **Only PostgreSQL has a dedicated dialect.** Anything else runs on `GenericDialect`, which\n  reports no cost estimate rather than inventing one. Row caps, timeouts and read-only\n  execution still apply.\n\n---\n\n## Built with\n\nJava 21, Spring Boot 4.1.1, [Spring AI 2.0.1](https://docs.spring.io/spring-ai/reference/api/mcp/mcp-overview.html)\nMCP server starter, [JSqlParser 5.3](https://github.com/JSQLParser/JSqlParser), PostgreSQL,\nH2 for tests.\n\n## Licence\n\n[MIT](LICENSE).\n"}