Security and isolation — extended reference

This reference preserves the earlier detailed chapter and its citations. Start with the guided explanation, then use these sections for additional mechanisms and qualifications. References to the original lesson sequence are historical.

Part 7: Building and operating applications on these engines

Prerequisites: Lesson 39 and the preceding foundations.

Learning outcomes

• Trace authentication, authorization, and isolation through a data request and subscription.
• Explain row/column visibility, resource abuse, key handling, and audit obligations.
• Identify why shared caches, internal services, and authorization changes need explicit trust boundaries.

A valid identity can still make an unauthorized request

A user logs in successfully, then changes a tenant identifier in a request. The endpoint queries a row belonging to another tenant and returns it. Transport encryption and authentication both worked. Authorization failed.

Database security follows data through every representation and operation. Queries, caches, subscriptions, logs, backups, repair tools, and administrative APIs can all expose or modify information. A secure design combines identity, permission, isolation, resource limits, and operational access rather than relying on one gateway check.

Credential and role lifetimes

A credential represents authority under a specific issuer, audience, expiry, and validation mechanism. The server validates the required properties rather than trusting a decoded token as evidence. Rotation and revocation need a policy for existing sessions, cached verification material, and background jobs.

Runtime, migration, and recovery roles should have different scopes when their responsibilities differ. The ordinary application need not delete backups or alter policies. A background task should receive its intended identity explicitly instead of inheriting an unrestricted credential by convenience. This limits the consequence of one component’s compromise without pretending internal services are automatically trustworthy.

Session state is another lifetime. A pooled connection must not carry the previous request’s transaction, role, or tenant context into the next borrower. Async work must retain the correct actor even when execution moves to a different worker. The lifetime of a database connection is often much longer than the lifetime of the authority it temporarily represents.

Identity must survive every boundary correctly

A request starts with an authenticated identity, but downstream components can lose or misinterpret that context. A gateway may validate a token, an application may select a tenant, and a database may execute under a shared service role. The design must show how the authenticated actor constrains the actual rows accessed.

A tenant identifier supplied by the client is a requested scope, not proof of membership. The server should verify that the actor may act in that scope. If it trusts an arbitrary header after token validation, authentication can succeed while authorization fails completely.

token -> verified actor
actor + requested tenant -> authorized scope
scope + requested operation -> allowed transition
allowed transition -> protected data access

Figure: Authentication establishes identity; subsequent checks establish scope and action. Each arrow can be a trust boundary.

A service-to-service request has the same issue. One service’s broad credential does not mean it should perform every operation requested by an upstream user. The receiving component needs an explicit delegation contract or a narrow interface that prevents the upstream caller from exceeding its authority.

A concrete cross-tenant failure

Suppose an endpoint fetches /tenants/18/invoices/901. The caller is authenticated as a member of tenant 17. The application queries invoice 901 by its globally unique primary key and returns it, assuming the route already checked access. Another middleware checked that the caller belongs to some tenant but did not bind membership to 18.

Every component performed a plausible partial check, but no component enforced the actual relationship. A correct query or policy binds invoice ownership, requested tenant, and authorized actor together. The invariant is not “user is logged in”; it is “this actor may read this invoice in this scope.”

Composite identifiers can help express relationships, but they are not authorization by themselves. Unpredictable IDs reduce casual guessing without preventing disclosure through logs, links, or compromised accounts. Security cannot depend on a user never learning another record’s identifier.

Tests should include valid users making invalid cross-tenant requests, not only anonymous users. Many isolation failures occur through legitimate authenticated sessions using unexpected object combinations.

Row policies need role-aware interpretation

PostgreSQL documents row-level security policies and the role conditions under which they apply, including privileged bypass behavior. Policies are a useful enforcement layer, but they must be tested under the actual application roles and session settings. PostgreSQL row security

A policy relying on a session variable needs a safe source for that variable. If arbitrary SQL can change it, the policy may merely enforce user-supplied claims. A pooled connection also must reset prior request context; otherwise one tenant’s setting can leak into another request.

Views and server functions can change execution context. Security-definer behavior, search paths, and object ownership deserve explicit review in systems that support them. A helper intended to simplify access can accidentally expose broader privileges than its caller should have.

