Demo Database Security Audit

This lesson walks through a database security audit for an Express login demo application. The objective is to inspect every database interaction, surface security gaps, and produce prioritized remediation steps so the app can be safely promoted to production. Initial CLI session used to launch the auditing tool:

jeremy@MACSTUDIO Express-login-demo % claude

* Welcome to Claude Code!

/help for help, /status for your current setup

cwd: /Users/jeremy/Repos/Claude Code Course/Express-login-demo

> Try "how does <filepath> work?"

? for shortcuts

In INPUT_VALIDATION_SECURITY_REPORT.md

We follow a structured checklist covering parameterized queries, secret management, permissions, encryption, PII controls, timeouts/pools, and audit/logging.

A dark-themed Visual Studio Code window with a file explorer on the left and a central terminal/editor pane showing a database security checklist (parameterized queries, connection string security, user permissions, encryption, PII handling, timeouts, connection pool settings, etc.). The project file tree and a highlighted security report file are visible in the sidebar.

High-level audit focus areas

Parameterized queries / safe ORM usage to prevent SQL/NoSQL injection
Connection string protection, TLS/SSL, and secret management
Database user permissions (principle of least privilege)
Encryption at rest and TLS in transit
PII handling, retention, and logging hygiene
Query timeouts, connection-pool limits, and transaction discipline
Audit logging for sensitive operations and schema changes
NoSQL injection hardening and row/tenant isolation for multi-tenant apps

Principle of least privilege: give each DB account only the privileges it needs — separate read-only, migration, and admin accounts; avoid SUPERUSER-level credentials unless explicitly required. We verify that critical fields are minimized, encrypted or tokenized where needed, and that PII is redacted from logs. Example checklist items (condensed):

Verify parameterized queries or ORM APIs are used instead of concatenating user input.
Ensure connection strings and credentials are stored in a secrets manager and rotated.
Confirm DB connections use TLS/SSL (validate server certificate).
Validate query timeouts and pool limits are configured at driver and server levels.
Confirm audit logging for schema and privilege changes, failed logins, and sensitive table access.
Enforce row-level security (or server-side tenant scoping) for multi-tenant data isolation.

A Visual Studio Code window with the explorer on the left and a text file open in the main pane. The file shows a numbered security checklist covering items like audit logging, NoSQL injection hardening, row/tenant isolation, TLS, secret management, and schema controls.

Row / tenant isolation

Enforce server-side row-level security (e.g., Postgres RLS: https://www.postgresql.org/docs/current/ddl-rowsecurity.html) or strict ownership/tenant filters in queries.
Never rely on client-side filtering for multi-tenant isolation — always scope queries server-side.

NoSQL sanitization (Mongoose example)

For MongoDB/Mongoose, avoid passing raw user input directly into query filters:

// For Mongoose, enable sanitizeFilter to avoid passing raw user input into query filters
const mongoose = require('mongoose');
mongoose.set('sanitizeFilter', true);

See Mongoose migration docs for sanitizeFilter: https://mongoosejs.com/docs/migration.html#sanitizeFilter

TLS / secret management and schema controls to check

DB connections must use TLS/SSL (for Postgres: use sslmode=require or verify-full; see Postgres libpq SSL mode docs: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE).
Store credentials in a secrets manager (e.g., AWS Secrets Manager: https://aws.amazon.com/secrets-manager/, HashiCorp Vault: https://www.vaultproject.io/) and rotate them periodically.
Do not commit plaintext passwords in .env. Keep a .env.example for local development only. (See the dotenv package: https://www.npmjs.com/package/dotenv)
Enforce foreign keys, unique constraints, and NOT NULL where appropriate.
Avoid SELECT * — always fetch only the required fields.

Example: enabling SSL/TLS in node-postgres Pool (illustrative)

// javascript
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT || 5432),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  // Connection pool limits and timeouts
  max: Number(process.env.DB_POOL_MAX || 20),
  idleTimeoutMillis: Number(process.env.DB_IDLE_TIMEOUT || 30000),
  connectionTimeoutMillis: Number(process.env.DB_CONN_TIMEOUT || 5000),
  // TLS options (for production)
  ssl: {
    rejectUnauthorized: true, // ensure server certificate is validated
    // ca: process.env.DB_SSL_CA, // if using a custom CA
  },
});

