🔒

PostgreSQL Cryptographic Functions (pgcrypto)

Complete reference for PostgreSQL cryptographic functions from the pgcrypto extension, covering hashing, HMAC message authentication, symmetric encryption, password hashing with bcrypt, and cryptographically secure random bytes. Every function includes syntax, security notes, and anti-patterns. Updated for PostgreSQL 16.

6 functions

Jump to function

crypt digest gen_random_bytes gen_salt hmac pgp_sym_encrypt / pgp_sym_decrypt

What are PostgreSQL Cryptographic Functions (pgcrypto)?

PostgreSQL cryptographic functions are provided by the pgcrypto extension and include digest() for hashing (MD5, SHA1, SHA256, SHA512), hmac() for message authentication codes, crypt() and gen_salt() for password hashing using bcrypt or scrypt, encrypt() and decrypt() for AES symmetric encryption, and gen_random_bytes() for cryptographically secure random data. pgcrypto must be installed with CREATE EXTENSION pgcrypto before use.

crypt

PG 8.1+ (pgcrypto extension required: CREATE EXTENSION pgcrypto;)→ text

Hashes a password using a salt. Use gen_salt() to create the salt. Supports bf (bcrypt), md5, sha1, des algorithms.

SecurityDeveloperDBA

Signature

crypt ( password text, salt text ) → text

Parameters

Parameter	Type	Description
password	text	Plaintext password to hash
salt	text	Salt string from gen_salt(). Pass the stored hash when verifying.

Examples

sql

SELECT crypt('mysecretpassword', gen_salt('bf'));

→$2a$08$... (bcrypt hash)

sql

SELECT (crypt('mysecretpassword', stored_hash) = stored_hash) AS valid FROM users WHERE username = 'alice';

→true if password matches

sql

INSERT INTO users (username, password_hash) VALUES ('bob', crypt('s3cr3t', gen_salt('bf', 12)));

→User inserted with bcrypt-hashed password (work factor 12)

sql

SELECT crypt('newpassword', gen_salt('bf', 13)) AS upgraded_hash;

→$2a$13$... (higher work factor for 2025+ hardware)

⚠Anti-Pattern— Using crypt() with a weak algorithm like 'des' or 'md5'

DES-crypt and MD5-crypt password hashes are trivially brute-forced with modern hardware. Any system using these legacy algorithms for new password storage is insecure regardless of the key length.

✓ Instead: Always use `crypt(password, gen_salt('bf', 12))` (bcrypt with work factor ≥ 10). Never use `gen_salt('des')` or `gen_salt('md5')` for new passwords.

Always use `gen_salt('bf', 10)` (or higher) for password hashing. Bcrypt is intentionally slow, making brute-force attacks costly. The work factor doubles computation time per increment.

example

-- Store:
INSERT INTO users (username, password_hash) VALUES ($1, crypt($2, gen_salt('bf', 12)));
-- Verify:
SELECT crypt($password, password_hash) = password_hash AS valid FROM users WHERE username = $1;

→Bcrypt password storage and verification

digest

PG 8.1+ (pgcrypto extension required: CREATE EXTENSION pgcrypto;)→ bytea

Computes a binary hash of data using the specified algorithm. Requires the pgcrypto extension. Supported: md5, sha1, sha224, sha256, sha384, sha512.

SecurityDeveloperDBA

Signatures

digest ( data text, type text ) → bytea

digest ( data bytea, type text ) → bytea

Parameters

Parameter	Type	Description
data	text or bytea	Data to hash
type	text	Hash algorithm: 'md5', 'sha1', 'sha224', 'sha256', 'sha384', 'sha512'

Examples

sql

SELECT encode(digest('hello', 'sha256'), 'hex');

→2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824

sql

SELECT encode(digest('hello', 'md5'), 'hex');

→5d41402abc4b2a76b9719d911017c592

sql

SELECT encode(digest(file_content, 'sha256'), 'hex') AS integrity_hash FROM documents;

→Per-row SHA-256 integrity hash

sql

SELECT encode(digest(encode(payload, 'escape'), 'sha512'), 'hex') AS fingerprint FROM audit_log;

→SHA-512 fingerprint for audit records

⚠Anti-Pattern— Using digest() with md5 or sha1 for security-sensitive hashing

MD5 and SHA-1 are cryptographically broken — collision attacks are practical. Using `digest(data, 'md5')` or `digest(data, 'sha1')` for password derivation, integrity verification, or digital signatures is unsafe.

