跳转到主要内容

pgcrypto

cryptographic functions

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 column_encrypt omni_auth omni_aws omni_credentials omni_rest pg_dispatch pgcryptokey pgjwt
See Also pgsodium pgsmcrypto pgcryptokey pguecc shacrypt cryptint pg_tde column_encrypt supabase_vault pg_enigma hashlib xxhash

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