pgcrypto is a PostgreSQL extension for cryptographic functions, including password hashing. Enable it separately in each database that needs these capabilities. section 7.2.2
Enable and verify pgcrypto with SQL:
- Check availability with pg_available_extensions; an empty installed_version means the extension is not enabled for the current database. section 7.2.2
- Run CREATE EXTENSION pgcrypto; then inspect pg_extension to confirm the installed version. section 7.2.2
Listing 7.1 Checking the pgcrypto extension version
SELECT name, default_version, installed_version FROM pg_available_extensions WHERE name = 'pgcrypto';
Use the availability query to inspect the server version before enabling pgcrypto. section 7.2.2
Hash and verify a password
Create an accounts table with a password_hash column, then insert a user with crypt('SuperSecret123', gen_salt('bf')). To authenticate, compare password_hash with crypt('SuperSecret123', password_hash); the correct password returns the username, while a wrong password returns zero rows. section 7.2.2
Listing 7.3 Encrypting the password for a new user
INSERT INTO accounts (username, password_hash)
VALUES ('ahamilton', crypt('SuperSecret123', gen_salt('bf')));
Run the insertion example to store a salted password hash rather than the original password. section 7.2.2
