Re: strange behavior of .pgpass file

Please suggest.

On Wed, 21 Jun 2023, 02:02 Atul Kumar, <akumar14871(at)gmail(dot)com> wrote:

> Th both pgpass files contains details as below:
>
> *:5432:*:postgres:<postgrespassword>
>
> I couldn't find anything wrong there in pgpass.
>
> and if the issue would have been with .pgpass file only then I would not
> have got the same error with -W option.
>
> But I am getting the same issue when I use -W option as well on standby
> side only.
>
> While on master node I don't get any error while using the -W option along
> with standby host name.
>
>
> What could be the reason for this? As same passwords are replicating to
> standby.
>
>
>
> Regards
> Atul
>
>
>
> On Wed, 21 Jun 2023, 01:38 Adrian Klaver, <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 6/20/23 11:59, Atul Kumar wrote:
>> > Hi,
>> >
>> > I found some strange behaviour of postgres superuser password in my
>> > existing cluster, below is the basic outline of the setup.
>> > 1. version - postgres 12
>> > 2. replication - streaming replication async
>> > 3. OS- centos7
>> > 4. One Master, One Standby
>> >
>> > I have identical pgpass files on both server postgres home directory.
>> >
>> > So when I execute below command on slave node:
>> > psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select
>> > pg_is_in_recovery"
>> >
>> > I get error of password authentication:
>> >
>> > psql: error: FATAL: password authentication failed for user "postgres"
>> >
>> > password retrieved from file "/homedirectorypath/.pgpass"
>>
>> I'm going to say this is failing because per:
>>
>> https://www.postgresql.org/docs/15/libpq-pgpass.html
>>
>> hostname:port:database:username:password
>>
>> and when you are running it the hostname is not matching what you think
>> it is and the wrong password is being returned. Whereas the example
>> below is matching correctly.
>>
>> >
>> >
>> > But when I run the same command on master node:
>> >
>> > psql -h slave_hostname/ip -U postgres -d postgres -p 5432 -c "select
>> > pg_is_in_recovery"
>> >
>> >
>> > I don't get any errors and get the expected output as "t".
>> >
>> >
>> > *_Note: the passwords in and path of both pgpass files are identical._*
>> >
>> >
>> > then why am I getting errors on the slave node for the same command
>> even
>> > after having everything the same ?
>> >
>> >
>> > Also, I tried the -W to enforce the password of postgres user but got
>> > the same issue on slave and no issue on master although the password is
>> > the same.
>> >
>> >
>> > What am I missing here ? Please suggest.
>> >
>> >
>> >
>> >
>> > Regards,
>> > Atul
>> >
>> >
>> >
>> >
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
>>