Skip to content

Data

nimblebrain-server-sdk[db] is for a server that owns a Postgres database serving many tenants and workspaces. Isolation is enforced by Postgres row-level security, not by WHERE clauses, so a forgotten filter cannot leak.

Terminal window
uv add 'nimblebrain-server-sdk[db]'

The generic core is pgbulkhead, pinned exactly; nimblebrain_server_sdk.db is NimbleBrain’s configuration of it.

  • Tenant is a hard wall, never crossed. Workspace is a soft wall inside a tenant, crossable for reads only by a grant NimbleBrain puts in the verified token.
  • Reads see the active workspace plus any read-granted ones. Writes go to the active workspace only, both for the row written and for the rows an UPDATE or DELETE selects.
  • Identity comes from verified input only. Behind NimbleBrain’s edge, that is the x-tenant-id and x-workspace-id headers it injects. It never comes from a tool argument or a request body.
db_notes.py
"""A server that keeps its notes in Postgres, isolated per tenant and workspace by RLS."""
from collections.abc import AsyncIterator
from contextlib import asynccontextmanager
import sqlalchemy as sa
from fastmcp import FastMCP
from fastmcp.server.dependencies import get_http_headers
from nimblebrain_server_sdk import ADDS, READS, add_error_contract
from nimblebrain_server_sdk.db import SecuredDatabase, security_context_from_edge_headers
# NOTES_DATABASE_URL=postgresql+asyncpg://notes_app:...@host:5432/notes (the app role)
db = SecuredDatabase(url_env="NOTES_DATABASE_URL", prefix="NOTES")
metadata = sa.MetaData()
notes = sa.Table(
"notes",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("tenant_id", sa.Text, nullable=False),
sa.Column("workspace_id", sa.Text, nullable=False),
sa.Column("text", sa.Text, nullable=False),
)
@asynccontextmanager
async def lifespan(_: FastMCP) -> AsyncIterator[None]:
db.check() # the pool settings are valid
await db.check_role() # the role is one row-level security applies to
yield
await db.dispose()
mcp = FastMCP("notes", lifespan=lifespan)
add_error_contract(mcp) # a missing identity is `auth_required`, a full pool `rate_limited`
@mcp.tool(annotations=ADDS)
async def add_note(text: str) -> dict[str, bool]:
"""Add a note to the caller's workspace."""
ctx = security_context_from_edge_headers(get_http_headers()) # AuthError when absent
async with db.session(ctx) as conn: # admitted, then bound to ctx for RLS
await conn.execute(notes.insert().values(**ctx.identity_columns(), text=text))
return {"saved": True}
@mcp.tool(annotations=READS)
async def list_notes() -> list[str]:
"""List the notes the caller's workspace can read."""
ctx = security_context_from_edge_headers(get_http_headers())
async with db.session(ctx) as conn: # no WHERE clause: the policy filters the rows
return list((await conn.execute(sa.select(notes.c.text))).scalars())
  • One SecuredDatabase per process. It reads and validates its settings once and builds the engine on the first session, so importing the server needs no database.
  • The lifespan checks before serving. db.check() validates the pool settings and await db.check_role() connects and refuses a role that bypasses row-level security, so a misconfiguration fails the rollout and not the first call.
  • db.session(ctx) admits the call against its workspace’s concurrency limit, opens a transaction, and binds ctx for the policies. The binding is transaction-local, so a pooled connection cannot carry one request’s identity into the next.
  • ctx.identity_columns() is the tenant_id and workspace_id a row is written with. The policy’s WITH CHECK rejects a mismatch.
  1. Start a Postgres

    Terminal window
    docker run -d --name notes-pg -e POSTGRES_PASSWORD=postgres -p 127.0.0.1:5432:5432 postgres:16
  2. Run the migration as a role that owns the schema

    Terminal window
    export MIGRATION_DATABASE_URL=postgresql+asyncpg://postgres:postgres@127.0.0.1:5432/postgres
    export NOTES_APP_PASSWORD=notes-local-only
    uv run python db_migrate.py
  3. Point the server at the app role

    Terminal window
    export NOTES_DATABASE_URL=postgresql+asyncpg://notes_app:$NOTES_APP_PASSWORD@127.0.0.1:5432/postgres
db_migrate.py
"""Create the notes table, its RLS policy, and the role the server connects as.
Run it as a role that owns the schema (locally, the `postgres` superuser):
MIGRATION_DATABASE_URL=postgresql+asyncpg://postgres@127.0.0.1:5432/postgres \
NOTES_APP_PASSWORD=... python db_migrate.py
"""
import asyncio
import os
import sqlalchemy as sa
from db_notes import metadata
from sqlalchemy.ext.asyncio import create_async_engine
from nimblebrain_server_sdk.db import rls_policy_statements
APP_ROLE = "notes_app"
async def migrate(url: str, app_password: str) -> None:
engine = create_async_engine(url)
async with engine.begin() as conn:
await conn.run_sync(metadata.create_all)
for statement in rls_policy_statements("notes"): # asyncpg runs one statement a call
await conn.execute(sa.text(statement))
# The app role: row-level security does not apply to a superuser or BYPASSRLS role,
# and the server refuses to start as one.
exists = await conn.scalar(
sa.text("SELECT 1 FROM pg_roles WHERE rolname = :role"), {"role": APP_ROLE}
)
verb = "ALTER" if exists else "CREATE"
password = app_password.replace("'", "''")
await conn.execute(
sa.text(f"{verb} ROLE {APP_ROLE} LOGIN NOSUPERUSER NOBYPASSRLS PASSWORD '{password}'")
)
await conn.execute(sa.text(f"GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO {APP_ROLE}"))
await conn.execute(sa.text(f"GRANT USAGE ON SEQUENCE notes_id_seq TO {APP_ROLE}"))
await engine.dispose()
if __name__ == "__main__":
asyncio.run(migrate(os.environ["MIGRATION_DATABASE_URL"], os.environ["NOTES_APP_PASSWORD"]))
  • Loop over rls_policy_statements(table). asyncpg refuses several commands in one execute, so the policy is one statement per item. In an Alembic revision: for statement in rls_policy_statements("notes"): op.execute(statement). rls_policy_sql(table) is the same DDL as one string, for a driver that takes it.
  • The app role is NOSUPERUSER and NOBYPASSRLS. Postgres applies no policy to a superuser, a BYPASSRLS role, or a member of a managed Postgres admin role (on Amazon RDS, rds_superuser). The policies also FORCE row-level security on the table owner.
  • Grant the role its tables and sequences. A table the role cannot INSERT into, or a sequence it cannot use, fails on the first write.
  • The migration’s own engine is meant to bypass, so one built with create_pooled_engine or SecuredDatabase passes refuse_bypass_roles=False. A plain SQLAlchemy engine, as here, has no role check.

The rendered SQL is a schema contract: migrations replay it, so a change to it in a new SDK release comes with a migration note in the changelog.

Each binds identity the same way (set_config(..., is_local => true) inside a transaction, values bound as parameters).

Policy Session Hard wall Use when
rls_policy_statements db.session(ctx) (tenant_id, workspace_id) The default: workspace-scoped data
rls_policy_statements_tenant_scoped db.session(ctx) tenant_id A tenant-wide fact: a suppression list, an audit log
rls_policy_statements_org_scoped org_session(engine, org_id) org_id A service with no NimbleBrain tenant, isolated by an identity provider’s organization

Mixing shapes fails closed: a session bound for one shape matches no row of another.

security_context_from_edge_headers(get_http_headers()) reads x-tenant-id and x-workspace-id and nothing else. A request without either did not come through the edge, and the call raises AuthError instead of defaulting to a permissive scope. A deployment must admit only the edge to the server, so that a caller cannot set those headers itself.

AuthError is the auth_required service error. Its message is safe to show a caller; the missing header is in detail, which is logged and never sent. With add_error_contract(mcp), as above, a tool lets it propagate.

The in-memory fastmcp.Client(mcp) sends no HTTP headers, so every call is refused. edge_client serves the server over HTTP inside the test’s event loop (no port is bound) and sends the two headers the way the edge does.

