Skip to content

db

nimblebrain_server_sdk.db · install nimblebrain-server-sdk[db] · version 0.7.2

from nimblebrain_server_sdk.db import AdmittedSlot, AuthError, CLEANUP_TIMEOUT_SECONDS

Tenant + workspace data access for a server that owns a Postgres database.

Needs the [db] extra (uv add 'nimblebrain-server-sdk[db]'); imported without it, this package raises an ImportError that names the extra.

The pattern, in three pieces:

  • SecurityContext — the verified request identity, built ONLY from a verified token’s claims (never a request body), or for a server behind the edge from the verified headers the edge injects (security_context_from_edge_headers). Tenant is the hard wall; workspace is the soft wall inside a tenant.
  • secured_session — opens a transaction with that context bound as Postgres GUCs (SET LOCAL semantics), so every query runs under RLS scoped to (tenant, workspace, allowed_workspaces). Transaction-scoped, so a pooled connection cannot leak context to the next request. Its exit is shielded from cancellation, so a cancelled call returns its connection.
  • rls_policy_statements — the standard RLS policy a service applies to every table tagged (tenant_id, workspace_id), one statement per item for a migration to execute in turn (rls_policy_sql is the same DDL as one string).

And the capacity around them, for a server that serves many workspaces from one pool:

  • SecuredDatabase — the one object a server declares for its database: settings read and validated once (check() in the lifespan), a lazily built pooled engine, the workspace limiter, admitted session(ctx) and admitted(ctx) for several sessions under one slot.
  • PoolSettings / create_pooled_engine — the pool’s size and timeout, read from the server’s env namespace and validated together with the admission limit at boot. The engine refuses a connection whose session bypasses row-level security (a superuser or BYPASSRLS role, a member of a managed Postgres admin role in MANAGED_ADMIN_ROLES, or any session a probe sees bypass a forced policy, when the session can run the probe) with UnsafeRoleError unless built with refuse_bypass_roles=False (an engine meant to bypass, such as a migration’s).
  • WorkspaceLimiter — at most N calls per workspace at once, admitted before a call checks out a connection; a call refused for capacity raises Overloaded, which a server returns as rate_limited.

The generic core is the public pgbulkhead package; this package is NimbleBrain’s configuration of it (presets), its edge identity (edge), and the names servers import.

def create_pooled_engine(url: str, settings: PoolSettings, *, refuse_bypass_roles: bool = True, **options: Any) -> AsyncEngine

An async engine whose pool is sized by settings, with pool_pre_ping on.

With refuse_bypass_roles (the default) the engine refuses every connection whose session bypasses row-level security: a superuser, a BYPASSRLS role, a member of a managed Postgres admin role, or any session a probe sees bypass a forced policy (see install_role_guard). Turn it off only for an engine that is meant to bypass, such as one that runs migrations. options pass through to create_async_engine and may override the pool keywords.

async def org_session(engine: AsyncEngine, org_id: str) -> AsyncGenerator[AsyncConnection]

A transaction scoped to one identity-provider organization by RLS.

The single-key counterpart to secured_session, for a service whose hard wall is an org_id rather than the platform’s tenant_id — see rls_policy_sql_org_scoped for why those are different things and must not be read as peers.

The discipline is identical and is the whole reason this lives here rather than in the adopting service: set_config(..., is_local => true) inside a transaction, so the binding dies on commit/rollback and a pooled connection can never carry one request’s organization into the next. The value is a bound parameter, never interpolated. Getting either of those wrong fails SILENTLY and leaks across the wall, which is why there is one copy of it.

org_id MUST come from something the caller verified — a resolved link row or a validated token — never from a vendor payload or a request body. Passing a falsy value is refused rather than binding an empty GUC that would match no row: an unscoped read should be an explicit act, not a typo that looks like an empty result.

def rls_policy_sql(table: str) -> str

Render the standard tenant+workspace RLS policy DDL for table, as one string.

Several statements separated by ;. A driver that runs one statement per call (asyncpg refuses several commands in one execute) takes rls_policy_statements instead.

Emitted as four per-command policies so the read/write asymmetry holds for every command, not just INSERT:

  • SELECT — tenant match AND workspace in the allowed set (active ∪ read grants).
  • INSERT / UPDATE / DELETE — tenant match AND the active workspace only.

