db
nimblebrain_server_sdk.db · install nimblebrain-server-sdk[db] · version 0.7.2
from nimblebrain_server_sdk.db import AdmittedSlot, AuthError, CLEANUP_TIMEOUT_SECONDSTenant + 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 LOCALsemantics), 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_sqlis 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, admittedsession(ctx)andadmitted(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 orBYPASSRLSrole, a member of a managed Postgres admin role inMANAGED_ADMIN_ROLES, or any session a probe sees bypass a forced policy, when the session can run the probe) withUnsafeRoleErrorunless built withrefuse_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 raisesOverloaded, which a server returns asrate_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.
Functions
Section titled “Functions”create_pooled_engine
Section titled “create_pooled_engine”def create_pooled_engine(url: str, settings: PoolSettings, *, refuse_bypass_roles: bool = True, **options: Any) -> AsyncEngineAn 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.
org_session
Section titled “org_session”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.
rls_policy_sql
Section titled “rls_policy_sql”def rls_policy_sql(table: str) -> strRender 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.
rls_policy_sql_org_scoped
Section titled “rls_policy_sql_org_scoped”def rls_policy_sql_org_scoped(table: str) -> strRender 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_idis the platform’s own tenant, asserted by the NimbleBrain host into a verified platform token. A service holding one MUST userls_policy_sql(orrls_policy_sql_tenant_scoped) andsecured_session.org_idis 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 theorg_idin 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.
rls_policy_sql_tenant_scoped
Section titled “rls_policy_sql_tenant_scoped”def rls_policy_sql_tenant_scoped(table: str) -> strRender 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.
rls_policy_statements
Section titled “rls_policy_statements”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)rls_policy_statements_org_scoped
Section titled “rls_policy_statements_org_scoped”def rls_policy_statements_org_scoped(table: str) -> list[str]The statements of rls_policy_sql_org_scoped, one per item.
rls_policy_statements_tenant_scoped
Section titled “rls_policy_statements_tenant_scoped”def rls_policy_statements_tenant_scoped(table: str) -> list[str]The statements of rls_policy_sql_tenant_scoped, one per item.
secured_session
Section titled “secured_session”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).
security_context_from_edge_headers
Section titled “security_context_from_edge_headers”def security_context_from_edge_headers(headers: Mapping[str, str], *, user_header: str | None = None) -> SecurityContextThe 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.
system_session
Section titled “system_session”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.
Classes
Section titled “Classes”AdmittedSlot
Section titled “AdmittedSlot”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.
AdmittedSlot.session
Section titled “AdmittedSlot.session”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.
AuthError
Section titled “AuthError”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.
AuthError.from_upstream
Section titled “AuthError.from_upstream”def from_upstream(status: int, message: str | None = None, *, detail: str | None = None) -> ServiceErrorBuild 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.
AuthError.metric_code
Section titled “AuthError.metric_code”metric_code: strThe 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.
AuthError.to_envelope
Section titled “AuthError.to_envelope”def to_envelope() -> dict[str, object]The host-safe error payload (HTTP body / MCP structuredContent.error).
Overloaded
Section titled “Overloaded”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.
PoolConfigError
Section titled “PoolConfigError”class PoolConfigError(ValueError)A pool or admission setting is malformed, or the settings together cannot hold.
PoolExhausted
Section titled “PoolExhausted”class PoolExhausted()An admitted call found no free database connection within the pool’s timeout.
PoolSettings
Section titled “PoolSettings”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.
PoolSettings.engine_options
Section titled “PoolSettings.engine_options”def engine_options() -> dict[str, Any]The pool keywords for create_async_engine.
PoolSettings.from_env
Section titled “PoolSettings.from_env”def from_env(prefix: str, *, defaults: PoolSettings | None = None, environ: Mapping[str, str] | None = None) -> PoolSettingsRead <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.
PoolSettings.total
Section titled “PoolSettings.total”total: intThe most connections the pool holds: pool_size + max_overflow.
SecuredDatabase
Section titled “SecuredDatabase”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.
SecuredDatabase.admitted
Section titled “SecuredDatabase.admitted”async def admitted(ctx: SecurityContext) -> AsyncGenerator[AdmittedSlot, None]Hold one of ctx’s workspace slots across several sessions opened in turn.
SecuredDatabase.check
Section titled “SecuredDatabase.check”def check() -> NoneRead 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.
SecuredDatabase.check_role
Section titled “SecuredDatabase.check_role”async def check_role() -> NoneOpen 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.
SecuredDatabase.dispose
Section titled “SecuredDatabase.dispose”async def dispose() -> NoneDispose the engine and its pool; the next session builds a new one.
SecuredDatabase.get_engine
Section titled “SecuredDatabase.get_engine”def get_engine() -> AsyncEngineThe pooled engine, built on first use, refusing bypass roles unless told not to.
SecuredDatabase.limiter
Section titled “SecuredDatabase.limiter”limiter: WorkspaceLimiterThe process-wide per-workspace limiter, sized with the pool.
SecuredDatabase.session
Section titled “SecuredDatabase.session”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.
SecuredDatabase.settings
Section titled “SecuredDatabase.settings”settings: PoolSettingsThe pool and admission settings, read and validated on first use, then fixed.
SecuredDatabase.system_session
Section titled “SecuredDatabase.system_session”def system_session() -> AbstractAsyncContextManager[AsyncConnection]A system-role session (reconcilers, admin); not admitted, never per workspace.
SecuredDatabase.unadmitted_session
Section titled “SecuredDatabase.unadmitted_session”def unadmitted_session(ctx: pgbulkhead.SecurityContext) -> AbstractAsyncContextManager[AsyncConnection]An RLS session with no admission, for one opened while the request holds a slot.
SecurityContext
Section titled “SecurityContext”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 RLSWITH CHECK).scopes— capability scopes (e.g.crm:write).
SecurityContext.from_claims
Section titled “SecurityContext.from_claims”def from_claims(claims: dict[str, object]) -> SecurityContextBuild from VERIFIED token claims. Raises if tenant/workspace absent — fail closed rather than default to a permissive scope.
SecurityContext.identity_columns
Section titled “SecurityContext.identity_columns”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.
SecurityContext.readable_workspaces
Section titled “SecurityContext.readable_workspaces”def readable_workspaces() -> frozenset[str]The workspaces a session may read: the grants plus the active one.
SecurityContext.require_scope
Section titled “SecurityContext.require_scope”def require_scope(scope: str) -> NoneRaise PermissionError unless the context carries scope.
UnsafeRoleError
Section titled “UnsafeRoleError”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.
WorkspaceBusy
Section titled “WorkspaceBusy”class WorkspaceBusy()A call waited its bound for one of its workspace’s slots and did not get one.
WorkspaceLimiter
Section titled “WorkspaceLimiter”class WorkspaceLimiter(*, limit: int, wait_seconds: float)At most limit concurrent admitted calls per workspace, waiting wait_seconds.
WorkspaceLimiter.admit
Section titled “WorkspaceLimiter.admit”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.
WorkspaceLimiter.from_settings
Section titled “WorkspaceLimiter.from_settings”def from_settings(settings: PoolSettings) -> WorkspaceLimiterThe limiter settings sized together with the pool.
WorkspaceLimiter.tracked
Section titled “WorkspaceLimiter.tracked”def tracked() -> intHow many workspaces have an entry: those with a call holding or waiting for a slot.
Constants
Section titled “Constants”CLEANUP_TIMEOUT_SECONDS
Section titled “CLEANUP_TIMEOUT_SECONDS”CLEANUP_TIMEOUT_SECONDS = 10.0CONNECTIONS_PER_CALL
Section titled “CONNECTIONS_PER_CALL”CONNECTIONS_PER_CALL = 2MANAGED_ADMIN_ROLES
Section titled “MANAGED_ADMIN_ROLES”MANAGED_ADMIN_ROLES = ('rds_superuser', 'azure_pg_admin', 'cloudsqlsuperuser', 'alloydbsuperuser', 'neon_superuser')TENANT_HEADER
Section titled “TENANT_HEADER”TENANT_HEADER = 'x-tenant-id'WORKSPACE_HEADER
Section titled “WORKSPACE_HEADER”WORKSPACE_HEADER = 'x-workspace-id'