Crypto

Cryptographic functions for PostgreSQL

The pgcrypto extension provides cryptographic functions for PostgreSQL, including hashing, encryption, and random data generation. Your Nile database arrives with the pgcrypto extension already enabled.

Overview

The pgcrypto extension provides functions for:

• Password hashing
• General-purpose hashing
• Encryption (symmetric and asymmetric)
• Random data generation
• Message signing and verification

Password Hashing

Using crypt()

The crypt() function is recommended for password hashing:

-- Create a users table with hashed passwords
CREATE TABLE password_hashes (
    tenant_id uuid,
    user_id uuid DEFAULT uuid_generate_v4(),
    email text,
    password_hash text,
    PRIMARY KEY (tenant_id, user_id)
);

-- Insert a user with a hashed password (using blowfish)
INSERT INTO password_hashes (tenant_id, email, password_hash) VALUES
    ('11111111-1111-1111-1111-111111111111',
     '[e-mail]',
     public.crypt('user_password', public.gen_salt('bf')));

-- Verify password
SELECT user_id 
FROM password_hashes 
WHERE email = '[e-mail]' 
  AND password_hash = public.crypt('user_password', password_hash);

Symmetric Encryption

-- Create a table with encrypted data
CREATE TABLE sensitive_data (
    tenant_id uuid,
    record_id uuid DEFAULT uuid_generate_v4(),
    description text,
    encrypted_data bytea,
    PRIMARY KEY (tenant_id, record_id)
);

-- Insert encrypted data (AES-128-CBC)
INSERT INTO sensitive_data (tenant_id, description, encrypted_data) VALUES
    ('11111111-1111-1111-1111-111111111111',
     'Credit Card',
     public.encrypt(
         '4111111111111111'::bytea,
         'encryption_key',
         'aes'
     ));

-- Decrypt data
SELECT description,
           convert_from(
                public.decrypt(
                   encrypted_data,
                   'encryption_key',
                   'aes'
               ),
               'utf-8'
           ) as decrypted_data
FROM sensitive_data;

Random Data Generation

-- Generate random bytes
SELECT public.gen_random_bytes(16);            -- 16 random bytes

-- Generate random UUID (alternative to uuid-ossp)
SELECT public.gen_random_uuid();               -- Random UUID

Dos and Don’ts

Password Storage

✅ Use crypt() with Blowfish: password_hash = public.crypt(password, public.gen_salt('bf', 8))

❌ Don’t store plain MD5 (unsafe!): password_hash = public.md5(password)

Encryption Key Management

✅ Store keys securely outside the database: encrypted_data = public.encrypt(data, current_setting('app.encryption_key'), 'aes')

❌ Don’t store keys in the database

❌ Don’t hardcode keys in application code

Salt Generation

✅ Generate a new salt for each password: SELECT public.gen_salt('bf', 8);

❌ Don’t reuse salts

❌ Don’t use static salts

Performance Considerations

1. Hashing and encryption are CPU-intensive operations. Consider caching results when appropriate.
2. Encrypted columns cannot be effectively indexed. Consider indexing non-sensitive fields instead.

Additional Resources

• PostgreSQL pgcrypto Documentation
• OpenPGP Message Format
• Password Hashing Best Practices

Assistant

Responses are generated using AI and may contain mistakes.