A single FOR ALL policy cannot express this. PostgreSQL applies WITH CHECK only to the new row of INSERT/UPDATE and never to DELETE, while USING decides which existing rows UPDATE/DELETE may touch, so under one policy the read predicate (the allowed set) would govern which rows a DELETE or UPDATE selects. Per-command policies bind writes to the active workspace on both the row selected (USING) and the row written (WITH CHECK). The system role bypasses workspace scoping (system_session) but never the tenant wall. The leading DROP POLICY IF EXISTS statements make the DDL idempotent.

def rls_policy_sql_org_scoped(table: str) -> str

Render a single-key RLS policy for a table isolated by org_id.

For a service whose outermost isolation boundary is an identity provider’s organization, not the platform’s tenant: one that holds no platform token and therefore has no tenant_id, and has no workspace dimension of its own (a messaging bridge that forwards a workspace to a runtime, say, never enforcing it as a wall). What it does have is a hard wall, and org_id is it: organization A’s rows must never be visible to organization B.

org_id IS NOT tenant_id, and the two must not be read as peers:

  • tenant_id is the platform’s own tenant, asserted by the NimbleBrain host into a verified platform token. A service holding one MUST use rls_policy_sql (or rls_policy_sql_tenant_scoped) and secured_session.
  • org_id is an upstream identity provider’s organization id. It is the isolation key within such a service’s own database and nothing more. In particular it is NOT an authorization decision anywhere else: such a service routes on an admin-established binding, never on the org_id in a token it relays. Isolating rows by it and routing on it are different acts; this function is only the first.

The caller MUST bind the org from something it has verified — a link row the request already resolved, or a validated token — and NEVER from a vendor payload or a request body. That is the same invariant SecurityContext enforces for tenant claims, with a different verifier.

Shape-wise this is the one-key form rls_policy_sql_tenant_scoped already established, under a different column: both READ (USING) and WRITE (WITH CHECK) scope on the single key, and current_setting(.., true) is NULL when unset, so an unscoped session sees nothing — default deny. Mixing shapes therefore fails CLOSED: a session that binds app.tenant_id/app.workspace_id matches no row on an org-scoped table, and vice versa.

Like the tenant-scoped policy, this INTENTIONALLY carries no app.role='system' escape. A reconciler or sweep that must cross organizations (re-encrypting every stored ciphertext under a rotated master, say) has no role bypass to lean on: it must bind a concrete app.org_id per organization it touches, one scoped session at a time. That is deliberate — a cross-org read grant on these tables would be a hole in the plane the whole design depends on.

def rls_policy_sql_tenant_scoped(table: str) -> str

Render a TENANT-SCOPED RLS policy for a tenant-global ledger table.

For tables that hold a tenant-level FACT, not workspace business data — a legal opt-out / suppression list, an audit log, billing. Such a fact binds across ALL of a tenant’s workspaces (a recipient opts out from the company, not one workspace), so both READ (USING) and WRITE (WITH CHECK) scope on the tenant only; workspace_id on the row is provenance (which workspace recorded it), never an access key.

Tenant is still the hard wall, and current_setting(.., true) is NULL when unset, so an unscoped session sees nothing — default-deny, exactly like rls_policy_sql. Use this ONLY for genuinely tenant-global tables; workspace business data MUST use the workspace-scoped rls_policy_sql.

NOTE: this policy INTENTIONALLY omits the app.role='system' escape that rls_policy_sql carries, so every writer must be tenant-authenticated. A system_session reconciler or an async webhook (a write with no tenant on the request) would be SILENTLY DENIED here: it must bind a concrete app.tenant_id per row it writes (one scoped session per tenant), since there is no role bypass to lean on.

def rls_policy_statements(table: str) -> list[str]

The statements of rls_policy_sql, one per item, none ending in ;.

For a migration that runs on asyncpg, which refuses several commands in one execute:

for statement in rls_policy_statements("notes"):
op.execute(statement)
def rls_policy_statements_org_scoped(table: str) -> list[str]

The statements of rls_policy_sql_org_scoped, one per item.

def rls_policy_statements_tenant_scoped(table: str) -> list[str]

The statements of rls_policy_sql_tenant_scoped, one per item.

async def secured_session(engine: AsyncEngine, ctx: pgbulkhead.SecurityContext) -> AsyncGenerator[AsyncConnection]

A transaction with ctx bound. Every query inside runs under RLS scoped to (tenant, workspace, allowed_workspaces). Default-deny: if a policy can’t match the context, it returns nothing.

Raises ValueError, before opening anything, for a context that cannot be bound faithfully: an empty tenant or workspace, or an empty or comma-bearing workspace, read grant or scope. The read grants and scopes are bound comma-joined and the policy splits them again, so a comma inside one value would split it into several and widen what the session may read (pgbulkhead.check_context).

