Search Knowledge

© 2026 LIBREUNI PROJECT

Database Systems/Data at scale and synthesis
Page 18 of 20

Database Security

Place authorization, input handling, secrets, auditing, and recovery controls at the boundaries where data can actually escape or be corrupted.

Lesson briefData at scale and synthesis · 7 h · 4 outcomes

Required knowledge

  • Relational keys, constraints, SQL predicates, and transaction boundaries
  • Basic web-service request flow, authentication, and threat-model vocabulary
  • Replication, backups, and failure-recovery objectives

Learning outcomes

  • Draw a database-facing data flow and identify the enforcement point for each protected datum and authority transition.
  • Distinguish parameter binding, identifier allowlists, least-privilege roles, and row-level policies by the attack they address.
  • Design tenant isolation that remains safe under malformed input, alternate query paths, and a compromised runtime credential.
  • Specify audit, secret-rotation, backup, and incident evidence requirements without mistaking encryption or replicas for complete security.

Assessment target

Given an unfamiliar multi-tenant data flow and attack path, identify the first bypassable control, redesign the authority and query boundary, and produce a testable containment and evidence plan.

Scope boundary

  • Cryptographic protocol design, operating-system isolation, privacy law, PCI compliance programs, and incident forensics are separate subjects.
  • Vendor-specific role syntax is illustrative; policy behavior must be verified against the chosen DBMS.

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

Code
actor "tenant user" as U
rectangle "API\nauthenticates subject + tenant" as API
rectangle "application role\nparameterized queries" as APP
database "primary database\nconstraints + row policy" as DB
database "replica / backup" as BK
rectangle "audit sink" as AUD
U --> API : request and credential
API --> APP : trusted tenant context
APP --> DB : bound parameters
DB --> BK : protected replication / backup
DB --> AUD : security-relevant events
note right of DB
Tenant identity must be
server-derived, not accepted
as a free query parameter.
end note
tenant userAPIauthenticates subject + tenantapplication roleparameterized queriesprimary databaseconstraints + row policyreplica / backupaudit sinkTenant identity must beserver-derived, not acceptedas a free query parameter.request and credentialtrusted tenant contextbound parametersprotected replication /backupsecurity-relevant events

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 solutionExpand

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

NeedCorrect boundaryCommon unsafe shortcut
value in a predicateprepared statement / bound parameterconcatenate quoted input
user-selectable sort columnfixed allowlist mapped to known SQL fragmentsbind an identifier as a value or concatenate it
tenant row accessserver-derived tenant predicate and/or verified row policytrust a browser-supplied tenant ID
migration changesseparate owner/migration rolelet runtime API role alter schema
operational investigationbounded support role plus audited procedureshare 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.

HintExpand

Separate SQL syntax handling from database authority.

Worked solutionExpand

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

0 / 4 answered

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.

1
The application role possesses destructive authority, allowing arbitrary table truncation if any secondary defect exists.
2
Binding literal parameters prevents query optimizers from utilizing index structures on the filtered table.
3
Parameter binding automatically constrains database roles to least privilege through dynamic query privilege evaluation.
4
Parameterized queries cannot bind dynamic table or column identifiers (e.g. ORDER BY), leaving identifier injection possible.

Tenant isolation needs layered tests

LayerEnforcesTest that can falsify it
application authorizationsubject may act for tenant 12change a URL/body tenant ID to 13
SQL query constructioninput cannot rewrite a predicateuse hostile value syntax; verify query shape remains fixed
relational designreferences do not cross tenant ownershipattempt a tenant-12 child pointing at tenant-13 parent
row-level policydatabase filters rows for runtime roleissue a direct query as that role with no application predicate
audit/monitoringsensitive access is discoverableperform 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

AssetPrimary controlRecovery / detection evidence
runtime database credentialsecret manager, rotation, narrow roleissuance/use record; revoke and validate replacement
customer rowsauthorization, policies, constraintsdenied-path tests; tenant-bound audit event
backup archiveindependent access, encryption/key custody, retentionisolated restore with access logging
change historyappend-oriented audit with protected writerscorrelation across application and DB events
query trafficTLS and certificate/endpoint validationmonitored 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.

HintExpand

Prioritize credential containment, revocation, and scope analysis before considering data restore.

Worked solutionExpand

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.

Section Exercises

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

Verificationapplication

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.

HintExpand

Parameter binding cannot be used for SQL identifiers such as column names or sort directions.

Worked solutionExpand

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

Descriptionrecall

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.

HintExpand

Differentiate application web traffic, background workers, analytics, and schema migrations.

Worked solutionExpand

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:

  1. app_user: Limited SELECT, INSERT, UPDATE, DELETE strictly on necessary application tables; no DDL permissions.
  2. app_readonly: SELECT permissions on reporting tables or views; used by analytics dashboards.
  3. migration_user: DDL privileges (CREATE, ALTER, DROP) used exclusively during CI/CD deployment pipelines.
  4. admin: Restricted administrative role reserved for DBA maintenance and emergency recovery.

Exercise 5.3.3: Row-Level Security Tenant Isolation Policy Definition

Constructionapplication

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.

HintExpand

Enable RLS on the table and define a policy using USING and WITH CHECK clauses.

Worked solutionExpand
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

Generalisationtransfer

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.

HintExpand

Order actions: immediate revocation, credential rotation, log inspection, blast-radius audit, and post-mortem.

Worked solutionExpand
  1. Immediate revocation: Terminate existing connection pool sessions and revoke or rotate the exposed database password immediately in secrets management.
  2. Log suppression: Purge the public build log from version control and CI caches, invalidating web share links while preserving forensic copies internally.
  3. 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.
  4. Data integrity verification: Run cryptographic checksums and domain invariant checks to detect unauthorized table modifications or exfiltration traces.
  5. Least-privilege redeployment: Issue new credentials with least-privilege role scoping, and implement CI secrets scanning to prevent future pipeline credential leaks.

References & Further Reading