pg_argon2id

A PostgreSQL extension that provides secure password hashing using the Argon2id algorithm.

Overview

pg_argon2id brings the power of the Argon2id password hashing algorithm to PostgreSQL. Argon2id is the winner of the Password Hashing Competition and is recommended by OWASP for password storage. It provides resistance against both side-channel attacks and GPU-based attacks.

Features

• Secure Password Hashing: Uses Argon2id, the most secure variant of Argon2
• Customizable Parameters: Adjust memory cost, time cost, and parallelism for your security needs
• Simple API: Two main functions for hashing and verification
• Automatic Salt Generation: Cryptographically secure random salts are generated automatically
• Standard Format: Outputs standard Argon2 encoded format compatible with other implementations

Requirements

• PostgreSQL 12 or later (tested with PostgreSQL 16)
• libargon2 development library
• C compiler (gcc or clang)
• PostgreSQL development headers

Installing Dependencies

Ubuntu/Debian:

sudo apt-get update
sudo apt-get install postgresql-server-dev-16 libargon2-dev build-essential

Fedora/RHEL:

sudo dnf install postgresql-devel libargon2-devel gcc make

macOS (with Homebrew):

brew install postgresql libargon2

Installation

From Source

1. Clone the repository:

git clone https://github.com/polyaklaci/pg_argon2id.git
cd pg_argon2id

2. Build and install:

make
sudo make install

3. Enable the extension in your database:

CREATE EXTENSION pg_argon2id;

Usage

Basic Example

-- Create a users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL
);

-- ... application receives plain password from user input ...
-- Store a hashed password
INSERT INTO users (username, password_hash)
VALUES ('john_doe', argon2id_hash('user_secret_password'));

-- ... application receives credentials from login attempt ...
-- Verify password during login
SELECT argon2id_verify('user_secret_password', password_hash) AS is_valid
FROM users
WHERE username = 'john_doe';
-- Returns: true if password matches, false otherwise

Custom Security Parameters

-- Higher security: 512 MB memory, 4 iterations, 8 parallel threads
INSERT INTO users (username, password_hash)
VALUES ('admin', argon2id_hash('admin_password', 16, 4, 524288, 8));

-- Lower security for high-traffic scenarios: 64 MB memory, 2 iterations
INSERT INTO users (username, password_hash)
VALUES ('guest', argon2id_hash('guest_password', 16, 2, 65536, 1));

Password Update Function

CREATE OR REPLACE FUNCTION update_user_password(
    p_username VARCHAR,
    p_new_password TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    v_hash TEXT;
BEGIN
    -- ... new password comes from application/user input ...
    v_hash := argon2id_hash(p_new_password);

    UPDATE users
    SET password_hash = v_hash
    WHERE username = p_username;

    RETURN FOUND;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ... application calls this function with new password ...
SELECT update_user_password('john_doe', 'new_secret_password');

Function Reference

argon2id_hash

Generates an Argon2id hash from a plaintext password.

Signature:

argon2id_hash(
    password text,
    salt_length integer DEFAULT 16,
    time_cost integer DEFAULT 3,
    memory_cost integer DEFAULT 262144,
    parallelism integer DEFAULT 4
) RETURNS text

Parameters:

• password (required): The plaintext password to hash
• salt_length (optional): Length of random salt in bytes (default: 16, range: 8-32)
• time_cost (optional): Number of iterations (default: 3, range: 1-10)
• memory_cost (optional): Memory usage in KiB (default: 262144 = 256 MB, range: 8192-2097152)
• parallelism (optional): Number of parallel threads (default: 4, range: 1-16)

Returns:

• Encoded hash string in standard Argon2 format

Example:

SELECT argon2id_hash('my_password');
-- Returns: $argon2id$v=19$m=262144,t=3,p=4$...

argon2id_verify

Verifies a plaintext password against an Argon2id hash.

Signature:

argon2id_verify(
    password text,
    hash text
) RETURNS boolean

Parameters:

• password (required): The plaintext password to verify
• hash (required): The encoded Argon2id hash to check against

Returns:

• true if the password matches the hash
• false if the password does not match

Example:

SELECT argon2id_verify('my_password', '$argon2id$v=19$m=262144,t=3,p=4$...');
-- Returns: true or false

Security Considerations

Default Parameters

The extension uses moderate security defaults suitable for most applications:

• Memory Cost: 256 MB (262144 KiB)
• Time Cost: 3 iterations
• Parallelism: 4 threads
• Salt Length: 16 bytes

These parameters provide a good balance between security and performance. According to OWASP recommendations, you should tune these based on your specific requirements and server capabilities.

Choosing Parameters

• High-Traffic Web Applications: Use lower memory cost (64-128 MB) to handle more concurrent requests
• Sensitive Data: Use higher memory cost (512 MB+) and time cost (4+) for maximum security
• Background Jobs: Can use maximum parameters without impacting user experience

Best Practices

1. Never store plaintext passwords: Always hash passwords before storing
2. Use parameterized queries: Prevent SQL injection when handling user input
3. Implement rate limiting: Protect against brute-force attacks at the application level
4. Regular security audits: Review and update password hashing parameters periodically
5. Use HTTPS: Protect passwords in transit
6. Clear password from memory: The extension automatically clears passwords from memory after processing

Building and Testing

Build

make

Install

sudo make install

Run Tests

sudo make installcheck

The test suite includes:

• Basic hash generation and verification
• Unicode password support
• Parameter validation
• Edge cases (empty passwords, long passwords)

Clean

make clean

Troubleshooting

libargon2 not found

If you get an error about libargon2 not being found:

# Ubuntu/Debian
sudo apt-get install libargon2-dev

# Or manually install from source
git clone https://github.com/P-H-C/phc-winner-argon2.git
cd phc-winner-argon2
make
sudo make install

pg_config not found

Make sure PostgreSQL development files are installed and pg_config is in your PATH:

export PATH=/usr/lib/postgresql/16/bin:$PATH

Extension does not load

Check PostgreSQL logs for detailed error messages:

sudo tail -f /var/log/postgresql/postgresql-16-main.log

Performance

Hash generation time depends on the chosen parameters. Benchmarks on a typical server (4-core CPU, 16GB RAM):

Parameters	Time per Hash
64 MB, t=2, p=1	~50ms
256 MB, t=3, p=4 (default)	~200ms
512 MB, t=4, p=8	~500ms

Choose parameters that result in 200-500ms hashing time for a good balance between security and user experience.

Contributing

Contributions are welcome! Please feel free to submit issues or pull requests on GitHub.

License

This project is licensed under the MIT License. See the LICENSE file for details.

Author

Polyák László

• GitHub: @polyaklaci
• Email: work@polyaklaszlo.com

Acknowledgments

• Uses the PHC winner Argon2 reference implementation
• Inspired by the need for secure password hashing in PostgreSQL

References

• Argon2 Specification
• OWASP Password Storage Cheat Sheet
• PostgreSQL Extension Building Infrastructure