How to Enable Encryption in PostgreSQL

How do I enable encryption in PostgreSQL?

Enable Encryption secures PostgreSQL data in-transit and at-rest by configuring SSL/TLS and using cryptographic functions such as pgcrypto.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why should I enable encryption in PostgreSQL?

Prevent eavesdropping on client–server traffic and protect sensitive columns (e.g., credit-card numbers) from unauthorized reads. Compliance frameworks like PCI-DSS require both transport and data-at-rest encryption.

Which encryption options does PostgreSQL support?

PostgreSQL offers SSL/TLS for transport encryption and the pgcrypto extension for column-level encryption.Disk-level encryption (LUKS, AWS KMS, etc.) is handled outside PostgreSQL.

How do I enable SSL/TLS on the server?

Generate server.crt and server.key, copy them to $PGDATA, set permissions to 600, and add ssl = on plus ssl_cert_file / ssl_key_file paths in postgresql.conf. Restart PostgreSQL to activate.

How do clients verify SSL?

Distribute the CA certificate and connect with sslmode = verify-full to ensure hostname validation.

How do I encrypt specific columns with pgcrypto?

Install pgcrypto (CREATE EXTENSION pgcrypto;).Use pgp_sym_encrypt to store cipher text and pgp_sym_decrypt to read it. Store keys in a vault, not in code.

Can I mix SSL and pgcrypto?

Yes—SSL secures the wire, pgcrypto secures data at rest. They are complementary.

What are best practices for key management?

Use a Hardware Security Module (HSM) or cloud KMS.Rotate keys periodically, store key IDs not raw keys in the database, and audit decrypt events.

How do I audit encryption status?

Run SHOW ssl; to confirm on. Query pg_extension for pgcrypto. Review pg_settings where name LIKE 'ssl%'.

What are common mistakes?

Skipping server restart after changing ssl = on. Storing encryption keys in source code. See details below.

Why How to Enable Encryption in PostgreSQL is important

How to Enable Encryption in PostgreSQL Example Usage


-- Return decrypted emails only for admins
SELECT id,
       pgp_sym_decrypt(email_enc, 'mysecretkey') AS email
FROM   Customers
WHERE  id = 42;

How to Enable Encryption in PostgreSQL Syntax


-- 1. Enable SSL in postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file  = 'server.key'

-- 2. Restart the cluster (systemctl restart postgresql)

-- 3. Install pgcrypto for column encryption
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 4. Encrypt column data in an ecommerce context
-- Customers(id, name, email, created_at)
ALTER TABLE Customers ADD COLUMN email_enc bytea;
UPDATE Customers
SET email_enc = pgp_sym_encrypt(email, 'mysecretkey'),
    email      = NULL;

Common Mistakes

Mistake: Setting ssl = on but forgetting to restart PostgreSQL. Why wrong: config changes are only read at startup. Fix: run systemctl restart postgresql or pg_ctl restart.
Mistake: Saving the symmetric key in application code. Why wrong: attackers with code access can decrypt data. Fix: store keys in a vault or environment variable managed by KMS.