def security_context_from_edge_headers(headers: Mapping[str, str], *, user_header: str | None = None) -> SecurityContext

The per-request SecurityContext from the edge’s identity headers.

Tenant and workspace are stripped of surrounding whitespace; either one missing or empty raises AuthError, tenant checked first. user_header names a header to read user_id from, for a server that records one; it is None when the header is absent or empty, and it is never an authorization input.

async def system_session(engine: AsyncEngine) -> AsyncGenerator[AsyncConnection]

A transaction under the system role, for a job that no request’s identity covers.

A reconciler, a sweep or an admin job has no caller, so it has no tenant or workspace to bind. This binds app.role = 'system' instead, and the tenant+workspace policy (rls_policy_sql) lets that role past its workspace predicate. It does not pass the tenant wall: the tenant predicate still compares against app.tenant_id, 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 at all.

The role is a session setting, not a database role: the connection is still the app role, and the binding is transaction-local like any other. Never open one on a request path, where secured_session with the caller’s context is the only correct session, and record each use in the server’s audit trail as the actor system.

class AdmittedSlot(ctx: SecurityContext, open_session: Callable[[SecurityContext], AbstractAsyncContextManager[Any]])

One of a workspace’s slots, held by a request that opens several sessions in turn.

Each session runs under the held slot, so the request uses one connection at a time and never waits on its own workspace’s slots.

Until a session under the slot has opened, nothing has run under it, so a checkout that times out raises PoolExhausted, which says a retry is safe. Once one has opened, it may have committed, and a later checkout that times out raises the pool’s own TimeoutError instead: the request as a whole is no longer safe to retry.

async def session() -> AsyncGenerator[Any, None]

A session for the slot’s context, under the held slot.

Whether a checkout timeout is a refusal is decided on entry, not when the context manager is made: one made earlier may be entered after another session has opened.

class AuthError(detail: str)

Identity could not be established from the edge headers — fail closed.

Raised when x-tenant-id or x-workspace-id is missing or empty; it is never defaulted to a permissive scope. It is the auth_required ServiceError: message is all a caller sees, and detail names the missing header for the operator’s logs. A tool returns it with to_tool_error(exc), or the server calls add_error_contract(mcp) once and lets it propagate.

def from_upstream(status: int, message: str | None = None, *, detail: str | None = None) -> ServiceError

Build from a raw upstream HTTP status (the common provider-error path).

The bare status number is stamped as host-safe upstream_status; the vendor’s prose belongs in detail (logs only), never in message. Omit message to use the classified code’s host-safe default.

metric_code: str

The closed-set code used as the service_errors_total label.

Cross-cutting errors are their own code; domain subclasses override this to roll up, keeping the metric’s label set bounded.

def to_envelope() -> dict[str, object]

The host-safe error payload (HTTP body / MCP structuredContent.error).

class Overloaded()

The call was refused before it ran anything, because the server is at capacity.

message and next_step are text a server can return to the agent as they are: they say nothing ran, so a retry is safe. Map it onto your error surface as a rate limit. Replace the text with refusals on SecuredDatabase or admitted_session.

class PoolConfigError(ValueError)

A pool or admission setting is malformed, or the settings together cannot hold.

class PoolExhausted()

An admitted call found no free database connection within the pool’s timeout.

class PoolSettings(pool_size: int = 5, max_overflow: int = 5, pool_timeout_seconds: float = 10.0, workspace_concurrency: int = 2, workspace_wait_seconds: float = 15.0)

One server’s pool and per-workspace admission settings, validated on construction.

The field defaults are conservative, sized for a server whose peak is a few calls in flight, so several servers sharing one database stay well inside its connection budget.

def engine_options() -> dict[str, Any]

The pool keywords for create_async_engine.

def from_env(prefix: str, *, defaults: PoolSettings | None = None, environ: Mapping[str, str] | None = None) -> PoolSettings

Read <prefix>_DB_POOL_SIZE, …_DB_POOL_MAX_OVERFLOW, …_DB_POOL_TIMEOUT_SECONDS, …_WORKSPACE_CONCURRENCY and …_WORKSPACE_WAIT_SECONDS; an unset or blank one takes its value from defaults (this class’s when omitted).

Raises PoolConfigError, naming the variables, for a value that does not parse or settings that cannot hold together.

total: int

The most connections the pool holds: pool_size + max_overflow.

class SecuredDatabase(*, url_env: str, prefix: str, defaults: PoolSettings | None = None, url: Callable[[], str] | None = None, refuse_bypass_roles: bool = True)

