Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues

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