See node-postgres pooling docs: https://node-postgres.com/features/pooling

A dark-themed Visual Studio Code window showing a project explorer on the left and an editor/terminal pane with a security-audit checklist for an Express app in the center. The text highlights critical flags (e.g., direct string concatenation) and lists a structured findings/report template.

Critical flags to watch for

Direct string concatenation with user input in SQL or NoSQL queries — extremely high severity.
Passing user JSON directly into NoSQL query filters without sanitization (see OWASP NoSQL Injection: https://owasp.org/www-community/attacks/NoSQL_Injection).
Hard-coded credentials or placeholder secrets committed to the repository (e.g., JWT_SECRET=your_jwt_secret_key_here, DB_PASSWORD=your_db_password).

Useful audit commands (examples)

Search for environment files and secrets:

# search for env files
find /Users/jeremy/Repos/Claude\ Code\ Course/Express-login-demo -name "*.env*" -o -name ".env*" 2>/dev/null

Check for console logging (may leak PII):

# grep for console logging patterns
grep -r "console\.log\|console\.error" /Users/jeremy/Repos/Claude\ Code\ Course/Express-login-demo/ --include="*.js" --exclude-dir=node_modules

Generate a bcrypt test hash:

# bash
node -e "console.log(require('bcrypt').hashSync('password123', 12))"
# Use the output in test data; use cost factor 12+ for production

See bcrypt: https://www.npmjs.com/package/bcrypt

Audit findings (executive summary)

Strengths: parameterized queries, bcrypt password hashing, and input validation (express-validator) are implemented.
Critical gaps: no TLS/SSL for DB connections, placeholder secrets in .env, missing connection pool limits/timeouts, and no centralized audit logging.
Risk Score (current): 6.5 / 10 (Medium-High). After remediation, target ~2.5 / 10 (Low Risk).

Example critical finding (evidence & remediation)

CRITICAL: No TLS/SSL Encryption in Transit
- Evidence (example): config/database.js:3-9 — Pool configuration missing SSL settings.
- Impact: Cleartext DB credentials and payloads on the network (CWE-319: https://cwe.mitre.org/data/definitions/319.html).
- Priority fixes:
  1. Enable TLS/SSL for DB connections and validate certificates.
  2. Remove .env from git and add .env.example.
  3. Replace placeholder secrets with strong values stored in a secrets manager.
  4. Add pool limits, timeouts, and resource caps.

Security checklist assessment

Resource / Control	Status	Notes
Parameterized queries	Pass	Queries use placeholders like `$1`
Connection string security (TLS/Secrets)	Fail	No SSL and weak defaults detected
Database user permissions	Unable to verify	Requires DB server access
Encryption at rest	Unable to verify	Requires DB server access
PII handling	Partial	No retention/deletion policy visible
Query timeouts	Fail	No application-level timeouts configured
Connection pool settings	Fail	Missing limits/idle timeouts
Transaction handling	Partial / N/A	Mostly single-query flows
Audit logging	Fail	No centralized immutable logs observed
Row/tenant isolation	Fail	No RLS or server-side tenant scoping
Secret management	Fail	Placeholder secrets committed to repo
Schema integrity	Pass	Foreign keys/constraints present
Field minimization	Pass	App selects only required fields
Backup/restore security	Unable to verify	Requires infra access
Migration safety	Unable to verify	No migration framework found
ORM raw-query review	Pass	Raw queries parameterized

Priority remediation plan (recommended timeline)

Timeline	Actions (high-level)
Immediate (Week 1)	Enable TLS/SSL and validate certs; remove `.env` from VCS and add `.env.example`; issue strong production secrets and move to a secrets manager; configure connection pool limits and timeouts.
Short-term (Month 1)	Implement centralized audit logging for auth and schema changes; enforce application-level query timeouts; sanitize error logs; regenerate test password hashes with proper bcrypt cost factor.
Medium-term (Months 2–3)	Adopt a secrets manager (HashiCorp Vault or AWS Secrets Manager); add DB monitoring, alerting, anomaly detection; implement retention/deletion policies for PII; add row-level security for multi-tenant use cases.

Risk score justification (summary)

Missing TLS and credential management are the largest contributors to risk (+3).
Good fundamentals (parameterized queries, hashing, validation) reduce risk (-2).
Missing enterprise controls (audit, timeouts, monitoring) add risk (+1).
Completing immediate remediation should significantly lower overall risk.

Conclusion & recommendations

The app uses solid fundamentals against injection and password attacks, but it is not production-ready as-is.
Complete immediate remediation items (TLS, secrets, pool/timeouts, logging hygiene) before production deployment.
Audit every place that constructs queries — a single unsafe concatenation can be catastrophic.
Enforce server-side tenant scoping for multi-tenant apps and ensure backups/exports scrub or encrypt PII.

Do not commit secrets or placeholder credentials to source control. Replace them with values stored in a proper secrets manager and use .env.example for local setup instructions.

Parameterized queries and input validation significantly reduce injection risk — maintain these practices while hardening transport, secrets, and logging layers.

Next: API and infrastructure security auditing — focus on authentication flows, token handling, rate limiting, and network-level protections (VPCs, security groups, firewall rules). References and further reading:

Postgres row-level security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Postgres libpq SSL modes: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE
Mongoose sanitizeFilter migration: https://mongoosejs.com/docs/migration.html#sanitizeFilter
node-postgres pooling: https://node-postgres.com/features/pooling
AWS Secrets Manager: https://aws.amazon.com/secrets-manager/
HashiCorp Vault: https://www.vaultproject.io/
OWASP NoSQL Injection: https://owasp.org/www-community/attacks/NoSQL_Injection
CWE-319: Cleartext Transmission of Sensitive Information: https://cwe.mitre.org/data/definitions/319.html

Introduction

Getting Started with Claude Code

Working with Claude Code

Code Review with Claude Code

Security Auditing with Claude Code

Advanced Features

Demo Database Security Audit

High-level audit focus areas

Row / tenant isolation

NoSQL sanitization (Mongoose example)

TLS / secret management and schema controls to check

Example: enabling SSL/TLS in node-postgres Pool (illustrative)

Critical flags to watch for

Useful audit commands (examples)

Audit findings (executive summary)

Example critical finding (evidence & remediation)

Security checklist assessment

Priority remediation plan (recommended timeline)

Risk score justification (summary)

Conclusion & recommendations

Watch Video

​High-level audit focus areas

​Row / tenant isolation

​NoSQL sanitization (Mongoose example)

​TLS / secret management and schema controls to check

​Example: enabling SSL/TLS in node-postgres Pool (illustrative)

​Critical flags to watch for

​Useful audit commands (examples)

​Audit findings (executive summary)

​Example critical finding (evidence & remediation)

​Security checklist assessment

​Priority remediation plan (recommended timeline)

​Risk score justification (summary)

​Conclusion & recommendations

Watch Video

High-level audit focus areas

Row / tenant isolation

NoSQL sanitization (Mongoose example)

TLS / secret management and schema controls to check

Example: enabling SSL/TLS in node-postgres Pool (illustrative)

Critical flags to watch for

Useful audit commands (examples)

Audit findings (executive summary)

Example critical finding (evidence & remediation)

Security checklist assessment

Priority remediation plan (recommended timeline)

Risk score justification (summary)

Conclusion & recommendations