The settings, engine and limiter one server process uses for its own database.

url_env names the env var holding the async Postgres URL (the app role, never a superuser). url replaces how the URL is read, for a server whose missing URL means something specific. prefix is the env namespace of the pool settings, and defaults the server’s own when its traffic needs more than the conservative defaults (PoolSettings). refuse_bypass_roles (on by default) makes the engine refuse a superuser or BYPASSRLS role (create_pooled_engine); turn it off only for a database object that is meant to bypass row-level security.

async def admitted(ctx: SecurityContext) -> AsyncGenerator[AdmittedSlot, None]

Hold one of ctx’s workspace slots across several sessions opened in turn.

def check() -> None

Read and validate the settings and build the limiter; call it at startup.

Raises PoolConfigError for settings that cannot hold, so a bad value fails the rollout rather than a user’s call. Opens no connection.

async def check_role() -> None

Open one connection and raise UnsafeRoleError if its session bypasses row-level security; call it at startup, beside check.

The engine refuses such a role on every connection anyway, so a session can never run as one; this makes the rollout fail rather than the first user’s call.

async def dispose() -> None

Dispose the engine and its pool; the next session builds a new one.

def get_engine() -> AsyncEngine

The pooled engine, built on first use, refusing bypass roles unless told not to.

limiter: WorkspaceLimiter

The process-wide per-workspace limiter, sized with the pool.

def session(ctx: SecurityContext) -> AbstractAsyncContextManager[AsyncConnection]

The call’s own scoped session, admitted against ctx’s workspace limit first.

Raises Overloaded, having run nothing, when no slot frees in time or, once admitted, no connection does.

settings: PoolSettings

The pool and admission settings, read and validated on first use, then fixed.

def system_session() -> AbstractAsyncContextManager[AsyncConnection]

A system-role session (reconcilers, admin); not admitted, never per workspace.

def unadmitted_session(ctx: pgbulkhead.SecurityContext) -> AbstractAsyncContextManager[AsyncConnection]

An RLS session with no admission, for one opened while the request holds a slot.

class SecurityContext(tenant_id: str, workspace_id: str, allowed_workspaces: frozenset[str] = frozenset(), scopes: frozenset[str] = frozenset(), user_id: str | None = None, correlation_id: str | None = None)

Identity + grants for one request.

  • tenant_id — the hard isolation boundary. Never crossed.
  • workspace_id — the active workspace (the soft boundary).
  • allowed_workspaces — workspaces this principal may read across, from explicit grants embedded in the token. Always includes the active workspace. A read grant does NOT imply write (writes go to the active workspace only — enforced by the RLS WITH CHECK).
  • scopes — capability scopes (e.g. crm:write).
def from_claims(claims: dict[str, object]) -> SecurityContext

Build from VERIFIED token claims. Raises if tenant/workspace absent — fail closed rather than default to a permissive scope.

def identity_columns() -> dict[str, str]

The tenant_id / workspace_id columns a row inserted under this context carries: the active workspace, the only one the RLS WITH CHECK admits a write to.

def readable_workspaces() -> frozenset[str]

The workspaces a session may read: the grants plus the active one.

def require_scope(scope: str) -> None

Raise PermissionError unless the context carries scope.

class UnsafeRoleError(RuntimeError)

The connection’s session bypasses row-level security (its role is a superuser, has BYPASSRLS, belongs to a managed Postgres admin role, or was seen to bypass a forced policy), so pgbulkhead refuses to use it.

class WorkspaceBusy()

A call waited its bound for one of its workspace’s slots and did not get one.

class WorkspaceLimiter(*, limit: int, wait_seconds: float)

At most limit concurrent admitted calls per workspace, waiting wait_seconds.

async def admit(ctx: SecurityContext) -> AsyncGenerator[None, None]

Hold one of ctx’s workspace’s slots for the body, or raise WorkspaceBusy.

The lookup and the count change run with no await between them, so on one event loop no other call can see the entry between its creation and its first user.

def from_settings(settings: PoolSettings) -> WorkspaceLimiter

The limiter settings sized together with the pool.

def tracked() -> int

How many workspaces have an entry: those with a call holding or waiting for a slot.

CLEANUP_TIMEOUT_SECONDS = 10.0
CONNECTIONS_PER_CALL = 2
MANAGED_ADMIN_ROLES = ('rds_superuser', 'azure_pg_admin', 'cloudsqlsuperuser', 'alloydbsuperuser', 'neon_superuser')
TENANT_HEADER = 'x-tenant-id'
WORKSPACE_HEADER = 'x-workspace-id'