The goal is defense in depth with clear responsibility. Application authorization can provide product-specific messages, while database policies constrain direct data access. Two layers that both assume the other performed the critical check do not provide two protections.

Caches and subscriptions are data-access APIs

A cache key containing only query text can be unsafe when results depend on identity. If tenant 17 first fills recent_invoices, tenant 18 must not receive the same cached bytes. Include the relevant security scope or cache only demonstrably public data.

A subscribed query is also a persistent access request. Client filters select desired data but cannot substitute for server authorization. A modified client can ask for broader predicates. The server must enforce permitted tables, fields, and rows regardless of SDK convenience.

Permission changes should affect active subscriptions under the stated contract. Revoking room membership can require removing rows or terminating subscriptions, while historical replay must not restore unauthorized access. Previously delivered plaintext may remain on a modified client, so revocation cannot make old disclosures disappear.

SpacetimeDB’s guidance recommends views for fine-grained visibility and describes its separate RLS feature as experimental. That version-specific distinction matters when choosing the actual enforcement mechanism. SpacetimeDB access-control guidance

Shared computation can become a side channel

Even if result bytes are correct, timing and resource effects can reveal information. A query that returns different error details for “record exists but forbidden” and “record absent” can disclose existence. A shared cache can make one user’s access measurably affect another’s response time.

Whether these channels matter depends on threat model and sensitivity. A public catalog and a confidential medical tenant require different protections. The architecture should avoid claiming complete noninterference merely because row filters are present.

Aggregate results can also reveal private facts. If a user can ask a count for all employees and then all employees except one, the difference can disclose information about that person. Authorization for aggregates may need minimum group sizes, restricted predicates, or another product policy. The engine supplies computation, not the privacy policy itself.

Error handling should provide enough information for authorized diagnosis without revealing private identifiers or query contents to an attacker. Detailed internal errors can go to protected logs with appropriate redaction rather than untrusted responses.

Encryption protects particular paths

TLS protects data in transit under its authentication and configuration model. It does not authorize the requested operation or protect plaintext after a legitimate endpoint decrypts it. At-rest encryption protects stored media under a different threat model and requires key management.

PostgreSQL’s TLS documentation supplies concrete connection configuration behavior. A deployment must verify peer identity appropriately rather than assuming encryption alone proves it reached the intended server. PostgreSQL TLS connections

Keys introduce availability and recovery dependencies. Rotating a key requires ensuring retained data remains decryptable. Deleting an old version can make backups unusable. Storing every key beside the encrypted backup under the same unrestricted credential weakens the intended separation.

Application logs, traces, exports, and support tools often contain plaintext. Encrypting database files does not address those copies. A data-flow model should identify where sensitive values are decoded, retained, displayed, or transmitted again.

Resource abuse is an isolation failure

A valid user can submit an expensive query, request enormous results, or open many subscriptions. Authorization to read data does not imply permission to consume unlimited resources. Security includes protecting availability for other tenants.

Suppose an endpoint can return 10 MB and a user requests it 1,000 times/s. Payload demand alone is 10 GB/s before overhead. Even if every returned byte is authorized, the request pattern can overwhelm the service. Result limits, pagination, concurrency budgets, and rate controls constrain this risk.

Request counts are a rough proxy. One scan can cost more than thousands of point reads. Cost-aware admission can use estimated rows, bytes, CPU, and retained subscription state, with hard bounds when estimates fail. A query that passes a cheap initial check must not allocate unbounded memory later.

Fairness applies to background work too. A tenant export, backfill, or index build can affect neighbors. Administrative privileges should not mean routine maintenance bypasses every safety budget. Emergency access can exist with audit and clear scope rather than becoming the ordinary execution path.

Query construction is a trust boundary

Untrusted values should be bound as parameters rather than inserted into SQL syntax. Parameters preserve a distinction between data and code. They do not automatically validate business meaning: an integer account ID can be safely parameterized and still refer to an unauthorized account.

