md5 and trust and pg_hba.conf

[Rossi, Maria]

Hi,

Question on pg_hba.conf. I have these entries:

host all psgdba localhost trust
host all psgdba 127.0.0.1/32 trust
host all gd07 localhost trust
host all gd07 127.0.0.1/32 trust


Command to logon to Postgres:
/usr/pgsql-9.3/bin/psql -d postgres

If I am logged on 'gd07' at the server , it lets me log-on to Postgres without prompting for password
If I am logged on as 'psgdba', it prompts for password.
As you can see, both have 'trust', but 'gd07' is the instance owner.

Why does 'psgdba' prompts? What I am missing?
Your help would be much appreciated.

Thanks.
Maria

[Sameer Kumar]

Post by Rossi, Maria
Hi,
host all psgdba localhost trust
host all psgdba 127.0.0.1/32 trust
host all gd07 localhost trust
host all gd07 127.0.0.1/32 trust

Can you post the whole of pg_hba.conf file?

You might have some other entry that is taking precedence.

Post by Rossi, Maria
/usr/pgsql-9.3/bin/psql -d postgres

If you don't have PGHOST set then most likely you are not going over a tcp
connection (which is what host entries in pg_hba are used for). Instead the
local entries of pg_hba are taking effect.

Post by Rossi, Maria
If I am logged on ‘gd07’ at the server , it lets me log-on to Postgres
without prompting for password

do you have a *.pgpass* file in the home directory?

Post by Rossi, Maria
If I am logged on as ‘psgdba’, it prompts for password.
As you can see, both have ‘trust’, but ‘gd07’ is the instance owner.
Why does ‘psgdba’ prompts? What I am missing?
Your help would be much appreciated.
Thanks.
Maria

--
--
Best Regards,

*Sameer Kumar | Senior Solution Architect*

*ASHNIK PTE. LTD.*

36 Robinson Road, #14-04 City House, Singapore 068877

T: [phone] | www.ashnik.com

Skype: sameer.ashnik | M: [phone]

[Fabio Pardi]

Maria,

I share the thoughts of Samir.

Also, please note that localhost and 127.0.0.1 are the same. I would leave 'localhost' which also covers ipv6. And remove 127.0.0.1. Or the other way around if you do not use ipv6.

Aas per your problem can you copy/paste the 'prompt' you get when you try to connect as gd07?

Unless your pg_hba.conf contains more entries than here reported, you should get instead a:

psql: FATAL:  no pg_hba.conf entry for host "a.b.c.d", user "some_user", database "some_db"

So please post the entire content of it as mentioned.

Also, it might help debugging if as psgdba you run:

env | grep PG


to check the PG variables set for the user.

What happens if you run as psgdba the following?


/usr/pgsql-9.3/bin/psql -d postgres -h localhost -U psgdba


Side note: when you have everything in order, please consider to enforce authentication on your db, thus removing 'trust'. 'Trust' is your enemy. Mkey?


Regards,

fabio pardi

Hi,
 
 
host    all             psgdba           localhost           trust
host    all             psgdba           127.0.0.1/32 <http://127.0.0.1/32>            trust
host    all             gd07           localhost                trust
host    all             gd07            127.0.0.1/32 <http://127.0.0.1/32>           trust
 
Can you post the whole of pg_hba.conf file?
You might have some other entry that is taking precedence.
 
 
/usr/pgsql-9.3/bin/psql -d postgres
If you don't have PGHOST set then most likely you are not going over a tcp connection (which is what host entries in pg_hba are used for). Instead the local entries of pg_hba are taking effect.
 
If I am logged on ‘gd07’  at the server , it lets me log-on to Postgres without prompting for password
do you have a *.pgpass* file in the home directory?
 
If I am logged on as ‘psgdba’, it prompts for password.
As you can see, both have ‘trust’, but ‘gd07’ is the instance owner.
 
Why does ‘psgdba’ prompts?  What I am missing?
Your help would be much appreciated.
 
Thanks.
Maria
 
--
-- 
Best Regards,
*Sameer Kumar | Senior Solution Architect*
*ASHNIK PTE. LTD.*
36 Robinson Road, #14-04 City House, Singapore 068877
T: [phone] | www.ashnik.com <http://www.ashnik.com/>
Skype: sameer.ashnik |   M: [phone]

[Rossi, Maria]

Both have PGHOST set. gd07 has a .pgpass, but psgdba does not. Does it still need a .pgpass even if it is already ‘trust’?
Thanks.


Maria A Rossi
Database Administration
Jackson National Life
email: ***@jackson.com<mailto:***@jackson.com>
cell phone: [phone]
work phone: [phone]
work phone extension: 23099

From: Sameer Kumar <***@ashnik.com>
Sent: Monday, September 10, 2018 10:55 AM
To: Rossi, Maria <***@jackson.com>
Cc: pgsql-***@lists.postgresql.org; pgsql-***@lists.postgresql.org
Subject: Re: md5 and trust and pg_hba.conf

EXTERNAL EMAIL


