Authentication in Pgpool-II
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:
- Authentication between client and Pgpool-II
- Authentication between Pgpool-II and PostgreSQL servers
Below are the password-based authentication steps:
- A user sends a request to Pgpool-II
- 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.
- Pgpool-II extracts the user's password from pool_passwd file
- User is prompted to enter password
- 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:
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_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:
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.
Comments
Post a Comment