Secure Data Storage, Databases, and Queries

The support export from Chapter 24 begins with three permitted invoice fields. The application reads them from a relational database, but that is not the only place they live. A cache may hold the invoice object. A search index may contain its description. A warehouse may receive it overnight. A vector store may hold an embedding of a support note. Replicas, snapshots, and backups preserve older versions. The export itself creates one more copy.

Suppose the primary query correctly restricts the request to Acme. The export can still cross a tenant boundary because the search filter was omitted, the warehouse role can read every customer, a cached object was keyed only by invoice ID, or retrieval found a semantically similar note belonging to another tenant. Each store can be healthy while the access path is unsafe.

Data therefore needs more than a secure database. Every representation must retain enough identity, tenant, classification, and provenance to enforce the decision that governs it. Every path to that representation needs a scoped credential, safe query construction, an integrity model, an encryption decision, a retention rule, and evidence that the path behaves as intended.

Follow the record, not the product inventory

Begin a storage review with one sensitive record and find every representation of it. Product names conceal important differences: two relational databases may have different roles and backup boundaries, while a search index and vector store may share the same dangerous assumption about metadata filters.

For the invoice, trace the authoritative row and each replica; cache entry; search document; analytics event and warehouse row; object or attachment; embedding and source chunk; snapshot; backup; development fixture; and exported file. Record the owner, data classes, tenant boundary, source, destination, retention, deletion behavior, and recovery purpose for each copy. If nobody can explain how a copy is produced and removed, it is not governed data.

The store type still changes the questions worth asking. A relational database offers constraints, transactions, views, and sometimes row or column policy. A document or key-value store makes partition keys, operator handling, consistency, and document shape central. Object storage adds naming, bucket policy, signed delivery, lifecycle, and content-processing concerns. A cache needs tenant-safe keys, sensitivity limits, expiry, and disciplined debug access.

Search indexes trade source structure for discoverability, so stale documents, indexed fields, filters, and result authorization matter. Warehouses add lineage, analyst roles, masking, broad joins, and bulk export. Vector stores add ingestion provenance, namespace or metadata scope, and authorization of retrieved source documents; similarity is not permission. Backups preserve the widest time horizon and often operate through identities different from the application.

This map should reveal a connected system rather than produce eight independent checklists. The same tenant_id that constrains the source row must survive the cache key, search document, warehouse transformation, embedding metadata, backup restore, and export job. A derived store that cannot represent the required access boundary may be unsuitable for that data.

Give each path only the authority it needs

The application, migration runner, replication process, analytics pipeline, backup service, restore operator, support tool, and administrator do different work. They should not share one database identity. Separate credentials make permissions narrower, rotation less disruptive, and actions attributable.

An online application role should normally lack schema ownership, role administration, backup access, and unrestricted cross-tenant export. A migration role may alter a specific schema during a controlled release without becoming the permanent application credential. Analytics ingestion can append to a bounded destination without reading unrelated operational tables. Human access should be named, approved, time-bounded where practical, and logged. Production and non-production must not share accounts or stores merely for convenience.

Break-glass access is deliberately powerful, so its path should be harder to use unnoticed: strong authentication, an explicit reason, short lifetime, immediate notification, complete event recording, and review after use. A shared emergency password in a team vault is not attributable merely because the vault has an audit log.

Application authorization remains necessary even when the store supports row or column policy. Store policy is valuable defense in depth against a missing predicate, a new endpoint, or a compromised application process, but only if the connection carries trustworthy tenant or actor context and pooled connections cannot leak that context between requests. Separate accounts, databases, schemas, restricted views, row policies, and allow-listed column projections provide different isolation strengths and operational costs. Choose from the threat model; then test the actual choice.

The test for Acme is concrete. With the export worker’s credential, can a query read another tenant, select payment fields, write arbitrary objects, inspect a backup, or invoke administrative functions? Any unneeded success is authority waiting for a bug to exercise it.

Keep values out of the query language

Authorization decides which records may be requested. Safe query construction keeps attacker-controlled values from changing what the request means. Both are required.

This query lets tenant and status become SQL syntax:

SELECT * FROM invoices
WHERE tenant_id = '${tenant}' AND status = '${status}'

Bind values instead, and select only the permitted fields:

SELECT id, status, total
FROM invoices
WHERE tenant_id = :tenant_id
  AND status = :status

