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:
Symmetric Encryption
Random Data Generation
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
- Hashing and encryption are CPU-intensive operations. Consider caching results when appropriate.
- Encrypted columns cannot be effectively indexed. Consider indexing non-sensitive fields instead.