psql: error: connection to server at "localhost" (127.0.0.1), port 5433 failed: ERROR: failed to authenticate with backend using SCRAM DETAIL: valid password not found

ERROR

psql: error: connection to server at "localhost" (127.0.0.1), port 5433 failed: ERROR: failed to authenticate with backend using SCRAM DETAIL: valid password not found

Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps:

1. Authentication between client and Pgpool-II
2. Authentication between Pgpool-II and PostgreSQL servers

Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method.

Solution 1

If PostgreSQL servers require MD5 or SCRAM authentication for some user’s authentication but the password for that user is not present in pool_passwd, then enabling allow_clear_text_frontend_auth will allow the Pgpool-II to use clear-text-password authentication with user to get the password in plain text form from the user and use it for backend authentication.

vim /etc/pgpool2/pgpool.conf
# Change the next line to `on`
allow_clear_text_frontend_auth = on

However, plain text passwords are not recommended. If you use plain text password authentication, the connection must be protected by SSL encryption to keep user credentials safe.

Solution 2 is more secure

Below are the password-based authentication steps:

1. A user sends a request to Pgpool-II
2. If enable_pool_hba = on, Pgpool-II gets the authentication method for this user from pool_hba.conf. Ifenable_pool_hba = off, Pgpool-II gets the authentication method for this user from PostgreSQL.
3. Pgpool-II extracts the user’s password from pool_passwd file
4. User is prompted to enter password
5. Pgpool-II verifies the password provided by the incoming user. If the password provided by the user matches the password stored in pool_passwd, then Pgpool-II uses the password stored in pool_passwd for each backend authentication.

The following sections will describe pool_passwd and pool_hba.conf in details.

Password file (pool_passwd)

To perform authentication, Pgpool-II requires a password file which contains a list of database users and passwords. You can specify the name of the password file in pool_passwd parameter in pgpool.conf. Default is pool_passwd.

pool_passwd = 'pool_passwd'

The password file is a text file in the following format:

user1:TEXTmypassword
user2:AESmzVzywsN1Z5GABhSAhwLSA==
user3:md5270e98c3db83dbc0e40f98d9bfe20972
...

The password file can contain 3 types of passwords. Pgpool-II identifies the password format type by its prefix, so each password entry in pool_passwd must be prefixed with the password format.

• Plain text: store the password in plain text format using TEXT prefix (e.g. TEXTmypassword)
• AES256 encrypted password: store AES256 encrypted password using AES prefix (e.g. AESmzVzywsN1Z5GABhSAhwLSA==)
• MD5 hashed password: store MD5 hashed password using md5 prefix (e.g. md5270e98c3db83dbc0e40f98d9bfe20972)

You can register a MD5 or AES password in pool_passwd like below.

Generate AES256 encrypted password

1. Create .pgpoolkey file in Pgpool-II start user’s home directory. Here we assume that Pgpool-II is started by postgres user.

   # su - postgres
   $ echo 'some string' > ~/.pgpoolkey
   $ chmod 600 ~/.pgpoolkey
   

2. Register user name (user2) and AES encrypted password in pool_passwd.

   $ pg_enc -m -k ~/.pgpoolkey -f /etc/pgpool2/pgpool.conf -u user2 -p
   db password:
   $ cat /etc/pgpool2/pool_passwd
   user2:AESmzVzywsN1Z5GABhSAhwLSA==
   

Generate MD5 hashed password

Register user name (user3) and MD5 hashed password in pool_passwd.

$ pg_md5 -m -f /etc/pgpool2/pgpool.conf -u user3 -p
password:
$ cat /etc/pgpool2/pool_passwd
user2:AESmzVzywsN1Z5GABhSAhwLSA==
user3:md5270e98c3db83dbc0e40f98d9bfe20972

If you encountered the error:

ERROR: pid 29506: initializing pool password, failed to open file:"/etc/pgpool2/pool_passwd"

Than you have to change the owner of the file pool_passwd to postgres:

sudo chown -R postgres:postgres /etc/pgpool2/pool_passwd 

Additionally, when troubleshooting auth issues, ensure to review the pg_hba.conf file for the client authentication method:

vim /etc/postgresql/xx/main/pg_hba.conf

Sources:

https://www.pgpool.net/docs/latest/en/html/auth-methods.html

https://www.pgpool.net/docs/latest/en/html/auth-pool-hba-conf.html 

https://b-peng.blogspot.com/2020/08/authentication-in-pgpool.html