pgsodium
pgsodium
pgsodium : Postgres extension for libsodium functions
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 7020 | pgsodium | pgsodium | 3.1.9 | SEC | BSD 3-Clause | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--sLd-- | No | Yes | Yes | Yes | no | no |
| Relationships | |
|---|---|
| Schemas | pgsodium |
| Need By | supabase_vault |
| See Also | pgsmcrypto pgcryptokey pgcrypto anon pg_tde sslutils faker |
+fix missing pg17
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PIGSTY | 3.1.9 | 18 17 16 15 14 | pgsodium | - |
| RPM | PIGSTY | 3.1.9 | 18 17 16 15 14 | pgsodium_$v | - |
| DEB | PIGSTY | 3.1.9 | 18 17 16 15 14 | postgresql-$v-pgsodium | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PGDG 3.1.9 | PGDG 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
el8.aarch64 | PGDG 3.1.9 | PGDG 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
el9.x86_64 | PGDG 3.1.9 | PGDG 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
el9.aarch64 | PGDG 3.1.9 | PGDG 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
el10.x86_64 | PGDG 3.1.9 | PGDG 3.1.9 | PGDG 3.1.9 | PGDG 3.1.9 | PGDG 3.1.9 |
el10.aarch64 | PGDG 3.1.9 | PGDG 3.1.9 | PGDG 3.1.9 | PGDG 3.1.9 | PGDG 3.1.9 |
d12.x86_64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
d12.aarch64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
d13.x86_64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
d13.aarch64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
u22.x86_64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
u22.aarch64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
u24.x86_64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
u24.aarch64 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 | PIGSTY 3.1.9 |
Source
pig build pkg pgsodium; # build rpm/debInstall
Make sure PGDG and PIGSTY repo available:
pig repo add pgsql -u # add both repo and update cacheInstall this extension with pig:
pig install pgsodium; # install via package name, for the active PG version
pig install pgsodium -v 18; # install for PG 18
pig install pgsodium -v 17; # install for PG 17
pig install pgsodium -v 16; # install for PG 16
pig install pgsodium -v 15; # install for PG 15
pig install pgsodium -v 14; # install for PG 14Config this extension to shared_preload_libraries:
shared_preload_libraries = 'pgsodium';Create this extension with:
CREATE EXTENSION pgsodium;Usage
pgsodium: libsodium-based cryptographic functions for PostgreSQL
pgsodium is an encryption library extension for PostgreSQL using the libsodium library. It provides a direct SQL interface to libsodium, server-managed key derivation, and Transparent Column Encryption (TCE).
CREATE EXTENSION pgsodium;Generating Random Data
SELECT pgsodium.randombytes_random();
SELECT pgsodium.randombytes_buf(16); -- 16 random bytes
SELECT pgsodium.randombytes_uniform(100); -- random int 0-99Secret Key Encryption (Authenticated)
SELECT * FROM pgsodium.crypto_secretbox_keygen();
SELECT pgsodium.crypto_secretbox('message', nonce, key);
SELECT pgsodium.crypto_secretbox_open(ciphertext, nonce, key);Public Key Encryption
SELECT * FROM pgsodium.crypto_box_new_keypair();
SELECT pgsodium.crypto_box('message', nonce, public_key, secret_key);
SELECT pgsodium.crypto_box_open(ciphertext, nonce, public_key, secret_key);Public Key Signatures
SELECT * FROM pgsodium.crypto_sign_new_keypair();
SELECT pgsodium.crypto_sign('message', secret_key);
SELECT pgsodium.crypto_sign_open(signed_message, public_key);Password Hashing
SELECT pgsodium.crypto_pwhash_str('my_password');
SELECT pgsodium.crypto_pwhash_str_verify(hash, 'my_password');Hashing
SELECT pgsodium.crypto_generichash('data');
SELECT pgsodium.crypto_shorthash('data', key);Server Key Management
pgsodium can load an external root key into memory that is never accessible to SQL. Sub-keys are derived by key id:
SELECT * FROM pgsodium.create_key();
-- Returns a UUID key id for use with TCE or encryption functionsTransparent Column Encryption (TCE)
CREATE TABLE private.users (
id bigserial PRIMARY KEY,
secret text
);
SECURITY LABEL FOR pgsodium ON COLUMN private.users.secret
IS 'ENCRYPT WITH KEY ID dfc44293-fa78-4a1a-9ef9-7e600e63e101';Encrypted data is stored on disk and automatically decrypted via a generated view.
Security Roles
pgsodium_keyiduser– less privileged, can only access keys by UUIDpgsodium_keymaker– more privileged, can work with raw keys
Last updated on