pgcrypto

What pgcrypto does

pgcrypto brings four categories of cryptographic operations into PostgreSQL:

• General-purpose hashing — digest() and hmac() compute hashes using MD5, SHA-1, SHA-224, SHA-256, SHA-384, or SHA-512
• Password hashing — crypt() and gen_salt() implement bcrypt, extended DES, and MD5 password hashing with automatic salting
• PGP encryption — pgp_sym_encrypt() / pgp_sym_decrypt() for symmetric encryption and pgp_pub_encrypt() / pgp_pub_decrypt() for public-key encryption, both following the OpenPGP standard (RFC 4880)
• Random data — gen_random_bytes() for cryptographically secure random bytes and gen_random_uuid() for version 4 UUIDs (now a core function in PostgreSQL 13+)

The extension uses OpenSSL under the hood, so it benefits from hardware-accelerated cryptographic instructions on modern CPUs. All functions operate on standard PostgreSQL types — text, bytea, and uuid — and can be used in any SQL context: INSERT, SELECT, triggers, or functions.

When to use pgcrypto

pgcrypto is useful when cryptographic operations need to happen at the database layer rather than in application code. Common scenarios:

• Password hashing for internal tools — when the application is simple enough that bcrypt in SQL is more practical than adding a hashing library
• Column-level encryption at rest — encrypting specific sensitive fields (medical records, financial data, PII) while leaving the rest of the row queryable
• Generating random tokens — API keys, session tokens, or nonces generated directly in SQL without a round trip to the application
• Data integrity verification — computing SHA-256 digests to verify that imported or replicated data has not been altered
• HMAC-based lookups — storing a keyed hash of an encrypted field to allow exact-match lookups without decrypting

I should be forthright about the trade-off, because overlooking it would be a disservice. When you encrypt or hash data in the database, the plaintext and encryption keys pass through PostgreSQL's memory and network connection. They may appear in query logs, pg_stat_activity, and pg_stat_statements. For high-security applications, hashing and encryption at the application level keeps sensitive material out of the database process entirely. Convenience and security are often in tension, and pgcrypto does not resolve that tension — it simply gives you the choice of where to make it.

Installation and setup

pgcrypto is a contrib module — it ships with every standard PostgreSQL installation. No additional packages, shared library preloading, or server restart required.

-- pgcrypto ships with PostgreSQL (contrib module)
CREATE EXTENSION pgcrypto;

-- Verify installation
SELECT digest('hello', 'sha256');

That is all. Two lines. The extension creates its functions in the current schema (usually public) and is ready to use immediately. Securing a household should be this straightforward more often than it is.

Hashing with digest() and hmac()

digest() computes a standard cryptographic hash. hmac() computes a keyed hash — useful when you need to verify both integrity and authenticity.

-- SHA-256 hash (returns bytea)
SELECT digest('my data', 'sha256');

-- Supported algorithms: md5, sha1, sha224, sha256, sha384, sha512
SELECT digest('my data', 'sha512');

-- HMAC — keyed hash for message authentication
SELECT hmac('my data', 'secret-key', 'sha256');

Both functions return bytea. Use encode(..., 'hex') or encode(..., 'base64') to convert to a human-readable string. The raw bytes are perfectly valid, but one does appreciate legibility.

Password hashing with crypt() and gen_salt()

crypt() is the correct function for password hashing. Unlike digest(), it uses algorithms designed to be slow (bcrypt), incorporates salt automatically, and produces a self-describing string that encodes the algorithm, iteration count, salt, and hash together.

-- Hash a password with bcrypt (recommended)
INSERT INTO users (email, password_hash)
VALUES ('user@example.com', crypt('their-password', gen_salt('bf')));

-- Verify a password against the stored hash
SELECT (password_hash = crypt('their-password', password_hash)) AS valid
FROM users
WHERE email = 'user@example.com';

-- gen_salt algorithms: 'bf' (bcrypt, recommended), 'md5', 'xdes', 'des'
-- Bcrypt iteration count (default 8, range 4-31):
SELECT gen_salt('bf', 12);  -- slower but more secure

