Encryption at Rest and in Transit: Getting It Right

You’ve deployed firewalls, locked down user access, and hardened your configuration—yet your data is still one misrouted packet or stolen backup tape away from exposure. Encryption at rest and in transit is the last line of defense that turns stolen bytes into useless noise. Getting it right means understanding not just which knobs to turn, but why each layer matters and how to avoid the common pitfalls that leave gaps in your protection.

Why Encryption Matters: The Threat Landscape

Modern threats don’t always come through the front door. Attackers increasingly target data in motion—sniffing network traffic on compromised switches or exploiting misconfigured cloud VPC peering—and data at rest, by stealing physical disks, cloud snapshots, or backup files. Regulatory frameworks like PCI DSS, HIPAA, and GDPR now mandate encryption as a baseline control. Without it, a single intercepted query or exfiltrated database file can expose millions of records. Encryption is not optional; it is the price of admission for any production database.

Encryption in Transit: TLS, mTLS, and Certificate Management

Encryption in transit ensures that data moving between your application, database, and replicas cannot be read or tampered with. The standard is TLS 1.2 or higher. Most databases support TLS natively: MySQL with --require-secure-transport, PostgreSQL with ssl = on, and MongoDB with net.tls.mode set to requireTLS. For stronger authentication, mutual TLS (mTLS) requires both client and server to present certificates, preventing man-in-the-middle attacks even if a certificate authority is compromised.

Certificate management is where most implementations fail. Use short-lived certificates (90 days or less) and automate rotation with tools like cert-manager or HashiCorp Vault. Never disable hostname verification in production—sslverify=0 or tlsAllowInvalidHostnames=true defeats the purpose. Monitor certificate expiry and set alerts at 30, 14, and 7 days before expiration.

Encryption at Rest: TDE, Filesystem, and Application-Level Approaches

Encryption at rest protects data when it is stored on disk, in backups, or in snapshots. Three common approaches exist:

• Transparent Data Encryption (TDE): The database engine encrypts data files automatically. MySQL uses InnoDB tablespace encryption with ALTER INSTANCE ROTATE INNODB MASTER KEY. PostgreSQL supports TDE via extensions like pg_tde or cloud-specific implementations. MongoDB offers encryption at rest via WiredTiger storage engine configuration.
• Filesystem-level encryption: Tools like LUKS or dm-crypt encrypt the entire block device. This is simple and engine-agnostic but does not protect against OS-level access or database process memory dumps.
• Application-level encryption: The application encrypts sensitive columns before sending data to the database. This gives you fine-grained control but breaks indexing, sorting, and searching on encrypted fields unless you use deterministic encryption with careful key management.

Choose TDE for broad protection with minimal application changes, filesystem encryption for backup and snapshot security, and application-level encryption for compliance with strict data segregation requirements.

Key Management: The Critical Component

Encryption is only as strong as the key management behind it. Hardcoded keys in configuration files or environment variables are a common disaster waiting to happen. Use a dedicated key management service (KMS) like AWS KMS, Azure Key Vault, or HashiCorp Vault. For on-premises deployments, a hardware security module (HSM) provides physical key protection.

Rule of thumb: Never store encryption keys on the same server as the encrypted data. If an attacker gains root access to the database host, they should not also gain access to the keys.

Implement key rotation policies—rotate master keys at least annually and data encryption keys more frequently for high-sensitivity data. Always test key rotation in a staging environment first. A failed rotation can render your database unreadable.

Performance Impact and How to Mitigate It

Encryption adds CPU overhead for cryptographic operations and can increase query latency, especially on write-heavy workloads. TLS handshakes add 1–3 round trips per connection. TDE adds 5–15% CPU overhead on typical workloads. Mitigation strategies include:

• Use hardware-accelerated encryption (AES-NI) on modern CPUs. Most cloud instances and modern servers support this natively.
• For TLS, enable session resumption (tls-ticket-key in MySQL, ssl_session_cache in PostgreSQL) to reduce handshake overhead.
• For TDE, use dedicated encryption keys per tablespace to allow parallel encryption operations.
• Benchmark your workload before and after enabling encryption. If overhead exceeds 20%, consider upgrading CPU or offloading encryption to a dedicated appliance.

Example: Enabling TLS in PostgreSQL with session caching:

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'
ssl_session_cache_size = 1000
ssl_session_cache_mode = 'ticket'

Cross-Engine Implementation: MySQL, PostgreSQL, and MongoDB

Each engine has its own syntax and quirks. Here is a concrete example for MySQL TDE:

-- Enable InnoDB tablespace encryption
SET GLOBAL innodb_encrypt_tables = ON;
SET GLOBAL innodb_encrypt_online_alter_tables = ON;
ALTER INSTANCE ROTATE INNODB MASTER KEY;

-- Create an encrypted tablespace
CREATE TABLESPACE encrypted_ts ADD DATAFILE 'encrypted_ts.ibd' ENCRYPTION='Y';
ALTER TABLE customers TABLESPACE encrypted_ts;

For PostgreSQL, use the pg_tde extension (available in some distributions):

-- Load extension and set key provider
CREATE EXTENSION pg_tde;
SELECT pg_tde_set_master_key('my-master-key', 'provider=file', 'file_path=/etc/pg_tde/keyfile');

-- Create encrypted table
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    ssn TEXT ENCRYPTED WITH (encryption_type = 'deterministic')
);

For MongoDB, enable encryption at rest via the configuration file:

# mongod.conf
security:
  enableEncryption: true
  encryptionKeyFile: /etc/mongodb/encryption-key
  encryptionCipherMode: AES256-CBC

Auditing and Compliance: Verifying Your Encryption Is Working

Encryption is not a set-and-forget configuration. Regular auditing ensures it remains active and correct. Use database-native audit logs to confirm TLS connections are enforced:

-- MySQL: Check TLS status for current connections
SELECT * FROM performance_schema.session_status WHERE VARIABLE_NAME IN ('Ssl_cipher', 'Ssl_version');

For PostgreSQL, query pg_stat_ssl:

SELECT ssl, sslversion, sslcipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();

For at-rest encryption, verify that data files are encrypted on disk using OS-level tools like strings or hexdump—encrypted files should show no readable data. Automate compliance checks with scripts that parse database configuration and alert on any deviation from your encryption policy. Schedule quarterly penetration tests that attempt to read raw data files or sniff network traffic.

The short version

Encrypt everything in transit with TLS 1.2+ and mutual authentication where possible. Encrypt at rest using TDE for broad protection, filesystem encryption for backups, and application-level encryption for sensitive columns. Store keys in a dedicated KMS or HSM, rotate them regularly, and never co-locate keys with data. Measure performance before and after enabling encryption, and mitigate overhead with hardware acceleration and session caching. Finally, audit your encryption configuration continuously—trust, but verify.