🔒

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

digest hmac crypt gen_salt pgp_sym_encrypt / pgp_sym_decrypt gen_random_bytes

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.

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

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

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

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

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

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

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

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

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

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

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

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

PostgreSQL Cryptographic Functions (pgcrypto)

Jump to function

What are PostgreSQL Cryptographic Functions (pgcrypto)?

digest

Signatures

Parameters

Examples

hmac

Signatures

Parameters

Examples

crypt

Signature

Parameters

Examples

gen_salt

Signature

Parameters

Examples

pgp_sym_encrypt / pgp_sym_decrypt

Signatures

Parameters

Examples

gen_random_bytes

Signature

Parameters

Examples

Related PostgreSQL Categories