How to Configure SCRAM and MD5 Authentication in Pgpool-II

-



This post refers to my previous one Authentication in Pgpool-II. In the post I introduced how Pgpool-II authentication mechanism works. In this post I will describe how to configure SCRAM and MD5 authentication methods in details.

scram-sha-256 Authentication Configuration

To perform the password-based authentication, Pgpool-II requires a password file which contains a list of database users and passwords in format username:password. The default password file name is pool_passwd.

If scram-sha-256 is specified as the authentication method in pool_hba.conf or pg_hba.conf, to use scram-sha-256 authentication, the decryption key to decrypt the passwords is required. We create the .pgpoolkey file in the home directory of Pgpool-II start user.

Here we assume that Pgpool-II is started using postgres user. 

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

Assuming you've already created a database user pgpool. Execute the pg_enc to register the user pgpool and AES encrypted password to pool_passwd.  

# su - postgres
$ pg_enc -m -k ~/.pgpoolkey -u pgpool -p
db password:
trying to read key from file /var/lib/pgsql/.pgpoolkey
$ cat /etc/pgpool-II/pool_passwd
pgpool:AESheq2ZMZjynddMWk5sKP/Rw== 

MD5 Authentication Configuration

Execute the pg_md5 to register the user pgpool and md5 encrypted password to pool_passwd.  

# pg_md5 --md5auth -f /etc/pgpool-II/pgpool.conf -u pgpool -p
# cat /etc/pgpool-II/pool_passwd
pgpool:md5f24aeb1c3b7d05d7eaf2cd648c307092

Register user:password from a file

Since the coming major release 4.2, Pgpool-II supports for registering user:password from a file.

AES encrypted password

$ cat users.txt
username1:secretpassword1
username2:secretpassword2

$ pg_enc -m -f /etc/pgpool-II/pgpool.conf -i users.txt
trying to read key from file /var/lib/pgsql/.pgpoolkey

$ cat /etc/pgpool-II/pool_passwd
username1:AESnGfDT45wWxwDhy0CYzJ6RQ==
username2:AES8cwgIELJzHtCGXwubZpsHg==

MD5 encrypted password

$ cat users.txt
username3:secretpassword3
username4:secretpassword4

$ pg_md5 -m -f /etc/pgpool-II/pgpool.conf -i users.txt
trying to read username:password pairs from file users.txt

$ cat /etc/pgpool-II/pool_passwd
username3:md5300d92c36d9f411a7e0d634cc1a4d45c
username4:md586b493ed9d09d4aec56e25aa7eb87ce3 

Conclusion

In this blog I described how to configure SCRAM and MD5 authentication. As I mentioned in my previous post, Pgpool-II supports several authentication methods. In future blogs, I am going to describe other authentication methods (e.g. Certificate Authentication, LDAP Authentication) 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

Authentication in Pgpool-II

-
Image
    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: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  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: Authen...
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