198: Database Security: Roles, GRANT/REVOKE, Row-Level Security, Injection, and Secrets

Learning outcomes

By the end of this lesson, you can:

explain and apply roles and ownership in a realistic implementation;
explain and apply grant and revoke in a realistic implementation;
explain and apply row-level security in a realistic implementation;
explain and apply sql injection in a realistic implementation;
explain and apply secrets and transport in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the 01–06 foundation and the earlier lessons in this module. Before you start, retrieve one concrete example from a previous project in which one of these concerns appeared. That retrieval step is useful because the goal is not to recite security vocabulary. The goal is to make a defensible decision in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency are connected.

Terminology

Roles and ownership: Use separate roles for owning database objects and running migrations, and for serving ordinary application requests. The runtime role should not inherit more authority than the request path needs.
GRANT and REVOKE: Grant only the privileges required on schemas, tables, sequences, and functions, and revoke privileges that are not part of the contract.
Row-level security: RLS attaches policies to individual rows. It can enforce tenant or ownership boundaries in the database as defense in depth, including when application code forgets a predicate.
SQL injection: Parameterize data values. For identifiers and sort expressions that cannot be bound as ordinary parameters, select from an explicit allowlist rather than concatenating unchecked input.
Secrets and transport: Put database credentials in a secret-management mechanism, rotate them, enforce TLS where it is required, and keep credentials out of client-side code, logs, and error messages.
Auditability: Record security-relevant database events at an appropriate layer, while excluding sensitive payloads from the audit trail.

Mental model

Treat Database Security: Roles, GRANT/REVOKE, Row-Level Security, Injection, and Secrets as a design problem with observable inputs, outputs, invariants, and failure modes. The security model should assume that credentials may be misused and that an application bug may bypass an intended code path. Least privilege and parameterized access therefore belong at multiple boundaries, not only in the route handler. A strong implementation makes assumptions visible, narrows uncertainty at each boundary, and leaves enough evidence—tests, types, constraints, metrics, or diagrams—to explain why the design is safe.

A useful sequence for both an interview answer and a production design is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement straight to a library call. First state what must remain true. Then choose the database and application mechanisms that enforce that invariant, and finally decide how you will observe a failure when the invariant is violated.

Deep dive

1. Roles and ownership

One of the most damaging shortcuts is using the schema owner or a superuser for ordinary application traffic. Separate the role that owns objects and runs migrations from the runtime application roles. The application should not connect as a superuser or schema owner for normal requests; otherwise a compromised credential or an authorization bug can expose far more than the endpoint intended to expose.

Decision rule: Use roles and ownership deliberately when they make the contract or invariant easier to prove. If they merely reduce typing while hiding an assumption about who can change or read an object, prefer the more explicit design.

2. GRANT and REVOKE

Privileges should describe what a role is allowed to do, not what happens to be convenient during development. Grant only the required privileges on schemas, tables, sequences, and functions, and revoke anything outside that set. Remember default privileges as well: if future objects receive broad access automatically, the security design is incomplete even if the current tables are configured correctly.

Decision rule: Use grant and revoke deliberately when they make the contract or invariant easier to prove. If they merely reduce typing while hiding an assumption about access, prefer the more explicit design.

3. Row-level security

Table-level permission answers whether a role may use a table; row-level security answers which rows that role may see or change. RLS policies can provide a tenant or ownership boundary in depth, but they are not a checkbox. You need to understand how session context is established, whether the table owner or a bypass-capable role can avoid the policies, how every relevant operation is covered, and how connection pooling prevents one request's tenant context from leaking into another.

Decision rule: Use row-level security deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption about session state or policy coverage, prefer the more explicit design.

4. SQL injection

SQL injection occurs when untrusted input changes the structure of a SQL statement instead of remaining data inside that statement. Bind values with parameters. Values such as a customer name or status belong in parameter placeholders; identifiers and sort expressions generally cannot be bound that way, so map the accepted input through an allowlist to a known-safe SQL fragment. An ORM or query builder can help, but it does not make dynamic SQL safe automatically if the application still concatenates unchecked strings.

