lime-ref-postgres-mcp

English · Русский

An open-source showcase of LIME agent identity on a real resource: PostgreSQL behind MCP.

Agents do not share a generic database password. They arrive with a LIME passport (Authorization: Bearer), get checked against a local whitelist and capabilities, then use MCP tools against Postgres. After an authorized agent is recognized, the core emits one audit event — who called what, and how it ended — without copying the tool response body.

This repository is a reference implementation, not a production service operated by LIME. Fork it, study the pattern, run it against your own Postgres, and plug your own audit consumers if you need them.

---

Why this exists

Without named agent identity	With LIME on this door
One shared POSTGRES_URL / API key for every bot	Each agent is a person (agent_id from the passport)
Logs show “someone queried the DB”	Logs/events can say which agent did what
Hard to attach corporate audit / SIEM	EventBus is an extension point — subscribe your own sink
MCP demos often skip real auth	Same LIME passport model as other LIME-protected resources

Primary goal of this package: make LIME technology tangible — passport → allowlist → action → event — on a concrete door (Postgres over MCP), so developers can see how agent identity works end-to-end.

Related LIME pieces:

• Platform & docs: lime.pics
• Agent client (issue MCP JWT with domain): lime-agents-sdk
• Resource-server verify (JWKS / RS256): lime-mcp-server-sdk (this server wraps it)

---

What it is / is not

Is	Is not
Open showcase of LIME agent passport on MCP → Postgres	LIME’s production product or hosted SaaS
Deny-by-default whitelist + capabilities	“One API key opens the whole DB”
Shipped ConsoleSink (demo of the event system)	Shipped webhook / SIEM exporters
Process observability (JSONL + metrics, ADR-003)	Mixing agent audit cards into process logs
Extractable package under this folder	Coupled to the rest of a monorepo runtime

Agent Bearer ≠ POSTGRES_URL.
The Bearer is the agent’s LIME passport. POSTGRES_URL is the MCP service database role — service credentials, not agent identity.

---

How a call works

Agent (LIME passport)  -- Bearer + tools/call -->  MCP /mcp
         │
         ▼
   1. Verify JWT (JWKS from lime.pics, domain + aud pin)
         │ fail → error to agent, NO agent-action event
         ▼
   2. Whitelist (config/agents.json)
         │ unknown agent → error, NO agent-action event
         ▼
   3. Capabilities + SQL class guard
         │ denied → reply + agent-action event (denied)
         ▼
   4. Postgres (asyncpg)
         ▼
   5. Agent-action event (ok | error) → reply to agent
         │
         └── EventBus subscribers (ConsoleSink demo / your sink)

Process logs (lime.mcp.process_log.v1) always can record preauth failures and call lifecycle; agent-action events exist only after a allowlisted agent is established. See ADR-003.

---

Features

• LIME passport gate — Authorization: Bearer on every tools/call; verify via lime-mcp-server-sdk
• Policy — JSON whitelist, permissions READ_SCHEMA / READ_DATA / WRITE_DATA / DDL / ADMIN, max_rows, lazy reload by mtime
• SQL defense — pglast AST → statement-class guard (readonly vs write vs DDL)
• Eight MCP tools — schema / data / write / admin surface only on /mcp
• Event system — AgentActionEvent without response payload; bus.subscribe(...) for custom sinks
• ConsoleSink — optional JSONL cards on stdout (event-system demo)
• Process observability — structured logs + in-process metrics + request_id correlation
• Quality gate — package-local prime_check + CI workflow

---

Quick start

Requirements: Python ≥ 3.12, uv. Docker only for integration tests.

cd Marketing/lime-postgres-mcp   # or clone this package as its own repo
uv sync --all-extras
cp .env.example .env            # fill LIME_* and POSTGRES_URL
# create config/agents.json from config/agents.example.json
# map real LIME agent_id (passport sub) → permissions

uv run python -m lime_ref_postgres_mcp serve
# → http://127.0.0.1:8000/mcp

From an agent worker, use lime-agents-sdk against that URL (OAuth mints a JWT with {"domain": "<your pin>"} matching LIME_EXPECTED_DOMAIN):

from lime_agents import LimeAgent

async with LimeAgent(agent_token="...") as agent:
    tools = await agent.list_tools("http://127.0.0.1:8000/mcp")
    result = await agent.call_tool(
        "http://127.0.0.1:8000/mcp",
        "list_schemas",
        {},
    )

Composition check (no HTTP):

uv run python -m lime_ref_postgres_mcp

---

MCP tools

Tool	Capability
list_schemas	READ_SCHEMA
list_tables	READ_SCHEMA
get_table_schema	READ_SCHEMA
select_rows	READ_DATA
execute_readonly_query	READ_DATA
explain_query_plan	READ_DATA
execute_write_query	WRITE_DATA
get_database_stats	ADMIN

