PostgreSQL - pgpool2 client authentication

Question

Environment: Ubuntu 20.04.6 LTS, PostgreSQL 15, pgpool-II version 4.3.5

Currently configuring pgpool2 for client authentication. I discover that it is possible to add encrypted AES256 password and user in /etc/pgpool/pool_passwd file and it's working fine. Bad thing about such setup is that you have to manually generate or update pool_passwd file every time new user is created in database or when somebody update user password (that is not sustainably for long-term) and can lead to error/or complaints from user.

I'm wondering if there is any possibility like it's in pgBouncer technology:

Setting auth_user to pgbouncer and create in database function for such user as:

CREATE FUNCTION public.lookup (
   INOUT p_user     name,
   OUT   p_password text
) RETURNS record
   LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$SELECT usename, passwd FROM pg_shadow WHERE usename = p_user$$;

From what I find in documentation it's possible to set authentication in pool_hba.conf only. Another way I think that can work is setting up cron to parse usename,password to temporary file on OS and re-encrypt scram-sha-256 into AES256 that is supported in pool_passwd.

Any ideas or experiences please ?

Answer 1

As far as I can tell It is not a built-in feature of Pgpool-II to manage user authentication dynamically, like the database functionalities of pgBouncer. When user credentials change, the traditional way entails manually changing the pool_passwd file, which can be time-consuming. You may automate this procedure by writing a script that checks the PostgreSQL pg_shadow table on a regular basis for user changes and modifies pool_passwd accordingly. To keep the user credentials in Pgpool-II and the database in sync, this script can be executed as a cron job. To handle users and their credentials directly within the PostgreSQL database and take advantage of its built-in authentication features, as an alternative, you might think about external authentication systems like LDAP or Kerberos. This would make user administration and authentication simpler and more dynamic.

Hope this helps, let me know if there is anything