A tenant filter in a web handler is not tenant isolation if an analyst endpoint, job worker, or direct SQL path can omit it. Database security begins by naming the data, the authority allowed to use it, and every transition where an attacker or defect can change that authority. The database is both a policy enforcement point and a high-value archive of credentials, history, backups, and replicas.
Put authority on the data flow
The diagram has two authority claims: who is the caller, and which rows may this execution read or modify. The API should authenticate and derive tenant scope from a trusted session/token mapping. The database role should have only the permissions its code path requires. A database policy can add defense in depth, but it must be tested with the actual runtime role and with privileged bypass behavior explicitly understood.
Prediction — find the bypass and counterexample
An API filters every query by a client-supplied tenant ID. A scheduled export job connects with an owner-like database role and has no tenant predicate. Which path is the security boundary failure mode?
Worked solutionExpandCollapse
Both paths fail as a worked example of incomplete boundary design: the export job is an alternate path that can read every tenant, and the client-supplied tenant ID is not a trustworthy authority source. Derive tenant identity server-side, give the job a narrow role and explicit export scope, and enforce tenant ownership again in the database where practical. A correct public endpoint does not compensate for an overprivileged internal role.
Query construction and authorization solve different problems
| Need | Correct boundary | Common unsafe shortcut |
|---|---|---|
| value in a predicate | prepared statement / bound parameter | concatenate quoted input |
| user-selectable sort column | fixed allowlist mapped to known SQL fragments | bind an identifier as a value or concatenate it |
| tenant row access | server-derived tenant predicate and/or verified row policy | trust a browser-supplied tenant ID |
| migration changes | separate owner/migration role | let runtime API role alter schema |
| operational investigation | bounded support role plus audited procedure | share an administrator credential |
A parameterized statement still needs an authorization condition. A perfect tenant predicate still needs binding so an injected value cannot alter the predicate. Security controls compose; their names are not interchangeable.
Application — repair a query API
A report endpoint accepts a status value and a sort field. It runs under the same role that can delete users. State the two independent repairs.
HintExpandCollapse
Separate SQL syntax handling from database authority.
Worked solutionExpandCollapse
Bind the status value as a parameter and map the sort field through a fixed allowlist of known columns/directions. Separately replace the broad runtime role with a least-privilege reporting role that can read only the approved view/table scope. Escaping input alone leaves destructive authority available; reducing the role alone leaves injection risk.
Two controls, two jobs
A reporting endpoint binds its filter values as parameterized inputs, but executes using a database role with full superuser privileges. Evaluate which security concerns remain unresolved:
Classify each option as correct or incorrect, or leave uncertain options unanswered (“I don't know”) and verify at any time.
Tenant isolation needs layered tests
| Layer | Enforces | Test that can falsify it |
|---|---|---|
| application authorization | subject may act for tenant 12 | change a URL/body tenant ID to 13 |
| SQL query construction | input cannot rewrite a predicate | use hostile value syntax; verify query shape remains fixed |
| relational design | references do not cross tenant ownership | attempt a tenant-12 child pointing at tenant-13 parent |
| row-level policy | database filters rows for runtime role | issue a direct query as that role with no application predicate |
| audit/monitoring | sensitive access is discoverable | perform approved and denied access; inspect immutable event fields |
Composite tenant-aware keys can make invalid cross-tenant references unrepresentable. Row-level security in PostgreSQL is an implementation feature whose policy enablement, session identity, table owner/superuser bypass, and role privileges need explicit tests. A policy that is never enabled for the runtime role is theater.
Protect the lifecycle, not only the primary table
| Asset | Primary control | Recovery / detection evidence |
|---|---|---|
| runtime database credential | secret manager, rotation, narrow role | issuance/use record; revoke and validate replacement |
| customer rows | authorization, policies, constraints | denied-path tests; tenant-bound audit event |
| backup archive | independent access, encryption/key custody, retention | isolated restore with access logging |
| change history | append-oriented audit with protected writers | correlation across application and DB events |
| query traffic | TLS and certificate/endpoint validation | monitored failures and configuration review |
Transfer — leaked runtime credential
A production read/write application credential appears in a build log. Put the first actions in order.
HintExpandCollapse
Prioritize credential containment, revocation, and scope analysis before considering data restore.
Worked solutionExpandCollapse
Contain first: revoke or rotate the exposed credential and stop the log’s further distribution while preserving evidence. Identify its permissions and recent use, then inspect audit/query/access records for misuse and validate that the replacement uses a narrower role if possible. Rotate dependent connection pools and verify service behavior. A backup restore is not the first response unless data integrity evidence indicates destructive activity; encryption at rest does not invalidate a live credential.
Deep Practice
Solve these problems to test and consolidate your understanding. Complete worked solutions and diagnostic hints are available for each problem.
Exercise 5.3.1: Parameter Binding vs. Dynamic Identifier Injection
An endpoint constructs a query using: f"SELECT * FROM items WHERE status = $1 ORDER BY {user_sort};". The developer asserts that because $1 is parameterized, SQL injection is completely prevented. Verify why this claim is false, and demonstrate an exploit payload for user_sort.
HintExpandCollapse
Parameter binding cannot be used for SQL identifiers such as column names or sort directions.
Worked solutionExpandCollapse
Parameter binding separates data literals from query syntax trees; it cannot bind SQL keywords, table names, or column identifiers. If user_sort is interpolated directly into the query string, an attacker can pass (CASE WHEN (SELECT 1 FROM users WHERE name='admin' AND substring(password,1,1)='a') THEN id ELSE name END) or append stacked commands if multi-statement execution is enabled. Dynamic identifiers must be validated against a strict hardcoded allowlist.
Exercise 5.3.2: Separation of Runtime Role Privileges and Least Privilege
Explain why connecting an application web server using a single all-powerful postgres superuser role violates least privilege, and outline a four-tier role separation model for production database systems.
HintExpandCollapse
Differentiate application web traffic, background workers, analytics, and schema migrations.
Worked solutionExpandCollapse
Using a superuser role grants unrestricted authority (DROP TABLE, bypass RLS, read write-ahead logs, execute system shell commands via extensions). If an injection or unauthorized access defect occurs, the entire database is compromised.
Four-tier role model:
app_user: LimitedSELECT, INSERT, UPDATE, DELETEstrictly on necessary application tables; no DDL permissions.app_readonly:SELECTpermissions on reporting tables or views; used by analytics dashboards.migration_user: DDL privileges (CREATE, ALTER, DROP) used exclusively during CI/CD deployment pipelines.admin: Restricted administrative role reserved for DBA maintenance and emergency recovery.
Exercise 5.3.3: Row-Level Security Tenant Isolation Policy Definition
Write PostgreSQL DDL to enable row-level security on orders and create a policy ensuring that the current session (via current_setting('app.current_tenant_id')) can select and insert only rows belonging to its authenticated tenant.
HintExpandCollapse
Enable RLS on the table and define a policy using USING and WITH CHECK clauses.
Worked solutionExpandCollapse
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_orders ON orders
FOR ALL
TO app_user
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);The USING clause restricts visible rows for reads, updates, and deletes; the WITH CHECK clause prevents inserting or updating rows with a foreign tenant ID.
Exercise 5.3.4: Incident Response Protocol for Compromised Database Credentials
A continuous integration build log publicly exposes production read/write credentials for an orders database. Prescribe a 5-step incident response checklist to contain the breach, investigate potential data exfiltration, and re-establish secure operations.
HintExpandCollapse
Order actions: immediate revocation, credential rotation, log inspection, blast-radius audit, and post-mortem.
Worked solutionExpandCollapse
- Immediate revocation: Terminate existing connection pool sessions and revoke or rotate the exposed database password immediately in secrets management.
- Log suppression: Purge the public build log from version control and CI caches, invalidating web share links while preserving forensic copies internally.
- Audit query logs: Query database audit logs and VPC connection logs for unauthorized client IP addresses connecting with the exposed username during the exposure window.
- Data integrity verification: Run cryptographic checksums and domain invariant checks to detect unauthorized table modifications or exfiltration traces.
- Least-privilege redeployment: Issue new credentials with least-privilege role scoping, and implement CI secrets scanning to prevent future pipeline credential leaks.