Chapter 6. Client Authentication

Table of Contents
6.1. The pool_hba.conf File
6.2. Authentication Methods
6.2.1. Trust Authentication
6.2.2. Clear Text Password Authentication
6.2.3. MD5 Password Authentication
6.2.4. scram-sha-256 Authentication
6.2.5. Certificate Authentication
6.2.6. PAM Authentication
6.2.7. LDAP Authentication
6.2.8. GSSAPI Authentication
6.3. Using different methods for frontend and backend authentication
6.4. Using AES256 encrypted passwords in pool_passwd
6.4.1. Creating encrypted password entries
6.4.2. Providing decryption key to Pgpool-II

Since Pgpool-II is a middleware that works between PostgreSQL servers and a PostgreSQL database client, so when a client application connects to the Pgpool-II, Pgpool-II in turn connects to the PostgreSQL servers using the same credentials to serve the incoming client connection. Thus, all the access privileges and restrictions defined for the user in PostgreSQL gets automatically applied to all Pgpool-II clients, with an exceptions of the authentications on PostgreSQL side that depends on the client's IP addresses or host names. Reason being the connections to the PostgreSQL server are made by Pgpool-II on behalf of the connecting clients and PostgreSQL server can only see the IP address of the Pgpool-II server and not that of the actual client. Therefore, for the client host based authentications Pgpool-II has the pool_hba mechanism similar to the pg_hba mechanism for authenticating the incoming client connections.

The pool_passwd file manages passwords for authentication used when clients connect to Pgpool-II (see pool_passwd for more details). The passwords registered on pool_passwd must match with the passwords registered on PostgreSQL. Note that passwords changed on PostgreSQL are not automatically sync to the passwords on pool_passwd. scram-shar-256 authentication and MD5 authentication require that the user name and the password have been already registered on pool_passwd, while clear text password authentication does not require that. Therefore, if you want to avoid maintaining the pool_passwd, it would be worth to check clear text password authentication.