✓ Instead: Use `digest(data, 'sha256')` or `digest(data, 'sha512')` for integrity/authentication. For password hashing use `crypt(password, gen_salt('bf', 12))` — never use a fast hash for passwords.

MD5 and SHA1 are cryptographically broken. Use sha256 or sha512 for any security-sensitive application. For password storage, use `crypt()` with a bcrypt or scrypt algorithm instead.

example

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT encode(digest(file_content, 'sha256'), 'hex') AS content_hash FROM documents;

→SHA-256 content hash for integrity verification

gen_random_bytes

PG 8.1+ (pgcrypto extension required: CREATE EXTENSION pgcrypto;)→ bytea

Generates count cryptographically strong random bytes.

SecurityDeveloperDBA

Signature

gen_random_bytes ( count integer ) → bytea

Parameters

Parameter	Type	Description
count	integer	Number of random bytes to generate (max 1024)

Examples

sql

SELECT encode(gen_random_bytes(16), 'hex');

→a3f7c81b4e9d2056... (32 hex chars for 16 bytes)

sql

SELECT encode(gen_random_bytes(32), 'base64') AS api_token;

→Random 256-bit base64 token

sql

UPDATE users SET reset_token = encode(gen_random_bytes(32), 'hex'), reset_token_expires = now() + interval '1 hour' WHERE email = $1;

→Secure password-reset token stored with expiry

sql

INSERT INTO sessions (session_id, user_id, created_at) VALUES (encode(gen_random_bytes(24), 'base64'), $1, now());

→192-bit cryptographically random session ID inserted

⚠Anti-Pattern— Using random() or md5(random()) to generate security tokens

`random()` is a pseudo-random number generator seeded from a predictable source. An attacker who knows the seed can predict all past and future outputs. MD5 of a predictable value is equally predictable.

✓ Instead: Always use `gen_random_bytes(32)` for security tokens, session IDs, API keys, and reset tokens. This uses the OS CSPRNG (cryptographically secure pseudo-random number generator).

Use `encode(gen_random_bytes(32), 'base64')` to generate a 256-bit cryptographically secure API token. Store the token (or its SHA-256 hash) in the database.

example

UPDATE users SET api_token = encode(gen_random_bytes(32), 'base64') WHERE id = $1 RETURNING api_token;

→New secure API token returned to the application

gen_salt

PG 8.1+ (pgcrypto extension required: CREATE EXTENSION pgcrypto;)→ text

Generates a new random salt string suitable for use with crypt(). Supported types: 'bf' (bcrypt), 'md5', 'sha256crypt', 'sha512crypt'.

SecurityDeveloperDBA

Signature

gen_salt ( type text [, iter_count integer] ) → text

Parameters

Parameter	Type	Description
type	text	'bf' for bcrypt (recommended), 'md5', 'sha256crypt', 'sha512crypt'
iter_count	integer	Iteration count (work factor). Default 8 for bf; higher = slower = more secure

Examples

sql

SELECT gen_salt('bf');

→$2a$08$... (random salt, default work factor 8)

sql

SELECT gen_salt('bf', 12);

→$2a$12$... (higher work factor)

sql

SELECT crypt('userpassword', gen_salt('bf', 12)) AS bcrypt_hash;

→Ready-to-store bcrypt hash with work factor 12

sql

SELECT gen_salt('bf', 13);

→$2a$13$... (work factor 13, ~800ms — appropriate for high-security contexts)

⚠Anti-Pattern— Reusing the same salt for multiple passwords

A salt must be unique per password. Calling `gen_salt` once and storing it to reuse across multiple `crypt()` calls means that two users with the same password produce the same hash, enabling dictionary attacks and rainbow-table lookups.

✓ Instead: Call `gen_salt('bf', 12)` fresh inside every `crypt()` call: `crypt(password, gen_salt('bf', 12))`. Never share or cache salts.

As hardware gets faster, increase the work factor. Work factor 10 takes ~100ms, 12 ~400ms, 14 ~1.6s per hash on modern hardware. Choose a factor that takes 100-300ms on your slowest supported machine.

example

SELECT gen_salt('bf', 12);  -- ~400ms per hash, good for 2025

→Salt for bcrypt with work factor 12

hmac

PG 8.1+ (pgcrypto extension required: CREATE EXTENSION pgcrypto;)→ bytea

Computes a keyed-hash message authentication code (HMAC). Like digest but uses a secret key, making the output unforgeable without the key.

SecurityDeveloperDBA

Signatures

hmac ( data text, key text, type text ) → bytea

hmac ( data bytea, key bytea, type text ) → bytea

Parameters

