agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedmd5 and trust and pg_hba.conf
8+ messages / 4 participants
[nested] [flat]
* md5 and trust and pg_hba.conf
@ 2018-09-10 14:45 Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 2 replies; 8+ messages in thread
From: Rossi, Maria @ 2018-09-10 14:45 UTC (permalink / raw)
To: 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: md5 and trust and pg_hba.conf
@ 2018-09-10 14:55 Sameer Kumar <sameer.kumar@ashnik.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
1 sibling, 2 replies; 8+ messages in thread
From: Sameer Kumar @ 2018-09-10 14:55 UTC (permalink / raw)
To: Rossi, Maria <maria.rossi@jackson.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
On Mon, Sep 10, 2018 at 10:46 PM Rossi, Maria <maria.rossi@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 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.
>
>
> 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: +65 6438 3504 | www.ashnik.com
Skype: sameer.ashnik | M: +65 8110 0350
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: md5 and trust and pg_hba.conf
@ 2018-09-10 15:17 Fabio Pardi <f.pardi@portavita.eu>
parent: Sameer Kumar <sameer.kumar@ashnik.com>
1 sibling, 0 replies; 8+ messages in thread
From: Fabio Pardi @ 2018-09-10 15:17 UTC (permalink / raw)
To: pgsql-novice@lists.postgresql.org
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
On 10/09/18 16:55, Sameer Kumar wrote:
>
>
> On Mon, Sep 10, 2018 at 10:46 PM Rossi, Maria <maria.rossi@jackson.com <mailto:maria.rossi@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: +65 6438 3504 | www.ashnik.com <http://www.ashnik.com/;
>
> Skype: sameer.ashnik | M: +65 8110 0350
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: md5 and trust and pg_hba.conf
@ 2018-09-10 15:22 Rossi, Maria <maria.rossi@jackson.com>
parent: Sameer Kumar <sameer.kumar@ashnik.com>
1 sibling, 1 reply; 8+ messages in thread
From: Rossi, Maria @ 2018-09-10 15:22 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org>
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: maria.rossi@jackson.com<mailto:maria.rossi@jackson.com>
cell phone: 517.256.4392
work phone: 517.367.3099
work phone extension: 23099
From: Sameer Kumar <sameer.kumar@ashnik.com>
Sent: Monday, September 10, 2018 10:55 AM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: pgsql-sql@lists.postgresql.org; pgsql-novice@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 <maria.rossi@jackson.com<mailto:maria.rossi@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: +65 6438 3504 | www.ashnik.com<http://www.ashnik.com/;
Skype: sameer.ashnik | M: +65 8110 0350
[Image removed by sender.]
Attachments:
[image/jpeg] ~WRD000.jpg (823B, ../../539851b398294c5994ee8c425c3710ac@DC03PXMBP003.jacksonnational.com/3-~WRD000.jpg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: md5 and trust and pg_hba.conf
@ 2018-09-10 15:35 Sameer Kumar <sameer.kumar@ashnik.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sameer Kumar @ 2018-09-10 15:35 UTC (permalink / raw)
To: Rossi, Maria <maria.rossi@jackson.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
On Mon, Sep 10, 2018 at 11:22 PM Rossi, Maria <maria.rossi@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.
>
>
>
>
>
> *Maria A Rossi*
>
> Database Administration
>
> Jackson National Life
>
> email: maria.rossi@jackson.com
> cell phone: 517.256.4392
>
> work phone: 517.367.3099
> work phone extension: 23099
>
>
>
> *From:* Sameer Kumar <sameer.kumar@ashnik.com>
> *Sent:* Monday, September 10, 2018 10:55 AM
> *To:* Rossi, Maria <maria.rossi@jackson.com>
> *Cc:* pgsql-sql@lists.postgresql.org; pgsql-novice@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 <maria.rossi@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 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.
>
>
>
>
>
> 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: +65 6438 3504 <6438%203504> | www.ashnik.com
>
> Skype: sameer.ashnik | M: +65 8110 0350 <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: +65 6438 3504 | www.ashnik.com
Skype: sameer.ashnik | M: +65 8110 0350
Attachments:
[image/jpeg] ~WRD000.jpg (823B, ../../CADp-Sm4qBwxXuWrOK-KuNiY0XPt-2-5hbDLCfR3bprcfdM7YUA@mail.gmail.com/3-~WRD000.jpg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: md5 and trust and pg_hba.conf
@ 2018-09-10 15:41 David Raymond <David.Raymond@tomtom.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
1 sibling, 0 replies; 8+ messages in thread
From: David Raymond @ 2018-09-10 15:41 UTC (permalink / raw)
To: 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org>
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:maria.rossi@jackson.com]
Sent: Monday, September 10, 2018 10:46 AM
To: 'pgsql-sql@lists.postgresql.org'; 'pgsql-novice@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: md5 and trust and pg_hba.conf
@ 2018-09-10 19:47 Rossi, Maria <maria.rossi@jackson.com>
parent: Sameer Kumar <sameer.kumar@ashnik.com>
0 siblings, 1 reply; 8+ messages in thread
From: Rossi, Maria @ 2018-09-10 19:47 UTC (permalink / raw)
To: Sameer Kumar <sameer.kumar@ashnik.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.org <pgsql-novice@lists.postgresql.org>
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 <sameer.kumar@ashnik.com>
Sent: Monday, September 10, 2018 11:35 AM
To: Rossi, Maria <maria.rossi@jackson.com>
Cc: pgsql-sql@lists.postgresql.org; pgsql-novice@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 <maria.rossi@jackson.com<mailto:maria.rossi@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 <sameer.kumar@ashnik.com<mailto:sameer.kumar@ashnik.com>>
Sent: Monday, September 10, 2018 10:55 AM
To: Rossi, Maria <maria.rossi@jackson.com<mailto:maria.rossi@jackson.com>>
Cc: pgsql-sql@lists.postgresql.org<mailto:pgsql-sql@lists.postgresql.org>; pgsql-novice@lists.postgresql.org<mailto:pgsql-novice@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 <maria.rossi@jackson.com<mailto:maria.rossi@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: +65 6438 3504<tel:6438%203504> | www.ashnik.com<http://www.ashnik.com/;
Skype: sameer.ashnik | M: +65 8110 0350<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: +65 6438 3504 | www.ashnik.com<http://www.ashnik.com/;
Skype: sameer.ashnik | M: +65 8110 0350
[https://www.ashnik.com/wp-content/uploads/2017/03/Final-Email-Signature.jpg]
Attachments:
[image/jpeg] image001.jpg (823B, ../../7974b8ff73cc4119804e0e8c8f900a26@DC03PXMBP003.jacksonnational.com/3-image001.jpg)
download | view image
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: md5 and trust and pg_hba.conf
@ 2018-09-10 20:39 David Raymond <David.Raymond@tomtom.com>
parent: Rossi, Maria <maria.rossi@jackson.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Raymond @ 2018-09-10 20:39 UTC (permalink / raw)
To: 'pgsql-sql@lists.postgresql.org' <pgsql-sql@lists.postgresql.org>; 'pgsql-novice@lists.postgresql.org' <pgsql-novice@lists.postgresql.org>
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:maria.rossi@jackson.com]
Sent: Monday, September 10, 2018 3:47 PM
To: 'Sameer Kumar'
Cc: pgsql-sql@lists.postgresql.org; pgsql-novice@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 entire pg_hba.conf.
Thanks again.
Maria
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-09-10 20:39 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-10 14:45 md5 and trust and pg_hba.conf Rossi, Maria <maria.rossi@jackson.com>
2018-09-10 14:55 ` Sameer Kumar <sameer.kumar@ashnik.com>
2018-09-10 15:17 ` Fabio Pardi <f.pardi@portavita.eu>
2018-09-10 15:22 ` Rossi, Maria <maria.rossi@jackson.com>
2018-09-10 15:35 ` Sameer Kumar <sameer.kumar@ashnik.com>
2018-09-10 19:47 ` Rossi, Maria <maria.rossi@jackson.com>
2018-09-10 20:39 ` David Raymond <David.Raymond@tomtom.com>
2018-09-10 15:41 ` David Raymond <David.Raymond@tomtom.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox