Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues - Mailing list pgsql-hackers

OK, so after doing some more testing and configuring to see if I can narrow
this down, I'm more confused than ever! =)  Because now I cannot connect to
my database unless the method is 'trust'; shouldn't I be able to connect
using the correct password if 'password' is the method in the pg_hba.conf
file?

To look into Tom's theory of the password being short-circuited, I did a
search on my pc for 'pgpass' and only came up with an html file, and I don't
think that's doing it...  and I don't know of any other places where this
could/would be occuring.

In my pg_hba.conf file I set up six different configurations (restarting the
server between each one, to be sure it was using the new settings), with the
following results:

 No HostSSL
---------------
1) hostssl disabled; host enabled - method: md5
    log-in results:   pgadmin: passwd prompt & passwd authentication failed
                           cmd pmpt: passwd prompt & psql: FATAL:  password
authentication failed for user "postgres"

2) hostssl disabled; host enabled - method: password
    log-in results:   pgadmin: passwd prompt & passwd authentication failed
                           cmd pmpt: passwd prompt & psql: FATAL:  password
authentication failed for user "postgres"

3) hostssl disabled; host enabled - method: trust
    log-in results:   pgadmin: passwd prompt & connects after password is
entered
                            cmd pmpt: no password prompt & connects with
"SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)" line displayed

 With HostSSL
-----------------
4) host disabled; hostssl enabled - method: md5
    log-in results:   pgadmin: no passwd prompt; "Connecting to
database....Failed."
                           cmd pmpt: passwd prompt & psql: FATAL:  no
pg_hba.conf entry for host "127.0.0.1", user "postgres", database "apt", SSL
off

5) host disabled; hostssl enabled - method: password
    log-in results:   pgadmin: no passwd prompt; "Connecting to
database....Failed."
                           cmd pmpt: passwd prompt & psql: FATAL:  no
pg_hba.conf entry for host "127.0.0.1", user "postgres", database "apt", SSL
off

6) host disabled; hostssl enabled - method: trust
    log-in results:   pgadmin: passwd prompt & connects after password is
entered
                            cmd pmpt: no password prompt & connects with
"SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)" line displayed


Any thoughts??  Like I said previously, I did build this on Windows from
source so we could use the SSL option.....could I have missed something when
I was doing that? (It was my first time and I was following instructions
from the INSTALL docs)

Thanks so much for your time and assistance!
-Jeanna

----- Original Message -----
From: "Jeff Frost" <jeff@frostconsultingllc.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Jeanna Geier" <jgeier@apt-cafm.com>; <pgsql-admin@postgresql.org>;
<pgsql-hackers@postgresql.org>
Sent: Tuesday, September 26, 2006 11:40 AM
Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues


> On Tue, 26 Sep 2006, Tom Lane wrote:
>
>> Jeff Frost <jeff@frostconsultingllc.com> writes:
>>> Interestingly, I receive the same error when I disable SSL on the
>>> server:
>>
>> If SSL is disabled then hostssl lines in pg_hba.conf effectively become
>> no-ops --- they can never be matched since no incoming connection will
>> be SSL-ified.  So that part of it sounds reasonable to me.  (Perhaps we
>> could log some kind of complaint in this case, though the easy places
>> to put in such a message would generate an unacceptably large number of
>> repetitions of the message :-()
>>
>>> But, when I put the trust line back with hostssl, I do not get connected
>>> as
>>> per her original indication.
>>
>> Please be clearer about what you mean here --- Jeanna *was* able to
>> connect in this case, if I'm not totally confused.
>
> Sorry, Tom.  I should have been more clear.  I was trying to reproduce her
> problem by leaving ssl=off in the postgresql.conf (as if she didn't
> restart postgres after the pg_hba.conf change), to see if the hostssl line
> magically became a host line.  But, she later indicated that she saw the
> SSL encryption info in the psql line when she got connected with this
> method, so that kind of ruled that out.  See my later e-mail where I tried
> lots of different methods.
>
> I suppose it's also possible there is a host all all 127.0.0.1/32 trust
> line later in the pg_hba.conf that it's falling through and hitting, but I
> think your .pgpass theory is the best.
>
> --
> Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing
> http://www.frostconsultingllc.com/ http://www.motonation.com/
> http://www.suomy-usa.com/ http://www.motionpro.com/
> http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/
> http://www.zoomzoomtrackdays.com/ http://www.braking.com/
>
>