Обсуждение: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

Hello,

I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm able to clone the primary to the standby
usingpg_basebackup. 
However, the standby is unable to authenticate to the primary to begin recovery during startup. It logs an error,
"FATAL: could not connect to the primary server: fe_sendauth: no password supplied". 

I've tried using both .pgpass and passing the password explicitly in the myrecovery.conf primary_conninfo string. Debug
logson the primary show the initial connection. but no user provided and no authentication attempt. 

pg_hba.conf on the primary:
host    all,replication     repl            primary-database-server           scram-sha-256
host    all,replication     repl            standby-database-server           scram-sha-256

myrecovery.conf on the standby:
primary_conninfo = 'host=primary-database-server port=5432 user=repl application_name=standby-server-name'
recovery_target_timeline = 'latest'
primary_slot_name = 'standby_replication_slot'

.pgpass on the standby:
# hostname:port:database:username:password
*:*:replication:repl:repl_user_password

I've triple-checked that the password in .pgpass matches the password set for the repl user in the database, and the
repluser has REPLICATION access. 
I'm able to connect to the primary server using the repl user and the psql client, both via .pgpass and providing the
passworddirectly. 
I can't figure out why the standby postgres server is skipping the provided credentials when connecting to the primary.

Thank you for your time,

Will Keaney

________________________________

This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain
informationthat is confidential and protected by law from unauthorized disclosure. Any unauthorized review, use,
disclosureor distribution is prohibited. If you are not the intended recipient, please contact the sender by reply
e-mailand destroy all copies of the original message. 

On Wed, 2024-01-10 at 22:51 +0000, Keaney, Will wrote:
> However, the standby is unable to authenticate to the primary to begin recovery during startup.
> It logs an error, "FATAL:  could not connect to the primary server: fe_sendauth: no password supplied".
>
> pg_hba.conf on the primary:
> host    all,replication     repl            primary-database-server           scram-sha-256
> host    all,replication     repl            standby-database-server           scram-sha-256
>
> myrecovery.conf on the standby:
> primary_conninfo = 'host=primary-database-server port=5432 user=repl application_name=standby-server-name'
> recovery_target_timeline = 'latest'
> primary_slot_name = 'standby_replication_slot'
>
> .pgpass on the standby:
> # hostname:port:database:username:password
> *:*:replication:repl:repl_user_password

make sure that .pgpass is in the home directory of the PostgreSQL user and has
restrictive permissions (0600).  Try using it with a manual connection attempt
via "psql".

Yours,
Laurenz Albe

On Wed, Jan 10, 2024 at 23:22 Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
>> On Wed, Jan 10, 2024 at 5:51 PM Keaney, Will <will(dot)keaney(at)kroger(dot)com> wrote:
>> Hello,
>>
>> I'm building a new 2-node Postgreql 13 streaming replication cluster. I'm able to clone the primary to the standby
usingpg_basebackup.
 
>> However, the standby is unable to authenticate to the primary to begin recovery during startup. It logs an error,
"FATAL: could not connect to the primary server: fe_sendauth: no password supplied".
 
>>
>> I've tried using both .pgpass and passing the password explicitly in the myrecovery.conf primary_conninfo string.
Debuglogs on the primary show the initial connection. but no user provided and no authentication attempt.
 
>>
>> pg_hba.conf on the primary:
>> host    all,replication     repl            primary-database-server           scram-sha-256
>> host    all,replication     repl            standby-database-server           scram-sha-256
>>
>> myrecovery.conf on the standby:
>> primary_conninfo = 'host=primary-database-server port=5432 user=repl application_name=standby-server-name'
>> recovery_target_timeline = 'latest'
>> primary_slot_name = 'standby_replication_slot'
>>
>> .pgpass on the standby:
>> # hostname:port:database:username:password
>> *:*:replication:repl:repl_user_password
>>
>> I've triple-checked that the password in .pgpass matches the password set for the repl user in the database, and the
repluser has REPLICATION access.
 
>> I'm able to connect to the primary server using the repl user and the psql client, both via .pgpass and providing
thepassword directly.
 
>> I can't figure out why the standby postgres server is skipping the provided credentials when connecting to the
primary.
>>
> Let pg_basebackup do all the work for you:
>
> $ cat ~postgres/.pg_service.conf
> [basebackup]
> host=<primary-server>
> port=5432
> user=replicator
> passfile=/var/lib/pgsql/.pgpass
>
> $ pg_basebackup \
>         --pgdata=$PGDATA \
>         --dbname=service=basebackup \
>         --verbose --progress \
>         --checkpoint=fast \
>         --write-recovery-conf \
>         --wal-method=stream \
>         --create-slot --slot=pgstandby1

Thank you! That's both extremely useful and helped me identify the source of my problem.
A previous experiment with repmgr had left a stale primary_conninfo string in postgresql.auto.conf, which was somehow
interferingwith my other recovery settings.
 
After cleaning that up, my standby can connect to my primary without issue.

Best,
Will

________________________________

This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain
informationthat is confidential and protected by law from unauthorized disclosure. Any unauthorized review, use,
disclosureor distribution is prohibited. If you are not the intended recipient, please contact the sender by reply
e-mailand destroy all copies of the original message.