Обсуждение: 8.4 vs. 9.x: 127.0.0.0/8

Hello all,

    We are noticing what appears to be a significant difference between
PostgreSQL 9.x and 8.4. Not having found documentation that would point
us in the direction of a good solution, I thought I’d post our issue here.

On CentOS 6 we have postgresql 8.4.20 and the following pg_hba.conf:

local samerole all        md5
host samerole all  127.0.0.200   255.255.255.255   pam
pamservice=postgresql_cpses
host samerole all  127.0.0.1   255.255.255.255   md5
local all postgres        md5
host all postgres  127.0.0.1   255.255.255.255   md5

So connections to 127.0.0.200 are handled by pam, connections to
127.0.0.1 are handled by md5.

If I run:
root@jason:/$ psql -h 127.0.0.200 -U pguser
Password for user pguser:
psql: FATAL:  PAM authentication failed for user "pguser"

You can see it tried to authenticate using PAM authentication.

On CentOS 7 we have postgresql 9.2.13 and the following pg_hba.conf:

local samerole all        md5
host samerole all  127.0.0.200   255.255.255.255   pam
pamservice=postgresql_cpses
host samerole all  127.0.0.1   255.255.255.255   md5
local all postgres        md5
host all postgres  127.0.0.1   255.255.255.255   md5

But, running the command above yields a different result:
root@i-0000764a [/usr/local/cpanel]# psql -h 127.0.0.200 -U pguser
Password for user pguser:
psql: FATAL:  password authentication failed for user "pguser"

It tried to use password auth instead of PAM. We think postgresql might
be seeing any 127.0.0.0/8 address as 127.0.0.1. Could you please shed
some light on the issue or point us in the right direction on how to
make the 9.2.13 configuration function as the 8.4.20 configuration does?

    Thank you!

-Felipe Gasper
Houston, TX

On 08/11/2015 01:19 PM, Felipe Gasper wrote:
> Hello all,
>
>      We are noticing what appears to be a significant difference between
> PostgreSQL 9.x and 8.4. Not having found documentation that would point
> us in the direction of a good solution, I thought I’d post our issue here.
>
> On CentOS 6 we have postgresql 8.4.20 and the following pg_hba.conf:
>
> local samerole all        md5
> host samerole all  127.0.0.200   255.255.255.255   pam
> pamservice=postgresql_cpses
> host samerole all  127.0.0.1   255.255.255.255   md5
> local all postgres        md5
> host all postgres  127.0.0.1   255.255.255.255   md5
>
> So connections to 127.0.0.200 are handled by pam, connections to
> 127.0.0.1 are handled by md5.
>
> If I run:
> root@jason:/$ psql -h 127.0.0.200 -U pguser
> Password for user pguser:
> psql: FATAL:  PAM authentication failed for user "pguser"
>
> You can see it tried to authenticate using PAM authentication.
>
> On CentOS 7 we have postgresql 9.2.13 and the following pg_hba.conf:
>
> local samerole all        md5
> host samerole all  127.0.0.200   255.255.255.255   pam
> pamservice=postgresql_cpses
> host samerole all  127.0.0.1   255.255.255.255   md5
> local all postgres        md5
> host all postgres  127.0.0.1   255.255.255.255   md5
>
> But, running the command above yields a different result:
> root@i-0000764a [/usr/local/cpanel]# psql -h 127.0.0.200 -U pguser
> Password for user pguser:
> psql: FATAL:  password authentication failed for user "pguser"
>
> It tried to use password auth instead of PAM. We think postgresql might
> be seeing any 127.0.0.0/8 address as 127.0.0.1. Could you please shed
> some light on the issue or point us in the right direction on how to
> make the 9.2.13 configuration function as the 8.4.20 configuration does?

Are you sure you are pointing at the right instance of Postgres?

Or to put it another way, is there more than one Postgres server running
on the machine?