Passport goes in the HTTP header only — never in tool arguments.

---

Agent event system (for integrators)

The core emits immutable AgentActionEvent values after an allowlisted agent is in context. It does not ship webhooks or SIEM connectors — by design. You attach consumers yourself.

What you get on each event

• agent_id, status (ok | denied | error)
• request — tool name + args (no result rows / no agent reply body)
• outcome — reason codes, missing capabilities, statement class, row counts
• meta — e.g. request_id, domain
• duration_ms, event_id, ts

Privacy rule: full SELECT payloads must not leave through audit. Events are cards, not response mirrors.

When there is no event

Missing / invalid passport, JWKS failure, or agent not on the whitelist → agent gets an error; no AgentActionEvent (there was no authorized actor). Process logs still record preauth.fail.

Shipped demo: ConsoleSink

With ENABLE_CONSOLE_EVENT_SINK=1 (default), bootstrap registers ConsoleSink — one JSON line per event on stdout. Turn it off if you only want your own subscribers.

Add your own sink (logging elsewhere)

Any async callable that accepts AgentActionEvent works. Subscribe at composition time (after build_scaffold_runtime / on runtime.event_bus):

from lime_ref_postgres_mcp.bootstrap.container import build_scaffold_runtime
from lime_ref_postgres_mcp.domain.auditing.agent_action_event import AgentActionEvent

async def forward_to_my_logger(event: AgentActionEvent) -> None:
    # Examples: write to your DB, push to a queue, call an internal API.
    # Do not put agent response bodies here — they are not on the event.
    await my_audit_store.write(
        agent_id=str(event.agent_id),
        tool=event.request.get("tool"),
        status=event.status,
        reason=(event.outcome.reason_code if event.outcome else None),
        request_id=(event.meta or {}).get("request_id"),
    )

runtime = build_scaffold_runtime()
runtime.event_bus.subscribe(forward_to_my_logger)
# then serve ASGI from this runtime (same pattern as `serve`)

Rules of the road

1. Sink is one-way: read the event; do not call back into invoke/authorize.
2. Sink errors are swallowed by the bus — they must not change the tool Result returned to the agent.
3. Prefer idempotent, fast handlers; offload heavy work to a queue inside your sink.
4. Process observability (bootstrap.observability) is a different channel from agent events — don’t overload one with the other.

ConsoleSink source: subscribers/console_sink.py.
Port: application/ports/events.py.

---

Configuration

Copy .env.example. Important variables:

Variable	Required	Role
LIME_EXPECTED_DOMAIN	yes	Hostname pin on the MCP JWT (domain claim)
LIME_JWKS_URL	yes	JWKS (default lime.pics well-known)
LIME_EXPECTED_AUD	no	default mcp
POSTGRES_URL	yes	Service DB URL (lazy pool)
AGENTS_POLICY_PATH	no	default ./config/agents.json
POLICY_RELOAD_TTL_SECONDS	no	default 60
ENABLE_CONSOLE_EVENT_SINK	no	default on — demo agent-action JSONL
LIME_MCP_LOG_* / LIME_MCP_METRICS	no	process observability (ADR-003)
LIME_MCP_BIND_HOST / LIME_MCP_PORT	no	serve bind (default 127.0.0.1:8000)

Policy shape: config/agents.example.json.

There is no WEBHOOK_URL and no WebhookSink in this package.

---

Verify / quality

uv run ruff check src tests
uv run mypy src
uv run pytest
uv run pytest tests/integration -m integration --no-cov -o addopts=

uv run python -m scripts.prime_check
uv run python -m scripts.prime_check --list

Nested CI: .github/workflows/prime_check.yml (extract-to-own-repo ready).

---

Documentation map

Doc	Content
PRD.md	Product intent, access model, event rules
TDD.md	Architecture, layers, module map
ADR-001	Auth boundary on MCP
ADR-003	Process logs + metrics + correlation
docs/verification/	Phase reports (Day0 → P6)

---

Layout

src/lime_ref_postgres_mcp/
  domain/           # policy, SQL guard, AgentActionEvent (pure)
  application/      # invoke / authorize / emit ports
  infrastructure/   # JWKS verify, JSON policy, asyncpg, EventBus
  presentation/mcp/ # Streamable HTTP /mcp
  subscribers/      # ConsoleSink (demo)
  bootstrap/        # settings, container, process observability
config/             # agents.example.json
scripts/prime_check # package quality gate
tests/

---

Status

Showcase sealed through P6 (CI green + SBOM). Intended as public reference code for LIME agent identity on MCP → Postgres.

Maintainers do not operate this as LIME production, and do not ship outbound webhook sinks.

---

License

See package metadata in pyproject.toml.