Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues - Mailing list pgsql-hackers
From | Jeff Frost |
---|---|
Subject | Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues |
Date | |
Msg-id | Pine.LNX.4.64.0609260924260.32444@discord.home.frostconsultingllc.com Whole thread Raw |
In response to | Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues (Jeff Frost <jeff@frostconsultingllc.com>) |
List | pgsql-hackers |
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
pgsql-hackers by date: