[pgpool-general: 8689] Re: password file format

[Todd Stein]

Hi Ron,
Thanks for your response.
I should have not included the reference to pcp.conf and pool_passwd files.  These are well documented, and made my question unclear.

This one statement is the one I need help with:

"In my testing I find that if the password in ~/.pgpass includes the AES prefix in the encrypted password, I get password authentication failed for user "postgres" when the system tries to start a replication slot."

More detail:

Here are a few lines from the postgresql-Wed.log file.  This entry corresponds to a pcp_recovery_node command:

2023-03-29 11:20:27.378 EDT [660839] STATEMENT:  START_REPLICATION SLOT "pg_basebackup_660839" 3/7000000 TIMELINE 76
2023-03-29 11:20:30.860 EDT [660848] FATAL:  password authentication failed for user "postgres"
2023-03-29 11:20:30.860 EDT [660848] DETAIL:  Connection matched pg_hba.conf line 108: "host    all             postgres         0.0.0.0/0             scram-sha-256"

During the pcp_recovery_node process the system attempts to create a replicaion slot, and fails...  I'm trying to figure out why.



Regards,

Todd Stein

From: pgpool-general <pgpool-general-bounces at pgpool.net> On Behalf Of Ron
Sent: Wednesday, March 29, 2023 11:18 AM
To: pgpool-general at pgpool.net
Subject: [pgpool-general: 8688] Re: password file format

On 3/29/23 09:52, Todd Stein wrote:

Hi,
Will someone please correct or confirm my assumption of the SCRAM-SHA-256 password file format for $HOME/.pgpass and $HOME/.pcppass files?

I'm not sure if I should be using the password with the AES prefix outside of the pool_password file or not.  For example in the .pgpass and/or .pcppass files.

$ pg_enc -k ~/.pgpoolkey -u postgres -p
db password:
trying to read key from file /var/lib/pgsql/.pgpoolkey

P1+l8j3GaTxzSBgcY1laEQ==
pool_passwd string: AESP1+l8j3GaTxzSBgcY1laEQ==

My understanding (please correct me if I'm wrong), is that the pcp.conf file must use md5 encryption regardless of what your password_encryption in the DB is.

pcp is for managing PgPool.


The pool_password file (when using scram-sha-256 encryption) requires the string it gets automatically (which includes the AES prefix) by the pg_enc command when providing the "-m" attribute.

pool_passwd is for accessing Postgresql databases.  Their "user lists" are completely separate.  You can, for example, have user "blarge" in pcp.conf but not in pool_passwd (and by extension be a Postgresql role).

However, I've not been able to find anything documented for the password files.

What do you mean? https://www.pgpool.net/docs/43/en/html/auth-methods.html<https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.pgpool.net%2Fdocs%2F43%2Fen%2Fhtml%2Fauth-methods.html&data=05%7C01%7Ctodd.stein%40microfocus.com%7C74b196748d38442770ac08db3068d1a7%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638156998980302068%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=SoP1bzxnsvnmNLUJVD9Ue9VKvbW%2BTXWw2c6ATDmAT1U%3D&reserved=0> describes pool_passwd, and describes how to create MD5 and SHA256 hashes.


  I'm pretty sure I've seen that if I were to use an encrypted password (scram-sha-256) in the pgpool.conf file, it must include the AES prefix.

pg_enc does that for you.



In my testing I find that if the password in ~/.pgpass includes the AES prefix in the encrypted password, I get password authentication failed for user "postgres" when the system tries to start a replication slot.

That needs more detail.
--
Born in Arizona, moved to Babylonia.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20230329/27ce16f7/attachment.htm>