On Mon, Sep 10, 2018 at 10:46 PM Rossi, Maria <***@jackson.com<mailto:***@jackson.com>> wrote:
Hi,

Question on pg_hba.conf. I have these entries:

host all psgdba localhost trust
host all psgdba 127.0.0.1/32<http://127.0.0.1/32> trust
host all gd07 localhost trust
host all gd07 127.0.0.1/32<http://127.0.0.1/32> trust


Can you post the whole of pg_hba.conf file?

You might have some other entry that is taking precedence.


Command to logon to Postgres:
/usr/pgsql-9.3/bin/psql -d postgres

If you don't have PGHOST set then most likely you are not going over a tcp connection (which is what host entries in pg_hba are used for). Instead the local entries of pg_hba are taking effect.


If I am logged on ‘gd07’ at the server , it lets me log-on to Postgres without prompting for password

do you have a *.pgpass* file in the home directory?

If I am logged on as ‘psgdba’, it prompts for password.
As you can see, both have ‘trust’, but ‘gd07’ is the instance owner.

Why does ‘psgdba’ prompts? What I am missing?
Your help would be much appreciated.

Thanks.
Maria
--
--
Best Regards,

Sameer Kumar | Senior Solution Architect

ASHNIK PTE. LTD.

36 Robinson Road, #14-04 City House, Singapore 068877

T: [phone] | www.ashnik.com<http://www.ashnik.com/>

Skype: sameer.ashnik | M: [phone]

[Image removed by sender.]

[Sameer Kumar]

Post by Rossi, Maria
Both have PGHOST set. gd07 has a .pgpass, but psgdba does not.

What is it set to?

Can you post the rest of the pg_hba.conf?

Post by Rossi, Maria
Does it still need a .pgpass even if it is already ‘trust’?

It should not.

Post by Rossi, Maria
Thanks.
*Maria A Rossi*
Database Administration
Jackson National Life
cell phone: [phone]
work phone: [phone]
work phone extension: 23099
*Sent:* Monday, September 10, 2018 10:55 AM
*Subject:* Re: md5 and trust and pg_hba.conf
*EXTERNAL EMAIL*
Hi,
host all psgdba localhost trust
host all psgdba 127.0.0.1/32 trust
host all gd07 localhost trust
host all gd07 127.0.0.1/32 trust
Can you post the whole of pg_hba.conf file?
You might have some other entry that is taking precedence.
/usr/pgsql-9.3/bin/psql -d postgres
If you don't have PGHOST set then most likely you are not going over a tcp
connection (which is what host entries in pg_hba are used for). Instead the
local entries of pg_hba are taking effect.
If I am logged on ‘gd07’ at the server , it lets me log-on to Postgres
without prompting for password
do you have a *.pgpass* file in the home directory?
If I am logged on as ‘psgdba’, it prompts for password.
As you can see, both have ‘trust’, but ‘gd07’ is the instance owner.
Why does ‘psgdba’ prompts? What I am missing?
Your help would be much appreciated.
Thanks.
Maria
--
--
Best Regards,
*Sameer Kumar | Senior Solution Architect*
*ASHNIK PTE. LTD.*
36 Robinson Road, #14
<https://maps.google.com/?q=36+Robinson+Road,+%2314&entry=gmail&source=g>-04
City House, Singapore 068877
T: [phone] <6438%203504> | www.ashnik.com
Skype: sameer.ashnik | M: [phone] <8110%200350>
[image: ~WRD000.jpg]
--

--
Best Regards,

*Sameer Kumar | Senior Solution Architect*

*ASHNIK PTE. LTD.*

36 Robinson Road, #14-04 City House, Singapore 068877

T: [phone] | www.ashnik.com

Skype: sameer.ashnik | M: [phone]

[Rossi, Maria]

Hi,

I think, I got it . Thanks for pointing me to the right direction.
Here’s the correct partial pg_hba.conf


host all psgdba localhost trust
host all psgdba 127.0.0.1/32 trust
host all psgdba 10.8.88.141/32 trust
host all psgdba all md5
host all gd07 localhost trust
host all gd07 127.0.0.1/32 trust
host all gd07 10.8.88.141/32 trust
host all gd07 all md5


This line below used to be there. This is the IP of the server. I deleted it and added a ‘trust’ line for psgdba and gd07

host all all 10.8.88.141/32 md5

Works now. Thanks for your help
Sorry for hesitating to post the entire pg_hba.conf.

Thanks again.

Maria








From: Sameer Kumar <***@ashnik.com>
Sent: Monday, September 10, 2018 11:35 AM
To: Rossi, Maria <***@jackson.com>
Cc: pgsql-***@lists.postgresql.org; pgsql-***@lists.postgresql.org
Subject: Re: md5 and trust and pg_hba.conf

EXTERNAL EMAIL


On Mon, Sep 10, 2018 at 11:22 PM Rossi, Maria <***@jackson.com<mailto:***@jackson.com>> wrote:
Both have PGHOST set. gd07 has a .pgpass, but psgdba does not.

