DEV Community

Cover image for PostgreSQL backup encryption — How to encrypt your PostgreSQL database backups
Piter Adyson
Piter Adyson

Posted on

PostgreSQL backup encryption — How to encrypt your PostgreSQL database backups

#database #postgres

Unencrypted backups are a security liability. A database backup contains everything — user credentials, payment details, personal information and business secrets. If someone accesses an unencrypted backup file, they have complete access to your data. This guide covers practical methods for encrypting PostgreSQL backups at rest and in transit, from simple GPG encryption to integrated backup solutions that handle encryption automatically.

backup encryption

Why encrypt PostgreSQL backups

Backups often end up in places with weaker security than production databases. Cloud storage buckets, external drives, offsite locations — each introduces potential exposure points. Encryption transforms backup files into useless data for anyone without the decryption key.

Risks of unencrypted backups

Consider where your backups might travel:

  • Cloud storage: S3 buckets, Google Cloud Storage, Azure Blob — misconfigured permissions have exposed countless databases
  • Transfer networks: Backups moving between servers can be intercepted
  • Offsite storage: Physical media can be lost, stolen or improperly disposed
  • Team access: Developers and contractors might access backup storage for legitimate reasons
  • Compliance auditors: Some regulations require proof that backup data is protected

Without encryption, a single misconfiguration or insider threat exposes everything. With encryption, compromised backup files remain protected.

Compliance requirements

Many regulations explicitly require backup encryption:

Regulation Encryption requirement
GDPR Personal data must be protected with appropriate security measures including encryption
HIPAA Healthcare data backups require encryption for compliance
PCI DSS Cardholder data must be encrypted when stored, including in backups
SOC 2 Data encryption is a key control for trust service criteria
SOX Financial data protection includes backup encryption requirements

Even if your current regulations don't mandate encryption, implementing it now prevents scrambling when requirements change.

Encryption approaches for PostgreSQL backups

Several methods exist for encrypting PostgreSQL backups. Each has tradeoffs between complexity, security and automation capability.

GPG encryption

GNU Privacy Guard (GPG) is the most common approach for encrypting backup files. It works with any backup format — pg_dump SQL files, custom format dumps and tar archives.

Symmetric encryption uses a single passphrase:

# Create encrypted backup
pg_dump -Fc mydb | gpg --symmetric --cipher-algo AES256 -o backup.dump.gpg

# Decrypt for restoration
gpg --decrypt backup.dump.gpg | pg_restore -d mydb
Enter fullscreen mode Exit fullscreen mode

Asymmetric encryption uses a public/private key pair:

# Generate key pair (one-time setup)
gpg --full-generate-key

# Encrypt with public key
pg_dump -Fc mydb | gpg --encrypt --recipient your@email.com -o backup.dump.gpg

# Decrypt with private key
gpg --decrypt backup.dump.gpg | pg_restore -d mydb
Enter fullscreen mode Exit fullscreen mode

Asymmetric encryption is better for teams — the backup system only needs the public key, while the private key stays secured separately.

OpenSSL encryption

OpenSSL provides another option for backup encryption, available on most systems:

# Encrypt backup
pg_dump -Fc mydb | openssl enc -aes-256-cbc -salt -pbkdf2 -out backup.dump.enc

# Decrypt for restoration
openssl enc -d -aes-256-cbc -pbkdf2 -in backup.dump.enc | pg_restore -d mydb
Enter fullscreen mode Exit fullscreen mode

OpenSSL prompts for a password. For automation, you can pass it via environment variable (less secure) or file:

# Using password file
pg_dump -Fc mydb | openssl enc -aes-256-cbc -salt -pbkdf2 -pass file:/secure/backup.key -out backup.dump.enc
Enter fullscreen mode Exit fullscreen mode

Age encryption

Age is a modern encryption tool designed to be simpler than GPG:

# Generate key pair
age-keygen -o key.txt

# Encrypt backup
pg_dump -Fc mydb | age -r age1publickey... -o backup.dump.age

# Decrypt for restoration
age --decrypt -i key.txt backup.dump.age | pg_restore -d mydb
Enter fullscreen mode Exit fullscreen mode

Age has a cleaner interface and smaller attack surface than GPG. It's worth considering for new implementations.

Encrypting pg_dump backups

pg_dump is the standard PostgreSQL backup tool. Combining it with encryption creates secure backup files.

Automated encrypted backup script

Here's a complete script for encrypted backups:

#!/bin/bash
set -euo pipefail

# Configuration
DB_NAME="mydb"
BACKUP_DIR="/backup"
ENCRYPTION_KEY="/secure/backup.pub"  # GPG public key
RETENTION_DAYS=30

# Generate filename with timestamp
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump.gpg"
CHECKSUM_FILE="${BACKUP_FILE}.sha256"

