DOCSEARCHbyAlgolia
Submit Docs

pgsodium

repository·main·Indexed 20 days ago

https://github.com/michelp/pgsodium

A PostgreSQL extension providing high-level cryptographic algorithms via the libsodium library. It features Server Key Management, Transparent Column Encryption (TCE) using security labels, and a Key Management API for deriving and managing keys via UUIDs. It supports standard encryption/decryption, public key encryption with crypto_box, and provides specialized security roles (pgsodium_keyiduser and pgsodium_keymaker) to enforce the principle of least privilege.

Tokens
11.7K
Snippets
38
Records
47
Agent score
69%

What's inside pgsodium

  1. Handle UPSERT patterns with TCE

    main

    Standard PostgreSQL INSERT ... ON CONFLICT DO UPDATE (UPSERT) patterns fail with TCE because the EXCLUDED values are already encrypted. To avoid 'double encryption', you must combine unencrypted data from the decrypted view with the existing encrypted data in the table.

    When performing an upsert, use the unencrypted value from the view to 're-encrypt' the data correctly.

    -- Example UPSERT pattern using a stored procedure
CREATE OR REPLACE FUNCTION upsert_test(p_id bigint, p_name text DEFAULT NULL, p_secret text DEFAULT NULL)
    RETURNS test LANGUAGE sql AS
    $$
    INSERT INTO test (id, name, secret) VALUES (p_id, p_name, p_secret)
    ON CONFLICT (id) DO UPDATE
        SET name   = coalesce(p_name, (SELECT name FROM test WHERE id = p_id)),
            secret = coalesce(p_secret, (SELECT decrypted_secret FROM decrypted_test WHERE id = p_id))
    RETURNING *
    $$;

  2. How pgsodium handles data types and encoding

    main

    pgsodium arguments and return values for content and keys use the bytea type.

    If you are working with text or varchar for general content, you must ensure they are encoded correctly to avoid conversion errors. While simple ASCII strings without escape or unicode characters may be implicitly cast by PostgreSQL, you should explicitly convert your text content using PostgreSQL binary string functions:

    • encode() and decode()
    • convert_to() and convert_from()

  3. Understand pgsodium security roles

    main

    The pgsodium API uses two distinct security roles to enforce the principle of least privilege:

    1. pgsodium_keyiduser: A low-privilege role suitable for user-facing applications. It can only access keys via their UUID.
    2. pgsodium_keymaker: A high-privilege role that can work with raw bytea data and managed server keys. This role should not be granted to application users.

    Note: Public key APIs (like crypto_box or crypto_sign) do not use 'key id' variants because they rely on combinations of keypairs from multiple parties.

  4. Understand Server Key Management

    main

    Server Key Management is an optional feature that allows pgsodium to load an external secret key into memory at server start. This root secret key is never accessible to SQL.

    How it works

    1. Preloading: Add pgsodium to your shared_preload_libraries in postgresql.conf or via Docker command line.
    2. Key Retrieval: pgsodium uses a custom script (placed in your postgres shared extension directory) to fetch the key. You can write your own script to fetch keys from /dev/urandom, AWS KMS, GCP KMS, Doppler, or Hardware Security Modules (HSM).
    3. Configuration: Specify the script location using the pgsodium.getkey_script configuration variable.

    Security Model

    The only way to use the server secret key is to derive sub-keys or keypairs from it using derive_key() or the key_id variants of the API. This allows you to store a bigint key ID in your database instead of the actual secret key, significantly reducing the risk of key exposure if the database is compromised.

    docker run -d ... -c 'shared_preload_libraries=pgsodium'

  5. Implement Transparent Column Encryption (TCE)

    main

    Transparent Column Encryption (TCE) uses PostgreSQL triggers to automatically encrypt columns in a table and decrypt them via a view. This is enabled using the SECURITY LABEL command. TCE requires keys created via the Key Management API (specifically of type aead-det).

    Pattern 1: One Key ID for the Entire Column

    Simple approach where one key encrypts all rows in a column. Uses the nonceless crypto_aead_det_xchacha20() algorithm.

    CREATE TABLE private.users (id bigserial primary key, secret text);
