pgcrypto
cryptographic functions
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pgcrypto | 1.3 | SEC | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 7980 | pgcrypto | No | Yes | No | Yes | Yes | No | - |
| Related | pgsodium pgsmcrypto lo anon pg_tde sslutils faker |
|---|---|
| Depended By | omni_auth omni_aws omni_credentials omni_rest pgcryptokey pgjwt |
Version
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|
| 1.3 | 1.3 | 1.3 | 1.3 | 1.3 |
Install
Note: This is a built-in contrib extension of PostgreSQL
CREATE EXTENSION pgcrypto;
Usage
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 |
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.