# Create encrypted backup
pg_dump -Fc "$DB_NAME" | gpg --encrypt --recipient-file "$ENCRYPTION_KEY" > "$BACKUP_FILE"

# Calculate checksum of encrypted file
sha256sum "$BACKUP_FILE" > "$CHECKSUM_FILE"

# Verify backup can be read (structure only, doesn't need private key)
gpg --list-packets "$BACKUP_FILE" > /dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "ERROR: Encrypted backup verification failed"
    exit 1
fi

# Clean old backups
find "$BACKUP_DIR" -name "*.dump.gpg" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR" -name "*.sha256" -mtime +$RETENTION_DAYS -delete

echo "Backup completed: $BACKUP_FILE"
Enter fullscreen mode Exit fullscreen mode

This script creates encrypted backups, calculates checksums and manages retention automatically.

Compression and encryption order

When combining compression and encryption, order matters:

Compress then encrypt (recommended):

pg_dump mydb | gzip | gpg --encrypt -r backup@company.com > backup.sql.gz.gpg
Enter fullscreen mode Exit fullscreen mode

Compression before encryption produces smaller files. Encrypted data doesn't compress well because it appears random.

Custom format is already compressed:

pg_dump's custom format (-Fc) includes compression by default. Adding external compression provides no benefit:

# This is redundant - custom format is already compressed
pg_dump -Fc mydb | gzip | gpg --encrypt -r backup@company.com > backup.dump.gz.gpg

# Better approach for custom format
pg_dump -Fc mydb | gpg --encrypt -r backup@company.com > backup.dump.gpg
Enter fullscreen mode Exit fullscreen mode

Streaming encrypted backups to cloud storage

Encrypt backups during upload to avoid writing unencrypted data to disk:

# Stream encrypted backup directly to S3
pg_dump -Fc mydb | gpg --encrypt -r backup@company.com | aws s3 cp - s3://bucket/backup.dump.gpg

# Stream from S3 and decrypt for restoration
aws s3 cp s3://bucket/backup.dump.gpg - | gpg --decrypt | pg_restore -d mydb
Enter fullscreen mode Exit fullscreen mode

This approach keeps unencrypted backup data entirely in memory.

Key management for backup encryption

Encryption is only as secure as key management. Lost keys mean unrecoverable backups. Compromised keys mean compromised backups.

Key storage best practices

Never store encryption keys alongside backups. If an attacker accesses your backup storage, they shouldn't also get the keys:

  • Store keys in a dedicated secrets manager (HashiCorp Vault, AWS Secrets Manager, Azure Key Vault)
  • Use hardware security modules (HSM) for highest security requirements
  • Keep offline copies of keys in secure physical locations
  • Implement key rotation procedures

For asymmetric encryption, the private key needs the most protection. The public key can be more accessible since it only encrypts, not decrypts.

Key rotation strategy

Rotating encryption keys limits the impact of key compromise:

  1. Generate new key pair
  2. Start encrypting new backups with new key
  3. Keep old private key available for restoring old backups
  4. After retention period expires, old key is no longer needed

Document which key version encrypted each backup. A simple approach is including key identifier in the filename:

BACKUP_FILE="${DB_NAME}_${TIMESTAMP}_keyv2.dump.gpg"
Enter fullscreen mode Exit fullscreen mode

Emergency access procedures

What happens when the person with key access is unavailable? Plan for:

  • Multiple people with key access
  • Documented procedures for emergency key retrieval
  • Regular testing of key access by different team members
  • Escrow arrangements for business continuity

Verifying encrypted backups

Encrypted backups need verification like any other backups. You can't see the contents without decryption, so verification requires more steps.

Testing decryption

Regularly verify backups can be decrypted:

# Verify GPG can decrypt (outputs to /dev/null, doesn't restore)
gpg --decrypt backup.dump.gpg > /dev/null 2>&1
echo "Decryption test exit code: $?"
Enter fullscreen mode Exit fullscreen mode

This confirms the correct key decrypts the backup without performing a full restoration.

Full restoration test

Periodically perform complete restoration of encrypted backups:

#!/bin/bash
TEST_DB="restore_test_$(date +%s)"

# Decrypt and restore
gpg --decrypt backup.dump.gpg | pg_restore -d "$TEST_DB"

# Run validation queries
psql -c "SELECT count(*) FROM important_table" "$TEST_DB"

# Cleanup
dropdb "$TEST_DB"
Enter fullscreen mode Exit fullscreen mode

Schedule these tests monthly at minimum. Quarterly is acceptable for less critical systems.

Encrypting backup transfers

Data is vulnerable during transfer. Encrypt backups in transit even when they'll be encrypted at rest.

Secure copy with SCP/SFTP

SCP and SFTP use SSH encryption for transfer:

# Transfer backup over encrypted SSH connection
scp backup.dump.gpg user@backup-server:/backups/