>
>      Thank you!
>
> -Felipe Gasper
> Houston, TX
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11 Aug 2015 5:56 PM, Adrian Klaver wrote:
> On 08/11/2015 01:19 PM, Felipe Gasper wrote:
>> Hello all,
>>
>>      We are noticing what appears to be a significant difference between
>> PostgreSQL 9.x and 8.4. Not having found documentation that would point
>> us in the direction of a good solution, I thought I’d post our issue
>> here.
>>
>> On CentOS 6 we have postgresql 8.4.20 and the following pg_hba.conf:
>>
>> local samerole all        md5
>> host samerole all  127.0.0.200   255.255.255.255   pam
>> pamservice=postgresql_cpses
>> host samerole all  127.0.0.1   255.255.255.255   md5
>> local all postgres        md5
>> host all postgres  127.0.0.1   255.255.255.255   md5
>>
>> So connections to 127.0.0.200 are handled by pam, connections to
>> 127.0.0.1 are handled by md5.
>>
>> If I run:
>> root@jason:/$ psql -h 127.0.0.200 -U pguser
>> Password for user pguser:
>> psql: FATAL:  PAM authentication failed for user "pguser"
>>
>> You can see it tried to authenticate using PAM authentication.
>>
>> On CentOS 7 we have postgresql 9.2.13 and the following pg_hba.conf:
>>
>> local samerole all        md5
>> host samerole all  127.0.0.200   255.255.255.255   pam
>> pamservice=postgresql_cpses
>> host samerole all  127.0.0.1   255.255.255.255   md5
>> local all postgres        md5
>> host all postgres  127.0.0.1   255.255.255.255   md5
>>
>> But, running the command above yields a different result:
>> root@i-0000764a [/usr/local/cpanel]# psql -h 127.0.0.200 -U pguser
>> Password for user pguser:
>> psql: FATAL:  password authentication failed for user "pguser"
>>
>> It tried to use password auth instead of PAM. We think postgresql might
>> be seeing any 127.0.0.0/8 address as 127.0.0.1. Could you please shed
>> some light on the issue or point us in the right direction on how to
>> make the 9.2.13 configuration function as the 8.4.20 configuration does?
>
> Are you sure you are pointing at the right instance of Postgres?
>
> Or to put it another way, is there more than one Postgres server running
> on the machine?
>

Hi Adrian,

    Thanks for your prompt response!

    There is only one PostgreSQL instance on the PGSQL 9.2 machine.

-F

On 08/11/2015 03:06 PM, Felipe Gasper wrote:
> On 11 Aug 2015 5:56 PM, Adrian Klaver wrote:
>> On 08/11/2015 01:19 PM, Felipe Gasper wrote:
>>> Hello all,
>>>
>>>      We are noticing what appears to be a significant difference between
>>> PostgreSQL 9.x and 8.4. Not having found documentation that would point
>>> us in the direction of a good solution, I thought I’d post our issue
>>> here.
>>>
>>> On CentOS 6 we have postgresql 8.4.20 and the following pg_hba.conf:
>>>
>>> local samerole all        md5
>>> host samerole all  127.0.0.200   255.255.255.255   pam
>>> pamservice=postgresql_cpses
>>> host samerole all  127.0.0.1   255.255.255.255   md5
>>> local all postgres        md5
>>> host all postgres  127.0.0.1   255.255.255.255   md5
>>>
>>> So connections to 127.0.0.200 are handled by pam, connections to
>>> 127.0.0.1 are handled by md5.
>>>
>>> If I run:
>>> root@jason:/$ psql -h 127.0.0.200 -U pguser
>>> Password for user pguser:
>>> psql: FATAL:  PAM authentication failed for user "pguser"
>>>
>>> You can see it tried to authenticate using PAM authentication.
>>>
>>> On CentOS 7 we have postgresql 9.2.13 and the following pg_hba.conf:
>>>
>>> local samerole all        md5
>>> host samerole all  127.0.0.200   255.255.255.255   pam
>>> pamservice=postgresql_cpses
>>> host samerole all  127.0.0.1   255.255.255.255   md5
>>> local all postgres        md5
>>> host all postgres  127.0.0.1   255.255.255.255   md5
>>>
>>> But, running the command above yields a different result:
>>> root@i-0000764a [/usr/local/cpanel]# psql -h 127.0.0.200 -U pguser
>>> Password for user pguser:
>>> psql: FATAL:  password authentication failed for user "pguser"
>>>
>>> It tried to use password auth instead of PAM. We think postgresql might
>>> be seeing any 127.0.0.0/8 address as 127.0.0.1. Could you please shed
>>> some light on the issue or point us in the right direction on how to
>>> make the 9.2.13 configuration function as the 8.4.20 configuration does?
>>
>> Are you sure you are pointing at the right instance of Postgres?
>>
>> Or to put it another way, is there more than one Postgres server running
>> on the machine?
>>
>
> Hi Adrian,
>
>      Thanks for your prompt response!
>
>      There is only one PostgreSQL instance on the PGSQL 9.2 machine.

What does the Postgres log show when you do the above?

>
> -F
>


--
Adrian Klaver
adrian.klaver@aklaver.com

I think there might be some misunderstanding here:

El 11/08/15 a las 17:19, Felipe Gasper escribió:
> Hello all,
>
>     We are noticing what appears to be a significant difference between
> PostgreSQL 9.x and 8.4. Not having found documentation that would point
> us in the direction of a good solution, I thought I’d post our issue here.
>
> On CentOS 6 we have postgresql 8.4.20 and the following pg_hba.conf:
>
> local samerole all        md5
> host samerole all  127.0.0.200   255.255.255.255   pam
> pamservice=postgresql_cpses
> host samerole all  127.0.0.1   255.255.255.255   md5
> local all postgres        md5
> host all postgres  127.0.0.1   255.255.255.255   md5
>
> So connections to 127.0.0.200 are handled by pam, connections to
> 127.0.0.1 are handled by md5.