Dynamic identifiers require a separate approach because table and column names are syntax, not ordinary value parameters. An application can select from a known allowlist or use the database driver’s supported identifier-quoting mechanism where genuinely needed. A generic escape function applied everywhere is easy to misuse.

The same principle applies to query languages for subscriptions, document filters, and search expressions. A restricted API can express the operations the product needs without exposing arbitrary server-side computation. Validation at the boundary should cover size, type, shape, and allowed operation.

A secure parser also defends against resource exhaustion. Deeply nested expressions, huge parameter lists, and oversized values can consume work before execution begins. Bounds belong in decoding and planning as well as in the final query operator.

Audit records are useful only if trustworthy

An audit trail can record actor, scope, operation identity, affected objects, outcome, and timing. It should distinguish attempted, authorized, committed, and externally completed actions. A log line written before commit does not prove the transaction succeeded.

Audit retention and access control require their own design. An attacker who can alter both business data and its only audit record may erase evidence. Independent append-oriented retention can protect against selected threats, subject to the actual storage and credential model.

Audit payloads should avoid unnecessary secrets. Recording full access tokens, passwords, or every row value can create a valuable secondary dataset with broader access than the primary. Identifiers and carefully chosen evidence often support investigation without copying all sensitive content.

Operational trace IDs help connect events but are not authentication credentials. Accepting a client-supplied trace label as proof of actor identity would confuse observability with trust. Every identifier should have a documented meaning and authority source.

Backups extend the security perimeter

A backup can contain all tenants, old permissions, and data users believe deleted from the live interface. Restore tools may bypass ordinary row policies. The people and services allowed to retrieve backups therefore need narrower, audited access.

A historical restore can also reintroduce revoked credentials or old access grants. Recovery must separate historical business reconstruction from current security policy. Restoring an old database wholesale without reviewing authority can reopen access that was intentionally removed.

Test environments created from production backups require the same attention. Masking or synthetic data can reduce exposure where appropriate, but masking needs referential and semantic consistency for useful tests. A poorly masked dataset can remain reidentifiable through combinations of attributes.

The security boundary follows every copy, including developer laptops and debugging exports. A database’s native authorization cannot protect plaintext copied into an unrelated spreadsheet or log system.

A secure shared-world design

A hypothetical game has public positions, party-only chat, and private inventories. Public position subscriptions can be shared broadly within interest regions. Party chat queries bind current membership. Inventory views bind the authenticated identity to the owned player and exclude private fields belonging to others.

Reducers validate actions separately. Seeing an item does not authorize taking it; the server checks distance, ownership, cooldowns, and current availability. A client-provided player identifier cannot impersonate another player. Requested actions carry stable identities so retries do not repeat effects.

Resource limits cap subscribed regions and output bytes. A user cannot request the whole world merely because each individual public row is readable. Permission revocation invalidates affected subscriptions, while already delivered information remains acknowledged as disclosed.

This scenario combines correctness, confidentiality, and availability. None can be inferred from another. A transaction can atomically perform an unauthorized action, an encrypted channel can deliver private data to the wrong user, and a fully authorized query can exhaust shared memory.

One request-path table joins policy to delivery

Table: Trust follows data beyond the original query. Each representation needs an access contract.

This table is useful because security failures often occur between otherwise reasonable components. An implementation should identify one authoritative source for actor identity and policy, then preserve their meaning across these boundaries. Rechecking an untrusted copied claim is not independent protection.

Preventing authorization-check races

A service can correctly check ownership and still perform an unauthorized mutation if the relationship changes before use. Suppose a moderator may delete posts only within rooms they administer. It checks membership, waits for another remote operation, and later deletes a post. During the gap, the moderator loses membership or the post moves to another room.

The required semantics determine the protection. If authorization must hold at the mutation’s authoritative decision, the check and relevant data change need a common transaction or equivalent validated dependency. Reading authorization from an old cache without a version contract cannot establish current permission.

transaction:
    read current post and its room
    read actor's current permission for that room
    require permission allows deletion
    delete the protected post
commit under isolation that protects these decision dependencies

The pseudocode does not imply every permission system can be joined into one local transaction. If policy lives in a remote service, the architecture needs a token, lease, version, or explicit eventual-revocation contract. The choice must state how long previously granted authority can remain effective.