SECURITY LABEL FOR pgsodium ON COLUMN private.users.secret IS 'ENCRYPT WITH KEY ID <UUID>';

    Pattern 2: One Key ID per Row

    More secure approach where each row has its own key and nonce. This prevents a single key compromise from exposing the entire column and allows for easier key rotation.

    CREATE TABLE private.users (id bigserial primary key, secret text, key_id uuid not null, nonce bytea);
SECURITY LABEL FOR pgsodium ON COLUMN private.users.secret IS 'ENCRYPT WITH KEY COLUMN key_id';

    Pattern 3: One Key ID per Row with Nonce and Associated Data

    Provides the highest security by using a unique nonce and mixing additional metadata (associated data) into the authentication signature to ensure the plaintext hasn't been altered.

    CREATE TABLE private.users (id bigserial primary key, secret text, key_id uuid not null, nonce bytea, associated_data text);
SECURITY LABEL FOR pgsodium ON COLUMN private.users.secret IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce ASSOCIATED (id, associated_data)';

    Note: Columns used for associated data must be deterministically castable to text.

    -- Example: One Key ID per Row with Nonce and Associated Data
CREATE TABLE private.users (
	id bigserial primary key,
	secret text,
	key_id uuid not null,
	nonce bytea,
	associated_data text
);

SECURITY LABEL FOR pgsodium
	ON COLUMN private.users.secret
	IS 'ENCRYPT WITH KEY COLUMN key_id NONCE nonce ASSOCIATED (id, associated_data)';

  6. Install pgsodium

    main

    pgsodium requires libsodium >= 1.0.18. You may also need the libsodium development headers and PostgreSQL header files (typically in -dev packages) to build the extension.

    Installation via Source

    1. Clone the repository.
    2. Run sudo make install.

    Installation via PGXN

    You can install pgsodium through the pgxn extension network using:

    pgxn install pgsodium

    Database Setup

    Once compiled, install the extension into your database using SQL:

    CREATE EXTENSION pgsodium;

    Note: pgsodium creates a schema named pgsodium to prevent search_path hacking. It is recommended to always use fully qualified names (e.g., pgsodium.function_name()) or ensure pgsodium is first in your search_path.

  7. Perform Key Exchange (KX)

    main

    The Key Exchange API allows two parties to securely compute a set of shared keys using their own secret key and their peer's public key.

    Workflow

    1. Generate Seeds: Create seeds for both parties using crypto_kx_new_seed().
    2. Generate Keypairs: Convert seeds into public/secret key pairs using crypto_kx_seed_new_keypair(seed).
    3. Compute Session Keys:
      • The Client uses crypto_kx_client_session_keys(peer_public, peer_secret, self_public) to get tx (transmit) and rx (receive) keys.
      • The Server uses crypto_kx_server_session_keys(peer_public, peer_secret, self_public) to get tx and rx keys.
    4. Secure Communication: Use the resulting session keys with crypto_secretbox to encrypt and decrypt messages.
    SELECT crypto_kx_new_seed() kxseed \gset

SELECT public, secret FROM crypto_kx_seed_new_keypair(:'kxseed') \gset seed_bob_
SELECT public, secret FROM crypto_kx_seed_new_keypair(:'kxseed') \gset seed_alice_

-- Bob (Client) computes keys
SELECT tx, rx FROM crypto_kx_client_session_keys(
    :'seed_bob_public', :'seed_bob_secret', 
    :'seed_alice_public') \gset session_bob_

-- Alice (Server) computes keys
SELECT tx, rx FROM crypto_kx_server_session_keys(
    :'seed_alice_public', :'seed_alice_secret', 
    :'seed_bob_public') \gset session_alice_

-- Bob sends encrypted message
SELECT crypto_secretbox('hello alice', :'secretboxnonce', :'session_bob_tx') bob_to_alice \gset