Parameter	Type	Description
data	text or bytea	Data to authenticate
key	text or bytea	Secret key
type	text	Hash algorithm: 'sha256', 'sha512', etc.

Examples

sql

SELECT encode(hmac('message', 'secret_key', 'sha256'), 'hex');

→7cca8d5b7a2de5c04e3e29d2b9e76c0a... (varies with key)

sql

SELECT encode(hmac(payload, api_secret, 'sha256'), 'hex') AS signature FROM webhook_events;

→HMAC signature per event

sql

SELECT encode(hmac(user_id::text || '|' || extract(epoch FROM now())::text, app_secret, 'sha256'), 'hex') AS csrf_token;

→Time-bound CSRF token

sql

SELECT encode(hmac(payload, webhook_secret, 'sha256'), 'hex') = provided_signature AS valid FROM webhook_queue;

→true if webhook signature matches

⚠Anti-Pattern— Hardcoding the HMAC key as a string literal in SQL

Embedding `hmac(payload, 'my_secret_key', 'sha256')` with a literal key in SQL code exposes the secret in query logs, pg_stat_activity, and source control. Any user who can read slow-query logs can extract the key.

✓ Instead: Retrieve the HMAC key from a GUC: `hmac(payload, current_setting('app.hmac_key'), 'sha256')`. Set the GUC from the application before executing the query: `SET app.hmac_key = $key`.

Use HMAC to generate and verify webhook signatures. Compute `hmac(payload, secret, 'sha256')` on both sides and compare with a constant-time equality check to verify the webhook came from the expected source.

example

SELECT encode(hmac(event_body, webhook_secret, 'sha256'), 'hex') = provided_signature AS valid FROM webhook_queue;

→true if signature matches

pgp_sym_encrypt / pgp_sym_decrypt

PG 8.1+ (pgcrypto extension required: CREATE EXTENSION pgcrypto;)→ bytea (encrypt) or text (decrypt)

Encrypts or decrypts data using a symmetric key with PGP (OpenPGP) symmetric encryption. Requires pgcrypto extension.

SecurityDeveloperDBA

Signatures

pgp_sym_encrypt ( data text, key text ) → bytea

pgp_sym_decrypt ( data bytea, key text ) → text

Parameters

Parameter	Type	Description
data	text or bytea	Plaintext to encrypt / ciphertext to decrypt
key	text	Symmetric encryption key / passphrase

Examples

sql

SELECT encode(pgp_sym_encrypt('sensitive data', 'my_secret_key'), 'base64');

→ww0ECQMCYp/gS... (base64 ciphertext)

sql

SELECT pgp_sym_decrypt(encrypted_value, 'my_secret_key') FROM secrets;

→Decrypted plaintext

sql

UPDATE users SET ssn_encrypted = pgp_sym_encrypt(ssn_plaintext, current_setting('app.encryption_key')) WHERE ssn_plaintext IS NOT NULL;

→SSN column encrypted in-place using app-supplied key

sql

SELECT pgp_sym_decrypt(ssn_encrypted, current_setting('app.encryption_key')) AS ssn FROM users WHERE id = $1;

→Decrypted SSN for authorised lookup

⚠Anti-Pattern— Storing the encryption key in the same database as the encrypted data

If the database is compromised, an attacker who can read the `pgp_sym_encrypt` ciphertexts from one table and the encryption key from another table has everything needed to decrypt all protected data. Co-location defeats the purpose of encryption.

✓ Instead: Inject the encryption key from the application layer via a parameterized GUC (`SET app.encryption_key = $key`) and never persist it in the database. Use a secrets manager (Vault, AWS Secrets Manager) as the key store.

Store `pgp_sym_encrypt` output in a `bytea` column, or encode it to base64 for text storage. The encryption includes a random session key, so encrypting the same value twice gives different ciphertexts — this prevents statistical attacks.

example

UPDATE users SET ssn_encrypted = pgp_sym_encrypt(ssn_plaintext, current_setting('app.encryption_key')) WHERE ssn_plaintext IS NOT NULL;

→SSN field encrypted in-database

PostgreSQL Cryptographic Functions (pgcrypto)

Jump to function

What are PostgreSQL Cryptographic Functions (pgcrypto)?

crypt

Signature

Parameters

Examples

digest

Signatures

Parameters

Examples

gen_random_bytes

Signature

Parameters

Examples

gen_salt

Signature

Parameters

Examples

hmac

Signatures

Parameters

Examples

pgp_sym_encrypt / pgp_sym_decrypt

Signatures

Parameters

Examples

Related PostgreSQL Categories