Decision rule: Use sql injection deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

5. Secrets and transport

Database credentials are operational secrets, not application constants. Store them in a secret-management mechanism, rotate them with a plan for updating active connections, and enforce TLS when the deployment requires it. Never put credentials in client-side code, source control, logs, or error messages. Transport protection reduces the chance that credentials or query data are exposed in transit, but it does not replace authorization or least privilege.

Decision rule: Use secrets and transport deliberately when they make the contract or invariant easier to prove. If they only reduce typing while hiding an assumption, prefer the more explicit design.

6. Auditability

When a security incident is investigated, “the request failed” is rarely enough. At an appropriate layer, record the principal, operation, resource, time, and correlation context for security-relevant database events. Do not turn the audit log into a copy of the data: sensitive payloads and credentials do not belong there. The right audit boundary depends on what the database can observe and what the application must add, so make that division explicit.

Decision rule: Use auditability deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption about what can be reconstructed later, prefer the more explicit design.

Worked example

Consider a PostgreSQL-backed transactional application in which schema design, correctness, query plans, and concurrency all matter. Begin with a one-sentence requirement, then write down the input and output contracts and identify which concept above owns each failure mode. The useful separation is this: parsing and boundary validation belong at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; and presentation rules belong in the client. A shorter happy-path demo can mix these concerns, but that makes retries, malformed input, authorization gaps, and concurrent behavior much harder to reason about.

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

This query is intentionally ordinary: it gives us a small persistence example whose behavior can be inspected rather than guessed at. Walk through at least four cases: the normal path; an empty or missing value; a duplicate, retry, or concurrent path where that applies; and a dependency failure. For every case, state which layer detects the problem and what the caller observes. That is the level of reasoning expected in a senior code review or technical interview. Also inspect the query plan when performance is relevant, and keep authorization guarantees separate from the fact that the query happens to return the expected rows.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after evidence identifies a bottleneck or a security risk; a mechanism that looks scalable is not automatically the right mechanism without a scale requirement.

When the topic involves an external dependency, define both timeout and cancellation behavior. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. A client-side check can improve usability, but it cannot stand in for server-side authorization, validation, or database guarantees.

Guided lab

Create owner, migration, read-write app, and read-only reporting roles. Apply least privilege and tenant RLS to one table. Then prove that a cross-tenant query fails even when the application forgets to include a tenant predicate. The point of the lab is not just to make the happy path work; inspect the active role, session context, policy behavior, and failure observed by the caller.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Edge cases and failure modes

Roles and ownership: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Also test what happens if the runtime role is accidentally granted ownership-level authority.
GRANT and REVOKE: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include newly created objects so default privileges are exercised.
Row-level security: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Test missing, stale, and cross-tenant session context, as well as owner or bypass behavior.
SQL injection: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify that values remain parameters and that identifier or sort input is restricted to an allowlist.
Secrets and transport: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Check rotation, TLS configuration, logging, error responses, and client bundles for accidental disclosure.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern may be syntactically correct while still being architecturally wrong for the application's trust boundaries.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and discovering the actual contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case first. Inspect the actual value, active role, session context, logs, or execution plan as appropriate. Trace the boundary where the invariant first becomes false, then fix the layer that owns that invariant instead of adding a downstream patch. For a database issue, check the database or query boundary; for an authorization issue, compare the principal and effective privileges; for a performance issue, inspect the actual plan rather than inferring it from the SQL text.

Interview questions

What problem does Roles and ownership solve, and what trade-off or failure mode would make you choose a different approach?
What problem does GRANT and REVOKE solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Row-level security solve, and what trade-off or failure mode would make you choose a different approach?
What problem does SQL injection solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Secrets and transport solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Database Security: Roles, GRANT/REVOKE, Row-Level Security, Injection, and Secrets to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. As a final check, explain how your example would behave if the client were modified, a request were retried, or the database connection carried stale session context.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.