The second query prevents those two values from rewriting the statement. It does not prove that the actor belongs to tenant_id, that total is an allowed export field, or that the database account should see every invoice. Those are separate properties of the access path.

Not every dynamic choice can be bound as a value. Column names, table names, operators, and sort direction often require a fixed mapping from a small public vocabulary to identifiers chosen in code. If a request says sort=recent, the server can map it to a reviewed expression; it should not append an arbitrary request string. Bound values plus an untrusted ORDER BY, projection, or table name still make a dynamic query.

An ORM or query builder is safe only along the paths that preserve this separation. Inventory raw-query methods, literal fragments, dynamic scopes, custom serializers, administrative consoles, and repository helpers that accept callers’ filter objects. Review generated queries at sensitive boundaries. Convenient methods such as findById(id) are also a common place to lose tenant scope: prefer an interface that requires the authorized tenant or an already scoped repository.

NoSQL and search systems have instruction languages too. Do not accept a JSON filter from a client and pass it to a driver, even if the payload is valid JSON. Construct a typed filter from allowed fields and operators, reject unexpected keys, bound result size and depth, and add server-derived tenant scope. Search syntax, regular expressions, scripts, aggregations, and expensive operators need the same treatment. Parameterization prevents instruction injection; it does not prevent an authorized-looking query from consuming excessive work or returning excessive data.

Vector retrieval needs two checks. Constrain the search with trusted tenant, collection, classification, or document metadata where the store supports it, then authorize the source identifiers returned before their content enters a prompt or response. A nearest-neighbor score says that two embeddings are close, not that the requester may read either source. Test missing metadata, stale permissions, deleted documents, namespace mistakes, and cross-tenant near duplicates.

For each query path, keep negative tests beside the allowed case. Substitute a tenant, object, field, operator, sort key, filter, page size, stale permission, and worker credential. Assert that unauthorized data is not fetched into an application buffer—not merely that the final response is denied.

Preserve meaning as data changes

Confidentiality failures are visible because data reaches the wrong reader. Integrity failures can be quieter: a duplicated refund, an invoice detached from its tenant, a stale search result, or a backfill that overwrites a newer decision.

Keep durable invariants close to the authoritative data. Use types, nullability, unique and check constraints, foreign keys where they fit the lifecycle, and transaction boundaries that commit one business change rather than a sequence of half-related writes. Use optimistic version checks or appropriate locking when two actors can update the same state. Application validation expresses business intent; store constraints remain useful when a race, repair command, partial deployment, or alternate writer bypasses the expected path.

Derived copies need an explicit consistency contract. Decide how a cache or index is invalidated, how deletion and permission changes propagate, how lag is measured, and what the reader sees while source and copy disagree. Reconcile counts and key invariants between the source, warehouse, index, and export. An authorization change that takes hours to reach the vector store is not merely eventual consistency; for those hours it is an access-control decision.

Audit fields should identify who or what changed high-value state, when, through which request or job, and under which approval or policy version. Do not make the application principal silently writable by ordinary update code. Where the risk calls for stronger evidence, use append-only records, chaining or signing, restricted log administration, and independent retention. “Tamper-evident” is not a property supplied by an append-only table alone if the same administrator can rewrite both the event and its evidence.

Encrypt the copies and separate the keys

Require authenticated encrypted transport on every network path to a store, including replication, administration, backup, and restore. Verify the peer; turning on TLS while accepting an arbitrary certificate protects little against an active network attacker.

At-rest encryption limits exposure from disks, snapshots, object media, or copied backups. It does not constrain a legitimate database session, a broadly authorized operator, or an injected query. Document which threat the storage layer addresses and which identities can ask the service to decrypt.

Field or application-layer encryption can create a stronger boundary for a small set of high-value fields, but it changes search, indexing, uniqueness, rotation, recovery, and incident response. Do not apply it as a label. Decide where plaintext exists, which service may decrypt, how old ciphertext is read during rotation, and what happens when the key is unavailable.

Separate keys when environments, services, tenants, data classes, or operator groups should not share one compromise boundary. Keep key administration apart from routine database administration where feasible. Log use, restrict grants, rotate through a tested transition, and prove recovery. Then inspect replicas, caches, indexes, warehouses, vector stores, snapshots, backups, temporary migration files, and exports: the primary database’s encryption setting does not extend to them by implication.

