pgcrypto

pgcrypto : cryptographic functions

Overview

ID	Extension	Package	Version	Category	License	Language
7980	pgcrypto	pgcrypto	`1.3`	SEC	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-dt-	No	Yes	No	Yes	no	yes

Relationships
Need By	omni_auth omni_aws omni_credentials omni_rest pgcryptokey pgjwt
See Also	pgsodium pgsmcrypto lo anon pg_tde sslutils faker

Packages

PG18	PG17	PG16	PG15	PG14
1.3	1.3	1.3	1.3	1.3

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION pgcrypto;

Usage

pgcrypto: Cryptographic functions for PostgreSQL

pgcrypto provides cryptographic functions including hashing, password hashing, PGP encryption, and raw encryption.

CREATE EXTENSION pgcrypto;

General Hashing

SELECT digest('data', 'sha256');                    -- Binary hash
SELECT encode(digest('data', 'sha256'), 'hex');     -- Hex output
SELECT hmac('data', 'secret_key', 'sha256');        -- HMAC

Supported algorithms: md5, sha1, sha224, sha256, sha384, sha512.

Password Hashing

-- Set a new password
UPDATE users SET pswhash = crypt('new password', gen_salt('bf'));

-- Verify a password
SELECT (pswhash = crypt('entered password', pswhash)) AS valid FROM users;

gen_salt() types: bf (Blowfish), md5, xdes, des, sha256crypt, sha512crypt.

PGP Symmetric Encryption

-- Encrypt
SELECT pgp_sym_encrypt('secret data', 'password');
SELECT pgp_sym_encrypt('secret data', 'password', 'cipher-algo=aes256, compress-algo=1');

-- Decrypt
SELECT pgp_sym_decrypt(encrypted_data, 'password');

PGP Public Key Encryption

-- Encrypt with public key
SELECT pgp_pub_encrypt('secret data', dearmor(pubkey));

-- Decrypt with private key
SELECT pgp_pub_decrypt(encrypted_data, dearmor(seckey));
SELECT pgp_pub_decrypt(encrypted_data, dearmor(seckey), 'key_password');

Key Utilities

SELECT pgp_key_id(dearmor(key_text));       -- Extract key ID
SELECT armor(binary_data);                   -- ASCII armor
SELECT dearmor(armored_text);                -- Remove armor
SELECT pgp_armor_headers(armored_text);      -- Extract armor headers

Raw Encryption

SELECT encrypt('data'::bytea, 'key'::bytea, 'aes');
SELECT decrypt(encrypted, 'key'::bytea, 'aes');

-- With IV
SELECT encrypt_iv('data'::bytea, 'key'::bytea, 'iv'::bytea, 'aes-cbc/pad:pkcs');
SELECT decrypt_iv(encrypted, 'key'::bytea, 'iv'::bytea, 'aes-cbc/pad:pkcs');

Algorithms: bf (Blowfish), aes. Modes: cbc (default), cfb, ecb. Padding: pkcs (default), none.

Random Data

SELECT gen_random_bytes(16);        -- 16 cryptographic random bytes
SELECT gen_random_uuid();           -- Random UUID v4

PGP Encryption Options

Option	Values	Default
`cipher-algo`	`bf`, `aes128`, `aes192`, `aes256`, `3des`, `cast5`	`aes128`
`compress-algo`	`0` (none), `1` (ZIP), `2` (ZLIB)	`0`
`compress-level`	`0-9`	`6`
`s2k-mode`	`0`, `1`, `3`	`3`

Last updated on 2026-03-12

auth_delay passwordcheck