What is it set to?

Can you post the rest of the pg_hba.conf?

Does it still need a .pgpass even if it is already ‘trust’?

It should not.
Thanks.


From: Sameer Kumar <***@ashnik.com<mailto:***@ashnik.com>>
Sent: Monday, September 10, 2018 10:55 AM
To: Rossi, Maria <***@jackson.com<mailto:***@jackson.com>>
Cc: pgsql-***@lists.postgresql.org<mailto:pgsql-***@lists.postgresql.org>; pgsql-***@lists.postgresql.org<mailto:pgsql-***@lists.postgresql.org>
Subject: Re: md5 and trust and pg_hba.conf

EXTERNAL EMAIL

On Mon, Sep 10, 2018 at 10:46 PM Rossi, Maria <***@jackson.com<mailto:***@jackson.com>> wrote:
Hi,

Question on pg_hba.conf. I have these entries:

host all psgdba localhost trust
host all psgdba 127.0.0.1/32<http://127.0.0.1/32> trust
host all gd07 localhost trust
host all gd07 127.0.0.1/32<http://127.0.0.1/32> trust


Can you post the whole of pg_hba.conf file?

You might have some other entry that is taking precedence.


Command to logon to Postgres:
/usr/pgsql-9.3/bin/psql -d postgres

If you don't have PGHOST set then most likely you are not going over a tcp connection (which is what host entries in pg_hba are used for). Instead the local entries of pg_hba are taking effect.


If I am logged on ‘gd07’ at the server , it lets me log-on to Postgres without prompting for password

do you have a *.pgpass* file in the home directory?

If I am logged on as ‘psgdba’, it prompts for password.
As you can see, both have ‘trust’, but ‘gd07’ is the instance owner.

Why does ‘psgdba’ prompts? What I am missing?
Your help would be much appreciated.

Thanks.
Maria
--
--
Best Regards,

Sameer Kumar | Senior Solution Architect

ASHNIK PTE. LTD.

36 Robinson Road, #14<https://maps.google.com/?q=36+Robinson+Road,+%2314&entry=gmail&source=g>-04 City House, Singapore 068877

T: [phone]<tel:6438%203504> | www.ashnik.com<http://www.ashnik.com/>

Skype: sameer.ashnik | M: [phone]<tel:8110%200350>
[~WRD000.jpg]
--
--
Best Regards,

Sameer Kumar | Senior Solution Architect

ASHNIK PTE. LTD.

36 Robinson Road, #14-04 City House, Singapore 068877

T: [phone] | www.ashnik.com<http://www.ashnik.com/>

Skype: sameer.ashnik | M: [phone]

[Loading Image...]

[David Raymond]

Not a problem. You're always free to remove or obscure private info when posting to a public list, especially things like IP addresses. We just recommend leaving a note of that in case "something else here" is actually important.

So you could say:

Here's pg_hba.conf (names have been changed to protect the innocent):

#stuff here for specific other databases I'm not trying to connect to
host all all ip-of-the-server/32 md5
host all user1 ip-of-the-server/32 trust
host all user1 all md5
host all user2 ip-of-the-server/32 trust
host all user2 all md5


Then if we think the problem is in the redacted section we can suggest what to look for in there.



From: Rossi, Maria [mailto:***@jackson.com]
Sent: Monday, September 10, 2018 3:47 PM
To: 'Sameer Kumar'
Cc: pgsql-***@lists.postgresql.org; pgsql-***@lists.postgresql.org
Subject: RE: md5 and trust and pg_hba.conf

Hi,

I think, I  got it .   Thanks for pointing me to the right direction.
...
Sorry for hesitating to post the

[David Raymond]

It goes through pg_hba.conf <in order>. So do you have other lines above these lines that would match a local connection, but have md5, or one of the other authentication methods set?

Since you mentioned gd07 has a pgpass file then I don't think it's a case of the pg_hba line being right for gd07 and wrong for psgdba, I think they're both "wrong" in that a previous line is being used which prompts for a password, and since gd07 has a password stored, then it uses that and winds up connected ok in the end.


From: Rossi, Maria [mailto:***@jackson.com]
Sent: Monday, September 10, 2018 10:46 AM
To: 'pgsql-***@lists.postgresql.org'; 'pgsql-***@lists.postgresql.org'
Subject: md5 and trust and pg_hba.conf

Hi,

Question on pg_hba.conf.  I have these entries:

host    all             psgdba           localhost           trust
host    all             psgdba           127.0.0.1/32            trust
host    all             gd07           localhost                trust
host    all             gd07            127.0.0.1/32           trust


Command to logon to Postgres:
/usr/pgsql-9.3/bin/psql -d postgres

If I am logged on ‘gd07’  at the server , it lets me log-on to Postgres without prompting for password
If I am logged on as ‘psgdba’, it prompts for password.
As you can see, both have ‘trust’, but ‘gd07’ is the instance owner.

Why does ‘psgdba’ prompts?  What I am missing?
Your help would be much appreciated.

Thanks.
Maria