How to Enable Encryption at Rest in PostgreSQL

How do I enable encryption at rest in PostgreSQL?

Encryption at rest protects PostgreSQL data files or individual columns so disk-level theft or snapshot leaks reveal only ciphertext.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why encrypt data at rest in PostgreSQL?

Sensitive columns like customer emails and order totals sit on disk in plain text by default. Disk theft, mis-configured backups, or cloud snapshot leaks can expose that data. Encrypting at rest renders stolen files useless.

What options exist for PostgreSQL?

PostgreSQL 16 lacks native Transparent Data Encryption (TDE). Most teams either ① encrypt the whole volume (LUKS, EBS-KMS, Azure Disk Encryption) or ② encrypt specific columns with pgcrypto.The latter gives row-level granularity without kernel support.

When should I choose column encryption?

Pick column encryption when only a few fields (credit cards, emails) require protection and you still need online index/partial search on non-encrypted columns. Full-disk encryption suffices for broad, coarse protection.

How does pgcrypto column encryption work?

pgcrypto adds functions like pgp_sym_encrypt() and pgp_sym_decrypt(). You store ciphertext (bytea) and decrypt only when needed.Keys can live in env variables, KMS, or separate key tables with tight ACLs.

Is performance acceptable?

Symmetric encryption adds CPU overhead proportional to row size. Indexes on encrypted columns are useless, so keep searchable data in plaintext or add hashed helper columns.

How do I rotate keys?

Create a new key, re-encrypt rows in batches inside a transaction, then revoke access to the old key.Using KMS-generated data keys simplifies rotation to a single update.

Step-by-step: encrypting customer emails

1. Install pgcrypto extension. 2. Add a email_enc bytea column. 3. Populate with UPDATE using pgp_sym_encrypt(). 4. Remove or null the plaintext column. 5. Decrypt in views or app queries.

Best practices

• Keep keys outside the database process. • Use strong AES cipher (default 256-bit in pgcrypto). • Limit decryption to roles that truly need it.• Automate key rotation and audit decrypt usage.

Galaxy tip

Store encryption helper queries in a Galaxy Collection, endorse them once reviewed, and let the AI copilot generate rotation scripts whenever the schema changes.

Why How to Enable Encryption at Rest in PostgreSQL is important

How to Enable Encryption at Rest in PostgreSQL Example Usage


-- Encrypt all historical order totals with a new key and store hash for lookup
ALTER TABLE Orders ADD COLUMN total_enc bytea;
ALTER TABLE Orders ADD COLUMN total_hash text;

UPDATE Orders
SET total_enc  = pgp_sym_encrypt(total_amount::text, :orders_key),
    total_hash = encode(digest(total_amount::text, 'sha256'), 'hex');

How to Enable Encryption at Rest in PostgreSQL Syntax


-- Enable extension (superuser once)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Encrypt column data
UPDATE Customers
SET email_enc = pgp_sym_encrypt(email::text, :master_key, 'cipher-algo=aes256')
WHERE email_enc IS NULL;

-- Decrypt on demand
SELECT id,
       pgp_sym_decrypt(email_enc, :master_key) AS email
FROM Customers
WHERE id = 42;

-- Insert with encryption
INSERT INTO Customers (name, email_enc, created_at)
VALUES ('Nadia', pgp_sym_encrypt('nadia@shop.io', :master_key), NOW());

-- Example joining encrypted and regular tables remains unchanged
SELECT o.id, o.total_amount,
       pgp_sym_decrypt(c.email_enc, :master_key) AS customer_email
FROM Orders o
JOIN Customers c ON c.id = o.customer_id;

Common Mistakes

Storing the encryption key inside the same database. Attackers who get the data files often get pg_catalog too. Fix: keep keys in environment variables, dedicated key tables with strict ACLs, or an external KMS.
Indexing encrypted columns for lookups. Ciphertext distribution is random, so B-tree indexes are useless and bloated. Fix: store a deterministic hash (SHA-256) alongside ciphertext for equality searches.