Switch PostgreSQL to MD5 auth to simplify pgpool2 setup

PostgreSQL and pgpool2 administrators often face unnecessary friction when configuring authentication methods like SCRAM-SHA-256. The protocol’s reliance on AES key management and encryption can slow down deployments and complicate troubleshooting. A practical workaround is to switch both systems to MD5 password hashing, which simplifies setup while maintaining basic security standards.

Why MD5 can simplify your pgpool2 setup

SCRAM-SHA-256, while secure, introduces additional layers of complexity. Administrators must manage encryption keys, handle SCRAM handshakes, and ensure compatibility across all client libraries. In contrast, MD5 provides a lightweight alternative that streamlines configuration without requiring intricate key management.

Teams using pgpool2 often encounter authentication bottlenecks when pgpool cannot connect to PostgreSQL due to mismatched password hashing methods. Switching to MD5 eliminates this inconsistency, allowing pgpool2 to authenticate seamlessly with the backend database.

Step-by-step: Configure pgpool2 for MD5 authentication

Before proceeding, ensure you have administrative access to both pgpool2 and PostgreSQL configuration files. Start by editing pgpool2’s host-based authentication (HBA) configuration.

1. Update pgpool2’s pool_hba.conf

Open the configuration file with elevated privileges:

sudo nano /etc/pgpool2/pool_hba.conf

Locate the line defining the authentication method for local connections. Replace the existing method with MD5:

host all all 127.0.0.1/32 md5

Save the file and proceed to the next step.

2. Generate an MD5 password entry

Use pgpool2’s built-in tool to create an MD5-hashed password for the PostgreSQL user. For example, for the postgres user with a strong password:

pg_md5 -m -u postgres StrongPassword123

This command writes an MD5 entry to pgpool2’s password file without requiring additional encryption keys.

3. Verify the password file

Check the contents of pgpool2’s password store to confirm the entry was created correctly:

sudo cat /etc/pgpool2/pool_passwd

The output should display the username followed by an MD5 hash, such as:

postgres:md5xxxxxxxxxxxxxxxxxxxxxxxx

4. Restart pgpool2

Apply the changes by restarting the pgpool2 service:

sudo systemctl restart pgpool2

Step-by-step: Configure PostgreSQL for MD5 authentication

If pgpool2 is already configured but PostgreSQL still uses SCRAM-SHA-256, update the database’s authentication method to ensure compatibility.

1. Locate PostgreSQL’s pg_hba.conf

Find the configuration file using a system-wide search:

sudo find / -name pg_hba.conf 2>/dev/null

Navigate to the file, typically located in a PostgreSQL version-specific directory:

sudo nano /etc/postgresql/*/main/pg_hba.conf

2. Update HBA rules to use MD5

Modify the authentication method for local connections from scram-sha-256 to md5:

host all all 127.0.0.1/32 md5

Save the file and proceed to the next step.

3. Set password_encryption to MD5

Open PostgreSQL’s main configuration file:

sudo nano /etc/postgresql/*/main/postgresql.conf

Locate the password_encryption parameter and update it:

password_encryption = md5

If the line is commented, uncomment it before making the change.

4. Reset the user password

Rehash the postgres user’s password to ensure it uses MD5 hashing:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'StrongPassword123';"

This command updates the password and applies the new hashing method.

5. Restart PostgreSQL

Apply the changes by restarting the PostgreSQL service:

sudo systemctl restart postgresql

6. Rebuild pgpool2’s password store

Clear any outdated entries in pgpool2’s password file:

sudo truncate -s 0 /etc/pgpool2/pool_passwd

Generate a fresh MD5 entry for the postgres user:

pg_md5 -m -u postgres StrongPassword123

Verify the entry:

sudo cat /etc/pgpool2/pool_passwd

Ensure the output shows the correct MD5 hash format.

7. Restart pgpool2 again

Restart pgpool2 to apply the updated password store:

sudo systemctl restart pgpool2

Testing the configuration

After completing these steps, test the connection to verify that authentication works as expected:

psql -h 127.0.0.1 -p 9999 -U postgres -d your_db

If the connection succeeds without errors, your configuration is correct. If issues persist, double-check the password hashing method in PostgreSQL’s pg_shadow table:

sudo -u postgres psql -c "SELECT usename, passwd FROM pg_shadow WHERE usename='postgres';"

The passwd column should start with md5, confirming the password was rehashed properly. If it still shows SCRAM-SHA-256, repeat the password reset process.

Looking ahead: Balancing simplicity and security

While MD5 offers a straightforward path to resolving authentication issues, administrators should evaluate whether it meets their security requirements. For environments prioritizing robust security, consider alternative methods like SCRAM-SHA-256 with proper key management. For development or internal systems where simplicity is critical, MD5 remains a viable choice.