The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Install pgcrypto | CREATE EXTENSION IF NOT EXISTS pgcrypto
WITH SCHEMA extensions; | View examples |
| Check its version | SELECT extversion
FROM pg_extension
WHERE extname = 'pgcrypto'; | View examples |
| Check FIPS mode | SELECT extensions.fips_mode(); | View examples |
| Hash text with SHA-256 | SELECT encode(extensions.digest('payload', 'sha256'), 'hex'); | View examples |
| Hash binary data | SELECT extensions.digest(decode('00ff', 'hex'), 'sha512'); | View examples |
| Create an HMAC | SELECT extensions.hmac('payload', 'secret-key', 'sha256'); | View examples |
| Encode an HMAC | SELECT encode(extensions.hmac('payload', 'secret-key', 'sha256'), 'base64'); | View examples |
| Generate a bcrypt hash | SELECT extensions.crypt('password', extensions.gen_salt('bf', 12)); | View examples |
| Verify a password | SELECT extensions.crypt('candidate', password_hash) = password_hash
FROM app.users
WHERE id = 42; | View examples |
| Generate SHA-512 crypt | SELECT extensions.crypt('password', extensions.gen_salt('sha512crypt', 100000)); | View examples |
| Encrypt text symmetrically | SELECT extensions.pgp_sym_encrypt('secret', 'passphrase', 'cipher-algo=aes256'); | View examples |
| Decrypt symmetric text | SELECT extensions.pgp_sym_decrypt(ciphertext, 'passphrase')
FROM app.secrets
WHERE secret_id = 42; | View examples |
| Encrypt binary data | SELECT extensions.pgp_sym_encrypt_bytea(decode('00ff', 'hex'), 'passphrase'); | View examples |
| Encrypt with a public key | SELECT extensions.pgp_pub_encrypt('secret', extensions.dearmor(public_key))
FROM app.keyring
WHERE key_id = 3; | View examples |
| Decrypt with a private key | SELECT extensions.pgp_pub_decrypt(ciphertext, extensions.dearmor(private_key), 'key-passphrase'); | View examples |
| Generate random bytes | SELECT extensions.gen_random_bytes(32); | View examples |
| Generate a random UUID | SELECT pg_catalog.gen_random_uuid(); | View examples |
| Check builtin crypto policy | SHOW pgcrypto.builtin_crypto_enabled; | View examples |
pgcrypto supplies cryptographic functions inside PostgreSQL, but server-side encryption exposes plaintext and keys to the database process and administrators. Prefer TLS in transit, storage encryption at rest, and application or KMS-managed envelope encryption when the database must not see keys; use pgcrypto for narrowly defined threat models.
Step by step
Detailed examples
Install the trusted extension with least privilege
pgcrypto is trusted in PostgreSQL 18, so a role with CREATE on the database can install it when available; packaging still depends on OpenSSL build support. Install into a controlled schema and revoke unnecessary EXECUTE privileges where the threat model requires it. Extension functions are not row-level access controls.
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA extensions;
SELECT extname, extversion, extnamespace::regnamespace
FROM pg_extension WHERE extname = 'pgcrypto';Note: CREATE privilege on the database is sufficient for a trusted extension; schema privileges also apply.
Use digest for fingerprints, not password storage
digest computes an unkeyed hash and encode renders bytea for display. Hashes can detect accidental change only when the trusted expected digest is stored separately; an attacker able to alter both data and digest can recompute it. Plain fast hashes are unsuitable for passwords.
SELECT encode(extensions.digest(convert_to('invoice:481', 'UTF8'), 'sha256'), 'hex') AS sha256;Authenticate data with a separately managed secret
HMAC detects modification by parties who do not know its key. Store the key outside the protected rows and rotate it with an explicit version; SQL statements, logs, statistics, backups, and memory can expose inline secrets. Compare decoded bytea values rather than user-formatted strings when practical.
SELECT extensions.hmac(convert_to(payload, 'UTF8'), current_setting('app.hmac_key')::bytea, 'sha256') = expected_mac AS valid
FROM app.signed_events
WHERE event_id = 481;Note: A custom setting is not a secure vault by itself; supply secrets through an audited privileged path and restrict visibility.
Use purpose-built password hashing and migration policy
crypt with gen_salt stores the algorithm, parameters, and salt in one string and verifies by passing the stored hash as the second argument. PostgreSQL 18 adds sha256crypt and sha512crypt, while bcrypt remains available; organization policy and external identity systems may prefer Argon2 outside PostgreSQL. Restrict who can read hashes.
WITH stored AS (
SELECT extensions.crypt('correct horse battery staple', extensions.gen_salt('bf', 12)) AS hash
)
SELECT extensions.crypt('correct horse battery staple', hash) = hash AS valid
FROM stored;Note: Choose a work factor from measured authentication latency and rehash under stronger policy after successful login.
Prefer authenticated PGP envelopes over raw ciphers
pgp_sym_encrypt applies salt, key derivation, optional compression, a random prefix, and modification detection. Ciphertext changes each time for the same plaintext, so it cannot support equality indexing. Keep passphrases outside table data and avoid decrypting large result sets inside broadly accessible queries.
INSERT INTO app.secrets(secret_id, ciphertext, key_version)
VALUES (42, extensions.pgp_sym_encrypt('sensitive value', current_setting('app.data_key'), 'cipher-algo=aes256'), 3);Note: Do not expose keys in SQL logs or persistent role settings; use a key-management design with rotation and auditability.
Use public-key encryption when writers must not decrypt
pgp_pub_encrypt lets holders of a public key encrypt while only private-key holders decrypt. pgcrypto accepts ASCII-armored or binary keys but has limited key-ring features; use dedicated service keys rather than personal keys. Revocation, rotation, private-key passphrases, and audit remain application responsibilities.
SELECT extensions.pgp_pub_encrypt(
'export payload',
dearmor(current_setting('app.recipient_public_key'))
) AS ciphertext;Note: Keep the private key outside the database when database administrators must not decrypt the data.
Generate bounded random values and design key rotation
gen_random_bytes returns up to 1024 cryptographically strong bytes per call; core gen_random_uuid creates version-4 UUIDs. Random identifiers do not replace authorization. Plan ciphertext key-version columns, dual-read migration, backups of key metadata, and replica access because encrypted rows and SQL function calls replicate like other data.
WITH token AS (
SELECT extensions.gen_random_bytes(32) AS raw
)
SELECT encode(raw, 'base64') AS issue_once,
extensions.digest(raw, 'sha256') AS store_verifier
FROM token;Note: Send issue_once over a protected channel and persist only store_verifier with the key or policy version.
Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development Grouppgcrypto — Cryptographic Functionspostgresql.org
- PostgreSQL Global Development GroupCREATE EXTENSIONpostgresql.org
- PostgreSQL Global Development GroupPrivilegespostgresql.org
- PostgreSQL Global Development GroupSecure TCP/IP Connections with SSLpostgresql.org
- PostgreSQL Global Development GroupBinary String Functionspostgresql.org
- PostgreSQL Global Development GroupError Reporting and Loggingpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL 18 Release Notespostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.