Date: Tue, 30 Sep 2025 12:34:43 +0000 (UTC)
From: "Dan Mahoney (Gushi)" <postgres@gushi.org>
To: Laurenz Albe <laurenz.albe@cybertec.at>
cc: pgsql-general@lists.postgresql.org
Subject: Re: pgpass file in postresql.auto.conf?
In-Reply-To: <89f3ec586ade0b6fec211ea22d45fb32500611ff.camel@cybertec.at>
Message-ID: <f1e6f98b-27bc-f268-df3d-224c346fd95c@gushi.org>
References: <31ded2b6-d8f8-497c-59ea-c7885b4a7d26@gushi.org> <89f3ec586ade0b6fec211ea22d45fb32500611ff.camel@cybertec.at>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
Archived-At: <https://www.postgresql.org/message-id/f1e6f98b-27bc-f268-df3d-224c346fd95c%40gushi.org>
Precedence: bulk

On Fri, 26 Sep 2025, Laurenz Albe wrote:

> On Fri, 2025-09-26 at 12:05 +0000, Dan Mahoney (Gushi) wrote:
>> In the interest of automation, I've set up a pgpass file for my
>> pg_basebackup between master and standby.  This all works, thusly:
>>
>> pg_basebackup -d
>> 'postgres://repuser@10.1.1.1:5432/foo?sslmode=verify-ca' -F p
>> --wal-method=stream -P -R -D /var/db/postgres/data17-test3
>>
>> However, instead of the password getting baked into the pgsql.auto.conf,
>> the reference to the passfile gets put in, instead:
>>
>> # Do not edit this file manually!
>> # It will be overwritten by the ALTER SYSTEM command.
>> primary_conninfo = 'user=repuser passfile=''/var/db/postgres/.pgpass''
>> channel_binding=prefer host=10.1.1.1 port=5432 sslmode=''verify-ca''
>> sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1
>> ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres
>> gssdelegation=0 target_session_attrs=any load_balance_hosts=disable
>> dbname=foo'
>
> That happens when "pg_basebackup" used a password file to connect to
> the PostgreSQL server.
>
>> But it seems postgres won't actually read the passfile.
>
> Oh yes, it will, as long as it has permissions 0600, 0400 or 0700 and
> belongs to the database server OS user (commonly "postgres").
> It must have worked for the "pg_basebackup", so PostgreSQL assumes it
> will also work for replication.

I found the problem.

When I did the basebackup, I used a string like:

postgres://repuser@10.1.1.1:5432/foo?sslmode=verify-ca

And my .pgpass file on the replica reflects this:

#hostname:port:database:username:password
10.1.1.1:5432:foo:repuser:xxxx

(I read *somewhere* that you can use a dummy databasename in the .pgpass 
file for replication purposes, but the actual DB is ignored.)

What I missed was that for replication, the database name in .pgpass 
*must* be 'replication', for pgpass to pay attention to it.

As in, while everything else about the connection string allowed 
pgbasebackup to find that line, that same fake DB name was not baked in to 
the primary_conninfo allow postgres to find the same user.

-Dan

--