Authentication, Authorisation, Encryption and SQL Injection

In one line

Authenticate every client with strong methods (SCRAM passwords from a secrets manager, IAM tokens, or client certificates). Authorise with least-privilege roles, row-level security and column privileges. Encrypt with TLS in transit and storage encryption at rest with managed, rotated keys. Prevent SQL injection with parameterised queries everywhere.

0/3 · 0%

Think of it like this

A bank vault. Staff badge in (authentication), each person's badge opens only certain rooms (authorisation), valuables travel in locked vans (TLS) and sit in locked safes (encryption at rest), and no clerk will act on a note slipped under the door that says "also open vault 7" (injection).

Key ideas

01
Authentication: scram-sha-256 in pg_hba.conf (never trust or md5 on networks), cloud IAM database auth (short-lived tokens), or mutual TLS certificates for services. Store credentials in a secrets manager (Vault, AWS Secrets Manager) with automatic rotation; never in code or images.
02
Least privilege: separate roles for migrations (DDL owner), the application (DML on its tables only), read-only analytics, and humans (read-only by default, break-glass elevation with approval and audit). Revoke CREATE on the public schema; set default privileges.
03
Row-level security (RLS): policies filter rows per session (USING (tenant_id = current_setting('app.tenant_id')::bigint)), enforced by the database even if an application query forgets the WHERE. Column-level: GRANT SELECT (id, name) ON customer TO support; or views that mask sensitive columns.
04
Encryption: TLS (sslmode=verify-full on clients, so the server certificate is verified, not just encrypted), encryption at rest via disk or volume encryption with KMS keys (all managed clouds), and application-level encryption for highly sensitive fields (card data, national IDs) so DBAs and backups see ciphertext. Rotate keys with envelope encryption.
05
SQL injection: concatenating user input into SQL lets attackers change the query. Always use bound parameters (JDBC PreparedStatement, ORM parameters); for dynamic identifiers (sort column), use an allow-list, never raw input. Detect with SAST, code review and database logs of syntax errors from app roles.

Code & diagrams

roles-rls.sqlsql

-- roles
CREATE ROLE app_rw LOGIN PASSWORD NULL;            -- password set via secrets manager / IAM
GRANT CONNECT ON DATABASE shop TO app_rw;
GRANT USAGE ON SCHEMA app TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

CREATE ROLE analyst NOLOGIN;
GRANT SELECT (id, created_at, total, status) ON app.orders TO analyst;   -- column-level

-- row-level security for tenants
ALTER TABLE app.project ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.project FORCE ROW LEVEL SECURITY;          -- applies to the table owner too
CREATE POLICY tenant_isolation ON app.project
  USING (tenant_id = current_setting('app.tenant_id')::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
-- per transaction in the app:  SET LOCAL app.tenant_id = '42';

Injection.javajava

// VULNERABLE: input becomes part of the SQL text
String sql = "SELECT * FROM users WHERE email = '" + email + "'";

// SAFE: input is sent separately as a bound parameter
try (PreparedStatement ps = conn.prepareStatement("SELECT id, name FROM users WHERE email = ?")) {
    ps.setString(1, email);
    ResultSet rs = ps.executeQuery();
}

// Dynamic ORDER BY: allow-list, never raw input
private static final Map<String, String> SORT = Map.of("newest", "created_at DESC", "price", "price ASC");
String orderBy = SORT.getOrDefault(requestedSort, "created_at DESC");

Interview problem

The problem

Harden a database after an audit

An audit found: the application connects as the database superuser with a password in a config file, TLS is optional, developers query production with shared credentials, and one legacy endpoint builds SQL by string concatenation. Produce a remediation plan.

When it breaks

RLS enabled but the application connects as the table owner or a BYPASSRLS role

What you see

Policies don't apply (owners bypass RLS unless FORCE is set); tenant isolation silently doesn't exist.

Fix & prevent

Connect as a non-owner role without BYPASSRLS, use FORCE ROW LEVEL SECURITY, and test cross-tenant access in CI.

Explain it without notes

Why do parameterised queries prevent SQL injection?

Practice

Write a policy so support staff can see customers only in their assigned country.

Trade-offs

↔
Stricter access and encryption add setup, rotation and debugging friction; the alternative is a breach with no containment.

Done when you can

I can set up least-privilege roles, RLS, TLS verification, secret rotation and injection-safe queries.

Command Palette