Authentication in Pgpool-II

-

 
 
Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works.

Authentication methods in Pgpool-II

Pgpool-II supports several authentication methods:

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.

How does Pgpool-II authentication mechanism work?

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 



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. If "enable_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/pgpool-II/pgpool.conf -u user2 -p
db password:
$ cat /etc/pgpool-II/pool_passwd
user2:AESmzVzywsN1Z5GABhSAhwLSA==

Generate MD5 hashed password

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

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

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. 

allow_clear_text_frontend_auth = on

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

Access control in Pgpool-II

It is possible to configure access control rules in Pgpool-II using a file named pool_hba.conf.
Since clients connect to PostgreSQL servers via Pgpool-II, PostgreSQL considers all the accesses are from the host where Pgpool-II is running. Therefore, we need to control client authentication in Pgpool-II side.

To enable access control between clients and Pgpool-II using pool_hba.conf, you need to turn on enable_pool_hba. Default is off.

enable_pool_hba = on

The format of pool_hba.conf follows very closely PostgreSQL's pg_hba.conf format.
See the documentation for details.

Authentication for Pgpool-II internal tasks

Pgpool-II requires database user credentials to be configured in pgpool.conf (i.e. health_check_user, sr_check_user, recovery_user, wd_lifecheck_user) for performing internal tasks. You need to specify passwords for these users in *_password parameters. 

For example, you can specify *_user and *_password like below:

health_check_user = 'pgpool'
health_check_password = 'AESUlhhzCC3fyJ6JPRfMQd4bg=='

sr_check_user = 'pgpool'
sr_check_password = 'AESUlhhzCC3fyJ6JPRfMQd4bg=='

recovery_user = 'pgpool'
recovery_password = 'AESUlhhzCC3fyJ6JPRfMQd4bg=='

wd_lifecheck_user = 'pgpool'
wd_lifecheck_password = 'AESUlhhzCC3fyJ6JPRfMQd4bg=='

If *_password is left blank, Pgpool-II will first try to get the password from pool_passwd file before using an empty password. *_password accepts 3 types of passwords.

  • AES256 encrypted password
  • MD5 hashed password
  • Plain text password

Please note that MD5 hashed passwords can't be specified in recovery_password and wd_lifecheck_password.

Limitations

Pgpool-II doesn't support GSSAPI Authentication yet. If GSSAPI is requested in your environment, the connection attempt will fail. A workaround is to set an environment variable to disable GSSAPI encryption in the client:

export PGGSSENCMODE=disable

Conclusion

Pgpool-II supports several authentication methods. Starting with Pgpool-II 4.0, Pgpool-II supports SCRAM authentication. This implementation significantly improves the security of your database cluster. Additionally, the next major release of Pgpool-II 4.2 will support LDAP authentication. 

This blog should be helpful to users who want to understand the authentication mechanism in Pgpool-II. As mentioned above, because the authentication in Pgpool-II comprises two steps, the configuration might be a little bit complicated. In future blogs, I will describe how to configure each authentication method in details.


Comments

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: #
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize
Read more

Connection Pooling in Pgpool-II

-
Image
Pgpool-II is a cluster management tool for PostgreSQL that can cache connections to PostgreSQL servers. This blog introduces Pgpool-II connection pooling feature and shows how to configure connection pooling in Pgpool-II. What is connection pooling? Establishing and maintaining Database connections are expensive. The reason is that you have to establish a network connection, perform authentication and so on. Database connection pooling is a cache of database connections to keep database connections open so that the connection can be reused when a connection is required by a future request. Reusing an active connection rather than establishing a new connection each time a connection is requested can improve performance and save system resources. How does connection pooling work in Pgpool-II? Pgpool-II caches established connections to the PostgreSQL servers and reuses them whenever a new request with the same properties (i.e. user name, database, protocol version, and other con
Read more