Skip to main content
PreparationZone Logo
PrepZone

Database Security

Encryption at rest and in transit, RBAC, least privilege, and SQL injection prevention.

Pass checkpoint below

Why this matters

  • A leaked orders table export is a GDPR breach and a headline — encryption at rest is table stakes, not optional.
  • Over-privileged app credentials (GRANT ALL) let a single SQL injection drop tables; least privilege contains blast radius.
  • Senior interviews probe how you secure Postgres, Redis, and Elasticsearch together in a polyglot stack — not just "use SSL."
App writesINSERT / UPDATE
LeaderPrimary node
Replica 1Read traffic
Replica 2Read traffic
Writes go to the leader; replicas serve read traffic asynchronously.

Encryption: at rest and in transit

VaultCommerce encryption map

  • Postgres 16 (RDS) — AES-256 storage encryption enabled at cluster creation; KMS key rotation annually. TLS 1.2+ required on all connections (rds.force_ssl = 1).
  • Redis 7 (ElastiCache) — encryption at rest and in-transit enabled; AUTH token rotated via Secrets Manager.
  • Elasticsearch 8 — encrypted EBS volumes; HTTPS on port 9200; fine-grained access control via IAM or native users.
  • DynamoDB — AWS-managed keys by default; optional CMK for compliance workloads.
  • S3 backups — SSE-KMS with bucket policy denying unencrypted uploads.
Java
# Spring Boot — VaultCommerce order service (Postgres 16, TLS enforced)
spring:
  datasource:
    url: jdbc:postgresql://vaultcommerce-db.us-east-1.rds.amazonaws.com:5432/orders?sslmode=verify-full
    username: ${DB_USER}          # from Secrets Manager, not env files
    password: ${DB_PASSWORD}

Application-layer encryption for PCI fields: card tokens are never stored — only Stripe payment intent IDs in payments.stripe_intent_id.

RBAC and least privilege

VaultCommerce uses separate database roles per microservice — never a shared app_user with superuser access.

Java
-- Order service: read/write only its tables
CREATE ROLE order_svc LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE vaultcommerce TO order_svc;
GRANT SELECT, INSERT, UPDATE ON orders, order_items TO order_svc;
-- No DELETE, no DDL, no access to users or payments

-- Analytics reader: read-only on reporting views
CREATE ROLE analytics_reader LOGIN PASSWORD '...';
GRANT SELECT ON reporting.orders_daily TO analytics_reader;
AspectRoleVaultCommerce scope
order_svcorders, order_itemsCheckout and fulfillment API
catalog_svcproducts, categoriesProduct CRUD and inventory
search_indexerSELECT on productsES reindex worker only
migration_ciDDL via Flyway in CI onlySchema changes, not runtime
dba_breakglassSuperuser, MFA + ticketIncidents only, audited
  • order_svc

    Roleorders, order_items
    VaultCommerce scopeCheckout and fulfillment API
  • catalog_svc

    Roleproducts, categories
    VaultCommerce scopeProduct CRUD and inventory
  • search_indexer

    RoleSELECT on products
    VaultCommerce scopeES reindex worker only
  • migration_ci

    RoleDDL via Flyway in CI only
    VaultCommerce scopeSchema changes, not runtime
  • dba_breakglass

    RoleSuperuser, MFA + ticket
    VaultCommerce scopeIncidents only, audited

Break-glass accounts require MFA, a ticket reference, and post-use audit review.

The rest of this guide is part of Pro

Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.

Still to come · 3 sections

  • Redis 7 ACLs
  • SQL injection prevention
  • Network isolation

Test yourself

Answer these before moving on — recall is what makes it stick.