Bcrypt ('bf') is the recommended algorithm. The iteration count parameter controls how many rounds of hashing are performed — higher values are slower and more resistant to brute-force attacks. The default of 8 is reasonable; 12 is more conservative. Each increment doubles the work. A good lock is worth the extra moment it takes to turn.

Symmetric encryption with PGP

The pgp_sym_encrypt() and pgp_sym_decrypt() functions implement OpenPGP symmetric encryption. The output is a standard PGP message that can be decrypted by any PGP-compatible tool.

-- Symmetric encryption with PGP (AES-128 by default)
SELECT pgp_sym_encrypt('sensitive data', 'encryption-key');

-- Decrypt
SELECT pgp_sym_decrypt(
  pgp_sym_encrypt('sensitive data', 'encryption-key'),
  'encryption-key'
);

-- Encrypt with specific cipher (AES-256)
SELECT pgp_sym_encrypt(
  'sensitive data',
  'encryption-key',
  'cipher-algo=aes256'
);

pgcrypto also supports public-key PGP encryption via pgp_pub_encrypt() and pgp_pub_decrypt(), which use RSA or DSA keys. This is less commonly used in database contexts but can be valuable when different parties need to encrypt and decrypt data independently.

Random data generation

gen_random_bytes() returns cryptographically secure random bytes — suitable for tokens, keys, and nonces.

-- Generate 32 random bytes (for tokens, keys, nonces)
SELECT gen_random_bytes(32);

-- Hex-encoded random token
SELECT encode(gen_random_bytes(32), 'hex') AS token;

-- Base64-encoded random token
SELECT encode(gen_random_bytes(24), 'base64') AS token;

-- gen_random_uuid() — moved to core in PostgreSQL 13
-- On PG 13+, no extension needed:
SELECT gen_random_uuid();

A point of honesty: gen_random_uuid() was originally a pgcrypto function, but it was promoted to a core PostgreSQL function in version 13. If you are installing pgcrypto solely for UUID generation, you no longer need to. On PostgreSQL 13+, you can call it without any extension at all. If pgcrypto happens to be installed, its version is simply a thin wrapper around the core function. Credit where it is due — the function graduated from the extension that raised it.

Column-level encryption example

A common pattern is encrypting specific columns while leaving the rest of the row in plaintext for indexing and querying.

-- Example: encrypting a column at rest
CREATE TABLE medical_records (
  id serial PRIMARY KEY,
  patient_name text,
  diagnosis bytea  -- encrypted
);

-- Insert with encryption
INSERT INTO medical_records (patient_name, diagnosis)
VALUES ('Jane Doe', pgp_sym_encrypt('Type 2 Diabetes', 'clinic-key'));

-- Query with decryption
SELECT patient_name,
       pgp_sym_decrypt(diagnosis, 'clinic-key') AS diagnosis
FROM medical_records;

The encrypted column is stored as bytea and is entirely opaque — it cannot be indexed, sorted, or filtered without decryption. This is not a limitation to work around; it is the entire point. A locked safe that anyone can browse through is not a safe. Plan your schema accordingly: fields you need to search on should remain unencrypted or have a companion HMAC column for exact-match lookups.

Cloud availability

How Gold Lapel relates

I will be direct: Gold Lapel does not involve itself with your encrypted data. It sits between your application and PostgreSQL as a transparent proxy, and when queries containing pgcrypto calls — pgp_sym_encrypt(), crypt(), digest() — pass through, they are forwarded unchanged. Encrypted bytea columns are as opaque to Gold Lapel as they are to PostgreSQL's own query planner.

This is by design, not by limitation. Gold Lapel does not have your encryption keys, does not want your encryption keys, and would not accept them if offered. Your cryptographic operations pass through unmodified. Gold Lapel directs its attention to the queryable, plaintext portions of your schema — the rooms of the household where optimization is both possible and appropriate.