Database Security

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

Expert10 min read

Read these first

Backup and Disaster Recovery

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;

Aspect	Role	VaultCommerce scope
order_svc	orders, order_items	Checkout and fulfillment API
catalog_svc	products, categories	Product CRUD and inventory
search_indexer	SELECT on products	ES reindex worker only
migration_ci	DDL via Flyway in CI only	Schema changes, not runtime
dba_breakglass	Superuser, MFA + ticket	Incidents 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.

Redis 7 ACLs

Java

# redis.conf — ACL file
user order_svc on >token_from_vault ~cart:* ~session:* +@read +@write -@dangerous
user default off

Disable FLUSHALL, CONFIG, and DEBUG for application users. VaultCommerce blocks the default user entirely.

SQL injection prevention

Every query path uses parameterized statements — no string concatenation.

Java

// Safe — JPA/JPQL or named parameters
@Query("SELECT o FROM Order o WHERE o.userId = :userId AND o.status = :status")
List<Order> findByUserAndStatus(@Param("userId") UUID userId, @Param("status") String status);

Java

// Unsafe — never do this
String sql = "SELECT * FROM orders WHERE user_id = '" + userId + "'";

Defence layers: parameterized queries, ORM by default, WAF SQLi rules at the API gateway, and pg_stat_statements monitoring for anomalous query patterns.

Network isolation

Databases live in private subnets — no public IP, no 0.0.0.0/0 security groups.
App pods reach Postgres/Redis only through security group rules scoped to the EKS node SG.
Elasticsearch behind VPC endpoint; Kibana access via VPN or SSO proxy.
Audit logging: pgaudit on Postgres for DDL and role changes; CloudTrail for RDS API calls.

Quick recall

Everything you need if you only revisit this box.

Encrypt at rest (KMS) and in transit (TLS) on every engine — Postgres, Redis, ES, DynamoDB, S3 backups.
One role per microservice with table-scoped grants; no shared superuser at runtime.
Redis 7 ACLs restrict commands and key patterns; disable the default user.
SQL injection: parameterized queries only; WAF and query monitoring as secondary layers.
Private subnets, security groups, and audit logs (pgaudit, CloudTrail) close the network and accountability loop.

Test yourself

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