-- Alice decrypts message
SELECT is(crypto_secretbox_open(:'bob_to_alice', :'secretboxnonce', :'session_alice_rx'),
          'hello alice', 'secretbox_open session key');

  8. Use Security Invoker views in Postgres 15+

    main

    In PostgreSQL 15 and later, you can append SECURITY INVOKER to a TCE security label. This causes the automatically generated view to run with the privileges of the user invoking the query, rather than the owner of the view. This simplifies working with Row Level Security (RLS) policies.

    -- Example: Appending SECURITY INVOKER to a TCE label
SECURITY LABEL FOR pgsodium ON COLUMN private.users.secret 
IS 'ENCRYPT WITH KEY COLUMN key_id SECURITY INVOKER';

  9. Avoid secret logging using SET LOCAL

    main

    To prevent secret keys from being written to database logs when injecting them into a session, use a transaction block with SET LOCAL.

    1. Start a transaction with BEGIN;.
    2. Disable logging for the current session using SET LOCAL log_statement = 'none'; (requires superuser privileges).
    3. Inject secrets into session variables using SET LOCAL app.<name> = <value>;.
    4. Reset logging with RESET log_statement;.
    5. Retrieve the secrets in your queries using current_setting('app.<name>').
    6. COMMIT; the transaction.

    Once the session is closed, these session-local variables are no longer accessible.

    BEGIN;

-- Generate secrets (sent to client, not stored/logged)
SELECT crypto_box_noncegen() boxnonce \gset
SELECT public, secret FROM crypto_box_new_keypair() \gset bob_
SELECT public, secret FROM crypto_box_new_keypair() \gset alice_

-- Turn off logging and inject secrets into session
SET LOCAL log_statement = 'none';
SET LOCAL app.bob_secret = :'bob_secret';
SET LOCAL app.alice_secret = :'alice_secret';
RESET log_statement;

-- Use secrets via current_setting()
SELECT crypto_box('bob is your uncle', :'boxnonce', :'bob_public', 
                  current_setting('app.alice_secret')::bytea) box \gset

SELECT crypto_box_open(:'box', :'boxnonce', :'alice_public', 
                        current_setting('app.bob_secret')::bytea);

COMMIT;

  10. Use the Authentication (auth) API for message integrity

    main

    The auth API provides cryptographic authentication to verify that a message has not been altered. It uses a secret key and produces an authentication tag (MAC).

    Important Security Notes:

    • No Encryption: crypto_auth() does not encrypt the message; it only provides a way to prove the message has not been tampered with. To protect confidentiality, use encryption functions.
    • Key Sharing: This method requires access to the secret key. If many users need to verify messages, it is generally better to use Public Key Signatures instead of sharing secret keys.
    • Nonce Reuse: While authentication itself doesn't explicitly mention nonces in these specific functions, if you are performing encryption alongside authentication, never reuse a nonce with the same key.

  11. Provide pseudonymous access to encrypted data using pgsodium and postgresql-anonymizer

    main

    You can combine pgsodium (for encryption) and postgresql-anonymizer (for anonymization) to provide pseudonymous access to sensitive data. In this pattern, pgsodium handles the underlying encryption of table data, while the anon extension is used to create anonymized views or functions that mask sensitive details (like names, ages, or secrets) for non-privileged roles. This allows users with restricted roles (e.g., staff) to query data without ever accessing the raw decrypted values or the primary encrypted tables.

    -- Example pattern: Accessing pseudonymous data via a lateral join
SELECT rec.*
FROM encrypted_record e
LEFT JOIN LATERAL pseudo_record(e.id) rec ON true
LIMIT 10;

  12. Configure the pgsodium getkey script

    main

    To use Server Key Management, you must tell PostgreSQL where your key retrieval script is located. You can do this in postgresql.conf or by using ALTER SYSTEM.

    Example using ALTER SYSTEM:

    ALTER SYSTEM SET pgsodium.getkey_script = 'path_to_script';
    pgsodium.getkey_script = 'path_to_script'