Re: strange behavior of .pgpass file

[Laurenz Albe]

On Wed, 2023-06-21 at 00:29 +0530, Atul Kumar wrote:
> 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"
> 
> 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 ?

pg_hba.conf could be different on both servers.

Yours,
Laurenz Albe

Re: strange behavior of .pgpass file

[Adrian Klaver]

On 6/20/23 13:32, Atul Kumar wrote:

Please reply to list also.
Ccing list.


Th both pgpass files contains details as below:

*:5432:*:postgres:


There are no other lines in the file?

More information below.



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.


From here:

https://www.postgresql.org/docs/12/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-AUTHENTICATION

password_encryption (enum)

When a password is specified in CREATE ROLE or ALTER ROLE, this 
parameter determines the algorithm to use to encrypt the password. The 
default value is md5, which stores the password as an MD5 hash (on is 
also accepted, as alias for md5). Setting this parameter to 
scram-sha-256 will encrypt the password with SCRAM-SHA-256.


Note that older clients might lack support for the SCRAM 
authentication mechanism, and hence not work with passwords encrypted 
with SCRAM-SHA-256. See Section 20.5 for more details.



1) Verify what the password method is on both servers.

2) Check the version of psql you are using in each case.





Regards
Atul



On Wed, 21 Jun 2023, 01:38 Adrian Klaver, > 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.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: strange behavior of .pgpass file

[Atul Kumar]

Please suggest.

On Wed, 21 Jun 2023, 02:02 Atul Kumar,  wrote:

> Th both pgpass files contains details as below:
>
> *:5432:*:postgres:
>
> 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, 
> 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.kla...@aklaver.com
>>
>>

Re: strange behavior of .pgpass file

[Adrian Klaver]

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

strange behavior of .pgpass file

[Atul Kumar]

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"


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