Thread: pg_hba.conf: 'trust' vs. 'md5' Issues
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
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/
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
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
>
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
"Jeanna Geier" <jgeier@apt-cafm.com> writes:
> [ hostssl works with 'trust' but not 'md5' ]
> 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 experimented with this using CVS HEAD, and found that SSL+md5 works
fine as long as I enter the correct password ... but if I give a wrong
password I get
$ psql -h localhost regression
Password:
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "tgl", database "regression", SSL off
$
which is at best pretty misleading :-(. I think libpq is probably
mishandling the "bad password" error and concluding that it should fall
back to a non-SSL connection, which the server then rejects. Will look
into it.
As for Jeanna's problem, I don't see any password prompt at all in her
example. I've forgotten the details, but wasn't there a password
prompting problem with 8.0.x on Windows?
regards, tom lane
On Tue, 26 Sep 2006, Tom Lane wrote: > "Jeanna Geier" <jgeier@apt-cafm.com> writes: >> [ hostssl works with 'trust' but not 'md5' ] >> 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. > > > As for Jeanna's problem, I don't see any password prompt at all in her > example. I've forgotten the details, but wasn't there a password > prompting problem with 8.0.x on Windows? > It worked great with 8.1.4. Let me download 8.0.8 and try that on Windows since that appears to be what she's using. More later.
On Tue, 26 Sep 2006, Jeff Frost wrote:
>> As for Jeanna's problem, I don't see any password prompt at all in her
>> example. I've forgotten the details, but wasn't there a password
>> prompting problem with 8.0.x on Windows?
>>
>
> It worked great with 8.1.4. Let me download 8.0.8 and try that on Windows
> since that appears to be what she's using. More later.
Looks like the windows 8.0.8 psql worked fine against my running windows
8.1.4 server:
C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres postgres
Password:
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.
postgres=#
Do you remember if the problem was on the 8.0.8 server side that caused the
lack of prompting?
--
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/
Jeff Frost <jeff@frostconsultingllc.com> writes:
> Do you remember if the problem was on the 8.0.8 server side that caused the
> lack of prompting?
No, I'm pretty sure it was a client-side issue (and I thought we'd fixed
it by 8.0.8 anyway, so I'm glad to see your test agrees).
Jeanna, do you maybe have a pgpass file or something else that would
short-circuit the password prompt? It could be that your problem boils
down to supplying the wrong password behind-the-scenes.
regards, tom lane
On Tue, 26 Sep 2006, Tom Lane wrote: > Jeff Frost <jeff@frostconsultingllc.com> writes: >> Do you remember if the problem was on the 8.0.8 server side that caused the >> lack of prompting? > > No, I'm pretty sure it was a client-side issue (and I thought we'd fixed > it by 8.0.8 anyway, so I'm glad to see your test agrees). > > Jeanna, do you maybe have a pgpass file or something else that would > short-circuit the password prompt? It could be that your problem boils > down to supplying the wrong password behind-the-scenes. Interestingly, I receive the same error when I disable SSL on the server: C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres postgres psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off But, when I put the trust line back with hostssl, I do not get connected as per her original indication. Of course this is with my 8.1.4 windows server and not 8.0.8. Is it possible that 8.0.8 was more liberal with the hostssl vs host interpretation if ssl was disabled? I also tried making it so the postgres user could not read the server.crt and server.key files and this yielded the same result: C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres postgres psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off Can anyone think of an iteration I haven't tried? I'll go reset the postgres user password to something I know and start the 8.0.8 server by hand momentarily. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
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.
regards, tom lane
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/
On Tue, 26 Sep 2006, Jeff Frost wrote:
> But, when I put the trust line back with hostssl, I do not get connected as
> per her original indication. Of course this is with my 8.1.4 windows server
> and not 8.0.8. Is it possible that 8.0.8 was more liberal with the hostssl
> vs host interpretation if ssl was disabled?
>
> I also tried making it so the postgres user could not read the server.crt and
> server.key files and this yielded the same result:
>
> C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres postgres
> psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres",
> database "postgres", SSL off
>
> Can anyone think of an iteration I haven't tried? I'll go reset the postgres
> user password to something I know and start the 8.0.8 server by hand
> momentarily.
Well, here's what happens with 8.0.8 server and 8.0.8 client. I ran
through as many iterations as I could think of, so this gets rather long. If
you just want to skip to the bottom and see that Tom appears to have nailed
the cause, that'll save you some reading. :-)
With proper server.crt and server.key, and ssl=true and this pg_hba.conf:
# 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 md5
I get:
C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres template1
Password:
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.
So that seems to work ok. With ssl=false and the same settings above, I get:
C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres template1
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres",
database "template1", SSL off
Also, as you would expect.
If postgres can't read server.key (with ssl=true), you get the following error
when starting the postmaster (as expected):
C:\temp\pgsql\lib>"..\bin"\postmaster -D "../data"
FATAL: could not load private key file "C:/temp/pgsql/lib/../data/server.key":
Input/output error
If postgres can read server.key (with ssl=true), but can't read server.crt you
get the expected error:
C:\temp\pgsql\lib>"..\bin"\postmaster -D "../data" FATAL: could not load
server certificate file "C:/temp/pgsql/lib/../data/server.crt": Input/output
error
Testing the pgpass theory of Tom's seems to make Tom the winner again. I
modified my %appdata%\postgresql\pgpass.conf and put a bad password in like
so:
localhost:5432:*:postgres:p0stgres
I was then rewarded with the exact same error message Jeanna is receiving:
C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres template1
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres",
database "template1", SSL off
Removing it and I'm back in business:
C:\temp\pgsql\lib>..\bin\psql -h localhost -U postgres template1
Password:
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.
template1=#
So, I'd say that's near definitive proof. Jeanna, check your
%appdata%\postgresql\pgpass.conf. The default path for that would be
something like this for my user jeff:
C:\Documents and Settings\jeff\Application Data\postgresql
BTW, looks like that's where pgadmin3 stores passwords (I was suprised to see
a pgpass.conf full of various connection info before I realized pgadmin must
be storing them here), so that's likely how you would've gotten the wrong one
in there in the first place.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
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/
>
>
On Tue, 26 Sep 2006, Jeanna Geier wrote: > 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) Jeanna, see my earlier email regarding all the different variations and also where to find your pgpass file on windows. But, please note, you don't have to build the windows version from source to use SSL. The two binary versions I was using for testing both worked fine with SSL.
Searched again for 'pgpass' and for the 'Application Data' directory with no luck... And, tell me it ain't so "you don't have to build the windows version from source to use SSL" -- I had two seperate posters tell me that I did and I wrestled with it for a bit...for nothing?? Ah, live and learn! :o) I don't think I'll consider myself a 'newbie' after this project is done. :o) ----- Original Message ----- From: "Jeff Frost" <jeff@frostconsultingllc.com> To: "Jeanna Geier" <jgeier@apt-cafm.com> Cc: ""Tom Lane"" <tgl@sss.pgh.pa.us>; <pgsql-admin@postgresql.org>; <pgsql-hackers@postgresql.org> Sent: Tuesday, September 26, 2006 12:16 PM Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues > On Tue, 26 Sep 2006, Jeanna Geier wrote: > >> 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) > > > Jeanna, see my earlier email regarding all the different variations and > also where to find your pgpass file on windows. But, please note, you > don't have to build the windows version from source to use SSL. The two > binary versions I was using for testing both worked fine with SSL. >
Jeanna Geier wrote: > Searched again for 'pgpass' and for the 'Application Data' directory with > no luck... The file is called "pgpass.conf" on Windows. As for the "Application Data", it may be called differently if your Windows is localized -- try looking for %APPDATA%. (I think I'd do this by opening a terminal window and "echo %APPDATA%" or "cd %APPDATA%"). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Tue, 26 Sep 2006, Alvaro Herrera wrote: > Jeanna Geier wrote: >> Searched again for 'pgpass' and for the 'Application Data' directory with >> no luck... > > The file is called "pgpass.conf" on Windows. As for the "Application > Data", it may be called differently if your Windows is localized -- try > looking for %APPDATA%. (I think I'd do this by opening a terminal > window and "echo %APPDATA%" or "cd %APPDATA%"). You can also just click start, run then type %appdata% and windows will open an explorer window in that directory. I guess it's also possible you need to turn on the view hidden and system directories in the explorer options to see/find in that directory, but I'm not sure. -- 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/
Thank you, Thank you, Thank you!! :o)
Jeff - Thanks in particular for your help on this, it is greatly
appreciated!
It was a hidden folder, but not anymore!! I found the file and re-set the
password for the 'postgres' user and can now connect using my 'md5' hostssl
connection:
hostssl all all 127.0.0.1/32 md5
______________________________
C:\msys\1.0\local\pgsql\bin>psql -d apt -U postgres
Password:
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=#
Again, thanks for everyone's time and effort on this! This mailing list is
top-notch!!
-Jeanna
----- Original Message -----
From: "Jeff Frost" <jeff@frostconsultingllc.com>
To: "Alvaro Herrera" <alvherre@commandprompt.com>
Cc: "Jeanna Geier" <jgeier@apt-cafm.com>; "Tom Lane" <tgl@sss.pgh.pa.us>;
<pgsql-admin@postgresql.org>; <pgsql-hackers@postgresql.org>
Sent: Tuesday, September 26, 2006 12:35 PM
Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
> On Tue, 26 Sep 2006, Alvaro Herrera wrote:
>
>> Jeanna Geier wrote:
>>> Searched again for 'pgpass' and for the 'Application Data' directory
>>> with
>>> no luck...
>>
>> The file is called "pgpass.conf" on Windows. As for the "Application
>> Data", it may be called differently if your Windows is localized -- try
>> looking for %APPDATA%. (I think I'd do this by opening a terminal
>> window and "echo %APPDATA%" or "cd %APPDATA%").
>
> You can also just click start, run then type %appdata% and windows will
> open an explorer window in that directory. I guess it's also possible you
> need to turn on the view hidden and system directories in the explorer
> options to see/find in that directory, but I'm not sure.
>
> --
> 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/
>
>
I have run into the issue with our linux boxes connecting with the JDBC
driver. Lucky for us our connections already go over encrypted VPN
connections so I could get by with the following in my pg_hba.conf
hostssl all all 192.168.176.0 255.255.255.0 md5
host all all 192.168.176.2 255.255.255.255 md5
host all all 192.168.176.9 255.255.255.255 md5
host all all 192.168.176.21 255.255.255.255 md5
host all all 192.168.176.22 255.255.255.255 md5
This will select the SSL connection first and then fall back to the non-ssl
which are restricted to our tomcat web servers.
This work around was set up in 7.4 of postgres. We are currently upgrading
to 8.1, but I have not had a chance to revisit the SSL with JDBC yet.
Woody
IGLASS Networks
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Jeanna Geier
Sent: Tuesday, September 26, 2006 1:24 PM
To: Jeff Frost
Cc: "Tom Lane"; pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Searched again for 'pgpass' and for the 'Application Data' directory with no
luck...
And, tell me it ain't so "you don't have to build the windows version from
source to use SSL" -- I had two seperate posters tell me that I did and I
wrestled with it for a bit...for nothing?? Ah, live and learn! :o) I don't
think I'll consider myself a 'newbie' after this project is done. :o)
----- Original Message -----
From: "Jeff Frost" <jeff@frostconsultingllc.com>
To: "Jeanna Geier" <jgeier@apt-cafm.com>
Cc: ""Tom Lane"" <tgl@sss.pgh.pa.us>; <pgsql-admin@postgresql.org>;
<pgsql-hackers@postgresql.org>
Sent: Tuesday, September 26, 2006 12:16 PM
Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
> On Tue, 26 Sep 2006, Jeanna Geier wrote:
>
>> 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)
>
>
> Jeanna, see my earlier email regarding all the different variations
> and also where to find your pgpass file on windows. But, please note,
> you don't have to build the windows version from source to use SSL.
> The two binary versions I was using for testing both worked fine with SSL.
>
---------------------------(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