Long-running exports make the timing question unavoidable. A user starts an authorized export, then loses access halfway through. The product can stop future chunks, allow completion under a snapshot of authorization, or use another explicit rule. Every choice has different confidentiality and UX consequences. The server cannot postpone choosing simply because its query engine supports a snapshot.

Policy versions and prepared work

A cached query plan or prepared subscription can outlive the policy under which it was created. The system must distinguish a reusable computational plan from a reusable authorization decision. Parsing and optimizing once is often safe; permanently assuming the original permission remains valid may not be.

One design binds execution to a current policy version and invalidates affected cached decisions when that version changes. Another rechecks policy at every relevant execution boundary. Shared results can be tagged with the scope and policy generation that justified them. These are generic strategies, not a statement about one product’s internals.

The version must actually cover every relevant policy change. If membership changes in a different subsystem without advancing the version or triggering invalidation, the cache remains incorrectly trusted. A policy cache is a replicated derived representation and inherits the same completeness and freshness problems as any other cache.

Prepared work also has a resource consequence. Revoking a user’s right to start new queries does not necessarily stop queries already running. Cancellation, result suppression, or completion-under-prior-authority must follow the product’s rule. Security-sensitive systems should document the boundary rather than assume all revocations are instantaneous everywhere.

Secret handling through rotation and recovery

A key rotation can separate the key encrypting data from a key-encryption key that wraps it, depending on the architecture. Rewrapping changes protected key material without necessarily rewriting every data byte. A design that directly encrypts all data under one rotating key may require a different rewrite path. These are implementation choices with different operational costs.

During rotation, old and new data can coexist. Readers need access to the appropriate key versions until old representations and retained backups no longer require them. Deleting a retired key too early is a destructive data operation, even if live rows use the new key successfully.

A hypothetical backup created under key K1 remains retained for 90 days. Live data rotates to K2 today. A test restoring only today’s live database does not prove the old backup is recoverable. The recovery procedure must retrieve K1 under controlled access and verify its dependencies. Conversely, retaining K1 forever under broad access can undermine a security objective that required its eventual destruction.

Rotation also covers service credentials and sessions. A short-lived credential limits exposure but increases dependency on renewal infrastructure. If that infrastructure fails, running nodes may continue briefly while new nodes cannot start. Recovery capacity and security policy interact, and neither can be analyzed in isolation.

The trust-boundary argument is now concrete: the design controls who may act, when that authority is valid, how changes invalidate retained decisions, and how protected data remains usable without broadening access. Those mechanisms provide more assurance than repeating that authentication and encryption are enabled.

A negative authorization trace is evidence

A security review should follow one valid identity through a request it must not perform. For example, a member of tenant 17 requests tenant 18’s invoice through the ordinary endpoint, a cached endpoint, an export, and a subscription. The expected result remains denial or an authorized empty projection at every path, without leaking private fields in errors.

The same trace should run under the actual runtime database role, not only a privileged test owner. It should repeat after a pooled connection previously served tenant 18, because stale session context can make a request fail only under particular ordering. Finally, it should change membership while a long-lived subscription remains active and observe the documented revocation behavior.

These scenarios are explanatory acceptance evidence, not a substitute for a complete threat model. Their value is exposing assumptions between components. A policy can be locally correct while a cache, connection pool, or alternate API bypasses the intended boundary. Testing those crossings verifies the architecture the user actually encounters.

Security as an architectural property

A complete design follows identity and data through requests, transactions, caches, streams, logs, backups, and administrative operations. It identifies which component is trusted to establish each fact and tests failures under actual roles and pooled-session behavior.

The most effective controls often simplify the system: narrow APIs, explicit ownership, bounded results, stable operation identities, and consistent policy sources. Security becomes fragile when authorization is distributed across undocumented assumptions. The same explicit-boundary reasoning used for transactions and replication provides a disciplined way to prevent that fragility.

Sources and further reading

• PostgreSQL 18 row security
• PostgreSQL 18 TLS connections
• SpacetimeDB access control guidance

Course index · Lesson plan