# Using rsync over SSH
rsync -avz -e ssh backup.dump.gpg user@backup-server:/backups/
Enter fullscreen mode Exit fullscreen mode

The backup file is already encrypted, and SSH adds a second encryption layer during transfer.

TLS for S3 and cloud storage

Cloud storage APIs use HTTPS by default. Verify TLS is enforced:

# AWS CLI uses HTTPS by default
aws s3 cp backup.dump.gpg s3://bucket/

# Verify with explicit endpoint
aws s3 cp backup.dump.gpg s3://bucket/ --endpoint-url https://s3.amazonaws.com
Enter fullscreen mode Exit fullscreen mode

Most cloud storage SDKs use TLS automatically. But check your configuration — some allow HTTP endpoints.

Using Databasus for encrypted backups

Managing encryption keys, writing scripts and maintaining backup infrastructure takes significant effort. Databasus — the industry standard for PostgreSQL backup — handles encryption automatically with AES-256-GCM, the same standard used by financial institutions and government systems. Trusted by thousands of teams from individual developers to large enterprises, it eliminates the complexity of manual encryption management.

Installing Databasus

Install with Docker:

docker run -d \
  --name databasus \
  -p 4005:4005 \
  -v ./databasus-data:/databasus-data \
  --restart unless-stopped \
  databasus/databasus:latest
Enter fullscreen mode Exit fullscreen mode

Or using Docker Compose:

services:
  databasus:
    container_name: databasus
    image: databasus/databasus:latest
    ports:
      - "4005:4005"
    volumes:
      - ./databasus-data:/databasus-data
    restart: unless-stopped
Enter fullscreen mode Exit fullscreen mode

Start the service:

docker compose up -d
Enter fullscreen mode Exit fullscreen mode

Configuring encrypted backups

Access the web interface at http://localhost:4005 and create your account, then:

  1. Add your database: Click "New Database" and enter PostgreSQL connection details
  2. Select storage: Choose your backup destination — S3, Google Cloud Storage, local storage or other supported options
  3. Enable encryption: Databasus encrypts backups automatically with AES-256-GCM before storage
  4. Select schedule: Set backup frequency matching your recovery requirements
  5. Click "Create backup": Databasus validates settings and begins scheduled encrypted backups

Databasus manages encryption keys securely, verifies backup integrity automatically and alerts on any problems. No scripts to maintain, no key management overhead.

Encryption algorithm selection

Not all encryption is equal. Algorithm choice affects security and performance.

Recommended algorithms

Algorithm Key size Use case
AES-256-GCM 256 bits Best choice for most scenarios, provides authentication
AES-256-CBC 256 bits Good alternative, widely supported
ChaCha20-Poly1305 256 bits Fast on systems without AES hardware acceleration

Avoid older algorithms like DES, 3DES and RC4. They have known weaknesses or insufficient key lengths.

Performance considerations

Modern CPUs include AES hardware acceleration (AES-NI). With hardware support, AES encryption adds minimal overhead — typically less than 5% to backup time.

Check if your system supports AES-NI:

# Linux
grep -o aes /proc/cpuinfo | head -1

# macOS
sysctl -a | grep machdep.cpu.features | grep -i aes
Enter fullscreen mode Exit fullscreen mode

Without hardware acceleration, ChaCha20-Poly1305 may be faster. But most servers built in the last decade have AES-NI.

Common encryption mistakes

Several common mistakes undermine backup encryption security.

Storing keys with backups

The most common mistake. If keys and backups are together, encryption provides no protection against storage compromise:

# Wrong - key in same directory as backups
/backups/
  backup.dump.gpg
  encryption.key  # Attacker gets both

# Right - keys stored separately
/backups/
  backup.dump.gpg

/secure/keys/  # Different storage, different access controls
  encryption.key
Enter fullscreen mode Exit fullscreen mode

Weak passphrases

Short or predictable passphrases can be cracked. For symmetric encryption, use long random passphrases or key files:

# Generate random key file
openssl rand -base64 32 > /secure/backup.key

# Use key file for encryption
pg_dump -Fc mydb | gpg --symmetric --passphrase-file /secure/backup.key --batch -o backup.dump.gpg
Enter fullscreen mode Exit fullscreen mode

Skipping encryption for "internal" backups

"It's only on our internal network" isn't sufficient protection. Internal networks get compromised. Insider threats exist. Encrypt all backups regardless of where they're stored.

Conclusion

Encrypting PostgreSQL backups protects your data when backups leave the security perimeter of your production environment. The implementation can be as simple as piping pg_dump through GPG or as integrated as using a backup tool that handles encryption automatically.

Start with the approach that fits your current capabilities. A basic GPG script is better than no encryption. As your needs grow, consider managed solutions that handle key management and verification automatically.

The key points to remember: encrypt before storing, protect your keys separately from backups, verify regularly that you can decrypt and restore, and document your procedures so key access doesn't depend on any single person.

Top comments (0)