Re: Postgres 13 streaming replication standby not sending password, 'fe_sendauth: no password supplied'

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 using pg_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 logs 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 repl user 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 password 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