No! This means that connections that come from 127.0.0.200 are handled
by pam, not connection *to*. Same for 127.0.0.1 (which in this case
means nothing).

> If I run:
> root@jason:/$ psql -h 127.0.0.200 -U pguser
> Password for user pguser:
> psql: FATAL:  PAM authentication failed for user "pguser"

Which is the source IP where this command is executed.

> You can see it tried to authenticate using PAM authentication.
>
> On CentOS 7 we have postgresql 9.2.13 and the following pg_hba.conf:
>
> local samerole all        md5
> host samerole all  127.0.0.200   255.255.255.255   pam
> pamservice=postgresql_cpses
> host samerole all  127.0.0.1   255.255.255.255   md5
> local all postgres        md5
> host all postgres  127.0.0.1   255.255.255.255   md5
>
> But, running the command above yields a different result:
> root@i-0000764a [/usr/local/cpanel]# psql -h 127.0.0.200 -U pguser
> Password for user pguser:
> psql: FATAL:  password authentication failed for user "pguser"

Well, which is the IP from where you are running this command?

I guess the answer is in some place around that.

Regards,

--
Martín Marqués                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 11 Aug 2015 6:30 PM, Martín Marqués wrote:

>>
>> local samerole all        md5
>> host samerole all  127.0.0.200   255.255.255.255   pam
>> pamservice=postgresql_cpses
>> host samerole all  127.0.0.1   255.255.255.255   md5
>> local all postgres        md5
>> host all postgres  127.0.0.1   255.255.255.255   md5
>>
>> So connections to 127.0.0.200 are handled by pam, connections to
>> 127.0.0.1 are handled by md5.
>
> No! This means that connections that come from 127.0.0.200 are handled
> by pam, not connection *to*. Same for 127.0.0.1 (which in this case
> means nothing).

Ah … thank you!

This may indeed point to our fix … though, FWIW, it still seems curious
that the same configuration file worked as we expected in 8.4 but
changed in 9.x.

I’ll take this to my coworkers and see what comes …

-FG

Dear Martín,

    Thank you for leading us to the right solution!

    (We’re also suspecting some change in the networking stack from CentOS
6 to 7 may be playing a part here, too, FYI.)

-FG

On 11 Aug 2015 6:30 PM, Martín Marqués wrote:
> I think there might be some misunderstanding here:
>
> El 11/08/15 a las 17:19, Felipe Gasper escribió:
>> Hello all,
>>
>>      We are noticing what appears to be a significant difference between
>> PostgreSQL 9.x and 8.4. Not having found documentation that would point
>> us in the direction of a good solution, I thought I’d post our issue here.
>>
>> On CentOS 6 we have postgresql 8.4.20 and the following pg_hba.conf:
>>
>> local samerole all        md5
>> host samerole all  127.0.0.200   255.255.255.255   pam
>> pamservice=postgresql_cpses
>> host samerole all  127.0.0.1   255.255.255.255   md5
>> local all postgres        md5
>> host all postgres  127.0.0.1   255.255.255.255   md5
>>
>> So connections to 127.0.0.200 are handled by pam, connections to
>> 127.0.0.1 are handled by md5.
>
> No! This means that connections that come from 127.0.0.200 are handled
> by pam, not connection *to*. Same for 127.0.0.1 (which in this case
> means nothing).
>
>> If I run:
>> root@jason:/$ psql -h 127.0.0.200 -U pguser
>> Password for user pguser:
>> psql: FATAL:  PAM authentication failed for user "pguser"
>
> Which is the source IP where this command is executed.
>
>> You can see it tried to authenticate using PAM authentication.
>>
>> On CentOS 7 we have postgresql 9.2.13 and the following pg_hba.conf:
>>
>> local samerole all        md5
>> host samerole all  127.0.0.200   255.255.255.255   pam
>> pamservice=postgresql_cpses
>> host samerole all  127.0.0.1   255.255.255.255   md5
>> local all postgres        md5
>> host all postgres  127.0.0.1   255.255.255.255   md5
>>
>> But, running the command above yields a different result:
>> root@i-0000764a [/usr/local/cpanel]# psql -h 127.0.0.200 -U pguser
>> Password for user pguser:
>> psql: FATAL:  password authentication failed for user "pguser"
>
> Well, which is the IP from where you are running this command?
>
> I guess the answer is in some place around that.
>
> Regards,
>