Treat a migration as a privileged program

A migration can bypass the application’s validation, authorization, rate limits, and normal rollback path while touching every record. Review both the schema operation and the data program it enables.

Before execution, identify the owner, reviewer, affected stores and data classes, expected row or object count, required credential, lock and performance behavior, tenant and field invariants, temporary plaintext or files, backup or snapshot decision, and observable completion condition. Rehearse on a representative safe dataset. Choose rollback only when rollback is genuinely safe; otherwise specify a forward repair and the point after which reversal would lose valid writes.

During execution, use a short-lived credential limited to the needed objects and operations. Bound batches, preserve idempotency or a durable checkpoint, monitor errors and lock pressure, and stop on violated invariants rather than pressing through to satisfy a schedule. Reconcile source and destination before removing the old path.

Tenant repartitioning, identifier rewrites, encryption transitions, permission model changes, large deletes, analytics backfills, and embedding regeneration deserve special suspicion. A vector regeneration job, for example, must preserve source document identity and tenant metadata while preventing stale and new chunks from becoming one mixed collection.

The migration record should answer:

• What exact data and stores will change, and who owns the decision?
• Which invariants, permissions, encryption boundaries, and retention rules must survive?
• What can the migration identity read and write, and when does it expire?
• How were runtime, locks, batches, failure, retry, rollback or forward repair, and concurrent writes exercised?
• Which counts, samples, constraints, reconciliation results, and security events will prove completion?

Make exports expire as well as succeed

An export is a new store with unusually convenient exfiltration properties. It needs the same authorization as the underlying reads plus a decision about bulk volume, fields, masking, destination, recipient, and lifetime.

For the support export, preserve a server-side approval record containing the requester and acting identity, tenant and object scope, allowed fields, purpose, classification, recipient, destination, masking rule, encryption requirement, expiry, policy version, and case or ticket reference. The worker should derive its query and projection from that record, not from a caller-editable queue message. Log creation, retrieval, expiry, deletion, denial, and exceptional access without copying the exported contents into the log.

Signed URLs transfer possession of a URL, not the original user’s identity. Keep them short-lived, restrict the object and operation, avoid placing secrets or personal data in object names, and require a fresh authorization decision when the risk warrants it. Lifecycle deletion is part of the control; an expired link does not remove a file that remains broadly readable to support, analytics, or storage administrators.

An export approval should be rejected when the requested fields exceed the purpose, masking would destroy the purpose or is omitted without justification, the destination or recipient is untrusted, encryption cannot survive delivery, retention has no owner, or the source authorization will expire before execution. Development copies, incident bundles, and analyst extracts are also exports even when no customer-facing “Export” button created them.

Review the complete access path

A storage review is ready when a reviewer can choose one protected record and follow every permitted and denied route to every representation. Keep the evidence compact enough to use:

• The data map names each relational, NoSQL, object, cache, search, warehouse, vector, replica, backup, development, and export copy, with its owner, classification, tenant boundary, lineage, retention, and deletion behavior.
• The access record names application, pipeline, migration, human, support, break-glass, backup, and restore identities; their exact privileges; and the evidence from review and use.
• The query record identifies parameterized or typed construction, allowed dynamic identifiers and operators, tenant and field scope, resource bounds, ORM escape hatches, and negative SQL, NoSQL, search, and vector tests.
• The integrity record states authoritative constraints, concurrency behavior, derived-copy consistency, reconciliation, audit-field protection, and any stronger tamper-evidence mechanism.
• The encryption record covers transport verification, at-rest boundaries, field-level decisions, key separation, rotation, recovery, and every derived copy rather than only the primary store.
• Migration evidence records scope, review, credentials, rehearsal, runtime safeguards, rollback or forward repair, reconciliation, and approval.
• Export evidence binds purpose and authority to rows, fields, masking, destination, recipient, encryption, retrieval, logging, expiry, and deletion.

Return once more to Acme’s three invoice fields. The access path is sound only if the application account cannot widen the read, the query cannot change its own grammar, tenant and field scope survive every derived copy, the export worker cannot exceed its delegated record, and the resulting file disappears when its purpose ends. Protecting the primary row was necessary. Protecting the record’s many lives is the storage system.