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

[Ron]

Can you connect to "the database" from port 9999?

psql --host=blarge.example.com --username=postgres --port=9999



On 3/29/23 10:35, Todd Stein wrote:
>
> 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.
>

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