How to enable encryption in PostgreSQL

Galaxy Glossary

How do I enable SSL and data-at-rest encryption for ParadeDB?

Turns on SSL/TLS and optional row-level encryption so ParadeDB traffic and data stay unreadable to outsiders.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why enable encryption in ParadeDB?

Encryption protects data in transit (client ↔ server) and at rest (on disk). When ParadeDB is used for similarity search, vectors often contain sensitive customer signals that must stay private.

Which PostgreSQL settings switch on SSL?

Set ssl = on in postgresql.conf, provide a server certificate, and reload the service. ParadeDB automatically inherits the cluster-wide SSL configuration.

Steps to enable SSL

1) Copy server.crt and server.key into $PGDATA.
2) Run chmod 600 server.key.
3) Edit postgresql.conf: ssl = on.
4) Restart PostgreSQL: sudo systemctl restart postgresql.

How do I encrypt sensitive columns?

Use the pgcrypto extension. ParadeDB indexes encrypted payloads as bytea, so vectors remain functional while raw text stays hidden.

Column-level encryption example

Create a symmetric key, encrypt customer e-mails, and keep search vectors readable:

CREATE EXTENSION IF NOT EXISTS pgcrypto; ALTER TABLE Customers ADD COLUMN email_enc bytea; UPDATE Customers SET email_enc = pgp_sym_encrypt(email, 'aes-key-123'); ALTER TABLE Customers ALTER COLUMN email DROP NOT NULL;

Can I combine ParadeDB indexes with encrypted data?

Yes. Store the cleartext in a separate vector column while keeping the original sensitive field encrypted. This pattern satisfies both privacy and search performance.

Best practices for ParadeDB encryption

• Rotate SSL certificates yearly.
• Store encryption keys in a secrets manager, not in source control.
• Re-encrypt data after key rotation.
• Enable ssl_prefer_server_ciphers = on for stronger handshakes.

Why How to enable encryption in PostgreSQL is important

How to enable encryption in PostgreSQL Example Usage


-- Force SSL connection from psql
\set sslmode 'require'
\c "dbname=ecom user=analyst host=db.example.com sslmode=require"

-- Retrieve encrypted e-mail and decrypt on the fly
SELECT id,
       pgp_sym_decrypt(email_enc, 'aes-key-123') AS email
FROM   Customers
WHERE  id = 42;

How to enable encryption in PostgreSQL Syntax


-- 1. Enable SSL for the whole cluster
ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file  = 'server.key';
SELECT pg_reload_conf();

-- 2. Encrypt column data using pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Example in ecommerce schema
-- Customers(id, name, email, created_at)
ALTER TABLE Customers ADD COLUMN email_enc bytea;
UPDATE Customers
SET    email_enc = pgp_sym_encrypt(email, 'aes-key-123');

-- 3. Create ParadeDB vector index on safe column
-- Products(id, name, price, stock)
ALTER TABLE Products ADD COLUMN name_vec vector;
UPDATE Products SET name_vec = to_embedding(name);
CREATE INDEX products_name_vec_idx ON Products USING ivfflat (name_vec);

Common Mistakes

Skipping key permissions: If <code>server.key</code> isn’t <code>chmod 600</code>, PostgreSQL refuses to start. Fix by setting strict file permissions.
Encrypting indexed vector columns: ParadeDB can’t index ciphertext efficiently. Keep a separate unencrypted vector column and protect the original text.