Hi All-

I'm having some issues with connecting to my servers if I put 'md5' as the
connection method in my pg_hba.conf (which is what I want!). If I put
'trust', I can connect without any issues.

I built 8.0.8 from source because we wanted to use SSL; and my pg_hba.conf
file currently looks as follows:

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
#host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 trust
hostssl all all 127.0.0.1/32 trust

If I change the hostssl line to: hostssl all all 127.0.0.1/32 md5, restart
the server, and attempt to connect via pgadmin, I see the message
'Connecting to the database... Failed.' If I attempt to connect to a
database using the command line:
C:\msys\1.0\local\pgsql\bin>psql -d apt -U postgres
Password:
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user
"postgres", database "apt", SSL off
C:\msys\1.0\local\pgsql\bin>

But if I change the hostssl line back to: hostssl all all 127.0.0.1/32 trust
and restart the server, I can connect through both pgadmin and the command
line.

Would I have to had done something special when building Postgres to enable
the use of md5? My command line parameters were:
../configure --with-openssl --with-includes=/usr/local/include --with-libraries=/usr/local/lib
--without-zlib

Thanks in advance for all of your help! If you need any more info, just let
me know. I really need to get this issue resolved.

Thanks,
-Jeanna


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 26 Sep 2006, Jeanna Geier wrote:

> Hi All-
>
> If I change the hostssl line to: hostssl all all 127.0.0.1/32 md5, restart
> the server, and attempt to connect via pgadmin, I see the message 'Connecting
> to the database... Failed.' If I attempt to connect to a database using the
> command line:
> C:\msys\1.0\local\pgsql\bin>psql -d apt -U postgres
> Password:
> psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres",
> database "apt", SSL off
> C:\msys\1.0\local\pgsql\bin>
>

Jeanna,

It seems that for some reason either your server or your client are not trying
to use SSL. Note the: "SSL off" in the error message you received. Do you
have a server.crt in the data directory of the postgres server?

When you have the hostssl line set for trust, do you get something like this
when you connect with psql:

Welcome to psql 8.0.8, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Or are you missing the SSL connection line?

--
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/


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 26 Sep 2006, Jeff Frost wrote:

> It seems that for some reason either your server or your client are not
> trying to use SSL. Note the: "SSL off" in the error message you received.
> Do you have a server.crt in the data directory of the postgres server?
>

I guess I should have also asked if you have the

ssl = true

in postgresql.conf?

---
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hi Jeff-

Thanks so much for the reply.

Yes, I have ssl=true in postgresql.conf. (password encryption is commented
out - is that OK?: #password_encryption = true)

Also, yes, server.crt is in the 'data' directory of my postgres server, as
is server.key.

And, yes, when I am able to start Postgres (when using 'trust' in the
pg_hba.conf file vs. 'md5'), I do so the 'SSL connection' line:

C:\msys\1.0\local\pgsql\bin>psql -d apt -U postgres
Welcome to psql 8.0.8, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See psql reference
page "Notes for Windows users" for details.

apt=#

It's only when I change the connection method to 'md5' that I'm running into
problems -- then I cannot connect from pgadmin or the command line.

Thanks,
-Jeanna

----- Original Message -----
From: "Jeff Frost" <jeff@frostconsultingllc.com>
To: "Jeanna Geier" <jgeier@apt-cafm.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Tuesday, September 26, 2006 10:05 AM
Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues


> On Tue, 26 Sep 2006, Jeff Frost wrote:
>
>> It seems that for some reason either your server or your client are not
>> trying to use SSL. Note the: "SSL off" in the error message you
>> received. Do you have a server.crt in the data directory of the postgres
>> server?
>>
>
> I guess I should have also asked if you have the
>
> ssl = true
>
> in postgresql.conf?
>
> ---
> Jeff Frost, Owner <jeff@frostconsultingllc.com>
> Frost Consulting, LLC http://www.frostconsultingllc.com/
> Phone: 650-780-7908 FAX: 650-649-1954
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Tue, 26 Sep 2006, Jeanna Geier wrote:

> Yes, I have ssl=true in postgresql.conf. (password encryption is commented
> out - is that OK?: #password_encryption = true)
>
> Also, yes, server.crt is in the 'data' directory of my postgres server, as is
> server.key.
>
> And, yes, when I am able to start Postgres (when using 'trust' in the
> pg_hba.conf file vs. 'md5'), I do so the 'SSL connection' line:
>
> It's only when I change the connection method to 'md5' that I'm running into
> problems -- then I cannot connect from pgadmin or the command line.
>

I just went through setting up SSL on the windows postgresql server and here
are two other things to check:

Did you restart the postgresql service after making the changes? (I'm not
sure how to issue a reload with the windows version.)

Also, did you make sure that server.crt and server.key are accessible for read
by the account under which the postgresql service is running?

After verifying both of those, I got a working SSL connection under windows:

C:\Program Files\PostgreSQL\8.1\bin>psql -U postgres postgres
Password for user postgres:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

Warning: Console code page (437) differs from Windows code page (1252)
8-bit characters may not work correctly. See psql reference
page "Notes for Windows users" for details.

postgres=#


--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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/
>
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq