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.
uv add 'nimblebrain-server-sdk[db]'The generic core is pgbulkhead, pinned exactly; nimblebrain_server_sdk.db is NimbleBrain’s configuration of it.
The model
Section titled “The model”- 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
UPDATEorDELETEselects. - Identity comes from verified input only. Behind NimbleBrain’s edge, that is the
x-tenant-idandx-workspace-idheaders it injects. It never comes from a tool argument or a request body.
The server
Section titled “The server”"""A server that keeps its notes in Postgres, isolated per tenant and workspace by RLS."""
from collections.abc import AsyncIteratorfrom contextlib import asynccontextmanager
import sqlalchemy as safrom fastmcp import FastMCPfrom fastmcp.server.dependencies import get_http_headers
from nimblebrain_server_sdk import ADDS, READS, add_error_contractfrom 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),)
@asynccontextmanagerasync 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
SecuredDatabaseper 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 andawait 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 bindsctxfor the policies. The binding is transaction-local, so a pooled connection cannot carry one request’s identity into the next.ctx.identity_columns()is thetenant_idandworkspace_ida row is written with. The policy’sWITH CHECKrejects a mismatch.
Local setup
Section titled “Local setup”-
Start a Postgres
Terminal window docker run -d --name notes-pg -e POSTGRES_PASSWORD=postgres -p 127.0.0.1:5432:5432 postgres:16 -
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/postgresexport NOTES_APP_PASSWORD=notes-local-onlyuv run python db_migrate.py -
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
The migration and the app role
Section titled “The migration and the app role”"""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 asyncioimport os
import sqlalchemy as safrom db_notes import metadatafrom 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
NOSUPERUSERandNOBYPASSRLS. Postgres applies no policy to a superuser, aBYPASSRLSrole, or a member of a managed Postgres admin role (on Amazon RDS,rds_superuser). The policies alsoFORCErow-level security on the table owner. - Grant the role its tables and sequences. A table the role cannot
INSERTinto, 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_engineorSecuredDatabasepassesrefuse_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.
Three policy shapes
Section titled “Three policy shapes”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.
Identity
Section titled “Identity”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.
In a test
Section titled “In a test”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.
"""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 osfrom collections.abc import AsyncIterator
import pytestimport sqlalchemy as safrom db_migrate import APP_ROLE, migratefrom db_notes import db, mcpfrom fastmcp import Clientfrom sqlalchemy.ext.asyncio import create_async_engine
from nimblebrain_server_sdk.db.testing import edge_clientfrom 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.
In a local run
Section titled “In a local run”Serve over HTTP and send both headers from the client:
from fastmcp import Clientfrom 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"})Capacity
Section titled “Capacity”- Per-workspace admission.
db.sessionadmits 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(WorkspaceBusyorPoolExhausted). It ran nothing, so a retry is safe. Withadd_error_contractthe caller receives arate_limitederror; without it, a tool returnsrate_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 toSecuredDatabase.db.check()refuses settings where2 × WORKSPACE_CONCURRENCYreachesDB_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.
The system role
Section titled “The system role”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
systemescape. 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 actorsystem.
What it does not do
Section titled “What it does not do”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.
Related
Section titled “Related”- Notifications: an outbox under the same policies
- Errors
- API reference