pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Atul Kumar <akumar14871@gmail.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: strange behavior of .pgpass file
Date: Tue, 20 Jun 2023 13:08:37 -0700
Message-ID: <9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com> (raw)
In-Reply-To: <CA+ONtZ7=Qvha9jNyvnfTcNnKqYnySP9+eOoUFEaMTbzE90CSgg@mail.gmail.com>
References: <CA+ONtZ7=Qvha9jNyvnfTcNnKqYnySP9+eOoUFEaMTbzE90CSgg@mail.gmail.com>

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.klaver@aklaver.com






view thread (5+ messages)  latest in thread

Message-ID: <9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com>
Permalink:  ../9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com/
Also on:    postgresql.org/message-id/9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, akumar14871@gmail.com
  Subject: Re: strange behavior of .pgpass file
  In-Reply-To: <9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com>
  References: <CA+ONtZ7=Qvha9jNyvnfTcNnKqYnySP9+eOoUFEaMTbzE90CSgg@mail.gmail.com> <9398cb7d-5614-a311-6449-a97bb7dc4bf9@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox