# SLCG — read-only you can actually trust

A read-only PostgreSQL [MCP](https://modelcontextprotocol.io) server that gives an AI
clean schema context — with a read-only guarantee **enforced by the database
itself**, not by trying to filter the SQL an agent sends.

> The popular way to give an AI database access shipped a "read-only" mode that
> a single `COMMIT; DROP SCHEMA` can break out of. This is the version where the
> guarantee holds — and there's a test suite that proves the attacks fail.

## The 60-second version

A read-only mode is only real if it survives someone actively trying to leave it.
Enforcing it by inspecting the agent's SQL is the same losing game as classic SQL
injection, now with an LLM as the untrusted input — anything the agent *reads* (a
row, a ticket, a web page) can carry the payload. So SLCG doesn't filter SQL. It
connects as a PostgreSQL role that was **never granted** the ability to write.

The attack that breaks the naive approach, run against this one:

```text
# The documented breakout:  BEGIN READ ONLY; SELECT ...; COMMIT; DROP TABLE ...
#
# Against a privileged connection (the vulnerable pattern):
DROP TABLE            <- table destroyed

# Against the slcg_readonly role (this project):
ERROR:  cannot execute DROP TABLE in a read-only transaction

# ...and even if the attacker turns the read-only flag OFF first:
SET default_transaction_read_only = off;
INSERT INTO customers ...
ERROR:  permission denied for table customers   <- the privilege layer, not a flag
```

That last line is the whole point: there is nothing to talk your way around,
because the role has no write privilege to begin with.

## What it does / what it doesn't

| Does | Doesn't |
| --- | --- |
| Introspects schema: tables, columns, PKs, FKs, indexes | Offer a `run_sql` / arbitrary-query tool |
| Enforces read-only via a least-privilege DB role | RBAC, SSO, control plane |
| Ships an adversarial test suite + threat model | Multi-DB, vector search, dashboards |
| Speaks MCP (Claude Desktop, Cursor, …) | Write access "with safeguards" |

## Quickstart

**1. Create the least-privilege role** (this file *is* the security model — read it):

```bash
psql -v db=mydb -f sql/01_setup_readonly_role.sql -d mydb
```

**2. Run the server** as that role:

```bash
pip install -e .
PGDATABASE=mydb PGUSER=slcg_readonly slcg-server
```

**3. Point an MCP client at it.** Example Claude Desktop config:

```json
{
  "mcpServers": {
    "slcg": {
      "command": "slcg-server",
      "env": { "PGDATABASE": "mydb", "PGUSER": "slcg_readonly" }
    }
  }
}
```

## Prove it yourself

```bash
pip install -e ".[dev]"
pytest            # 10 adversarial attacks blocked, 6 introspection checks
```

The attack cases read like a spec: `test_commit_breakout_cannot_drop_table`,
`test_disabling_readonly_still_cannot_write`, `test_copy_to_program_exfil_is_denied`,
`test_injected_row_payload_cannot_trigger_write`.

## Why the keys come from `pg_catalog`, not `information_schema`

A detail worth knowing: `information_schema`'s constraint views are
privilege-filtered — a low-privilege read-only role literally cannot see primary
and foreign keys through them. SLCG reads keys from `pg_catalog`, which is
world-readable, so introspection stays complete *for exactly the least-privilege
role the design insists on.*

## Design decisions

See [`docs/THREAT_MODEL.md`](docs/THREAT_MODEL.md) for the threat model mapped to
the OWASP LLM / MCP risk categories, and the `sql/01_setup_readonly_role.sql`
comments for the enforcement rationale.

## Status

Working and tested: the read-only server, the least-privilege role, and a
10-case adversarial test suite all pass. Planned polish — a recorded terminal
demo (asciinema → gif) at the top of this README, and a linked writeup of the
transaction-breakout bypass class this design defends against.

## License

MIT.