test_db_notes.py
"""The Postgres notes server under a verified identity, against a real Postgres.
Needs a superuser URL in DB_TEST_URL, as the SDK's own database tests do; skipped without it.
"""
import os
from collections.abc import AsyncIterator
import pytest
import sqlalchemy as sa
from db_migrate import APP_ROLE, migrate
from db_notes import db, mcp
from fastmcp import Client
from sqlalchemy.ext.asyncio import create_async_engine
from nimblebrain_server_sdk.db.testing import edge_client
from nimblebrain_server_sdk.testing import Mode
ADMIN_URL = os.environ.get("DB_TEST_URL")
PASSWORD = "notes-test-only" # noqa: S105
pytestmark = pytest.mark.skipif(ADMIN_URL is None and not os.environ.get("CI"), reason="no DB")
@pytest.fixture(autouse=True)
async def database(monkeypatch: pytest.MonkeyPatch) -> AsyncIterator[None]:
assert ADMIN_URL is not None, "DB_TEST_URL is not set"
admin = create_async_engine(ADMIN_URL)
async with admin.begin() as conn:
await conn.execute(sa.text("DROP TABLE IF EXISTS notes"))
await migrate(ADMIN_URL, PASSWORD)
app_url = sa.make_url(ADMIN_URL).set(username=APP_ROLE, password=PASSWORD)
monkeypatch.setenv("NOTES_DATABASE_URL", app_url.render_as_string(hide_password=False))
yield
await db.dispose()
async with admin.begin() as conn:
await conn.execute(sa.text("DROP TABLE IF EXISTS notes"))
await admin.dispose()
@pytest.mark.parametrize("mode", ["auto", "legacy"])
async def test_a_workspace_reads_only_its_own_notes(mode: Mode) -> None:
async with edge_client(mcp, tenant_id="t1", workspace_id="ws_a", mode=mode) as ws_a:
await ws_a.call_tool("add_note", {"text": "from a"})
assert (await ws_a.call_tool("list_notes", {})).data == ["from a"]
async with edge_client(mcp, tenant_id="t1", workspace_id="ws_b", mode=mode) as ws_b:
assert (await ws_b.call_tool("list_notes", {})).data == []
async with edge_client(mcp, tenant_id="t2", workspace_id="ws_a", mode=mode) as other_tenant:
assert (await other_tenant.call_tool("list_notes", {})).data == []
async def test_a_call_with_no_identity_is_auth_required() -> None:
async with Client(mcp) as client: # the in-memory client sends no identity headers
result = await client.call_tool("list_notes", {}, raise_on_error=False)
assert result.is_error
assert result.structured_content is not None
assert result.structured_content["error"]["code"] == "auth_required"

edge_client changes nothing in the server. Identity still arrives in the two headers and nowhere else, so it opens no way for a tool argument or a request body to carry one. The test stands where the edge stands in production.

Serve over HTTP and send both headers from the client:

from fastmcp import Client
from fastmcp.client.transports import StreamableHttpTransport
transport = StreamableHttpTransport(
"http://127.0.0.1:8000/mcp",
headers={"x-tenant-id": "t1", "x-workspace-id": "ws_a"},
)
async with Client(transport) as client:
await client.call_tool("add_note", {"text": "hello"})
  • Per-workspace admission. db.session admits at most <PREFIX>_WORKSPACE_CONCURRENCY (2) calls per workspace before it checks a connection out, so one workspace’s burst queues behind its own slots.
  • A call refused for capacity raises Overloaded (WorkspaceBusy or PoolExhausted). It ran nothing, so a retry is safe. With add_error_contract the caller receives a rate_limited error; without it, a tool returns rate_limited_tool_error(exc).
  • Pool settings come from <PREFIX>_DB_POOL_SIZE (5), <PREFIX>_DB_POOL_MAX_OVERFLOW (5), <PREFIX>_DB_POOL_TIMEOUT_SECONDS (10) and <PREFIX>_WORKSPACE_WAIT_SECONDS (15), where the prefix is the one given to SecuredDatabase. db.check() refuses settings where 2 × WORKSPACE_CONCURRENCY reaches DB_POOL_SIZE + DB_POOL_MAX_OVERFLOW.
  • A cancelled call returns its connection. Each session commits or rolls back and returns the connection in a shielded scope, each step bounded by CLEANUP_TIMEOUT_SECONDS (10 seconds).
  • In a test, nimblebrain_server_sdk.db.testing.use_limiter(db, limiter) swaps in a small limiter for the block.

A reconciler, a sweep or an admin job has no caller, so it has no identity to bind. db.system_session() (or system_session(engine)) opens a transaction under the system role, which the tenant+workspace policy lets past its workspace predicate.

  • It does not pass the tenant wall. The tenant predicate still compares against the bound tenant, which this session leaves unset, so the job binds a tenant for the rows it touches or sees none.
  • The tenant-scoped and org-scoped policies carry no system escape. A job that crosses tenants or organizations there opens one scoped session per tenant or organization.
  • It is a session setting, not a database role. The connection is still the app role.
  • Never on a request path. There, db.session(ctx) with the caller’s identity is the only correct session. Record each use in the server’s audit trail as the actor system.

It does not hold against arbitrary SQL running as the app role, which can set any app.* setting. Do not run SQL a caller supplies on these connections.