Обсуждение: SSL and md5 password?
Hi all, I'm looking to add an md5-based user to a postgres server, and I can't seem to get psql to connect using SSL. Normally I use Kerberos to connect, and that works flawlessly with SSL. I'd much prefer to use SSL connections, so I'm wondering if there's something I'm doing wrong. Here's what happens with a Kerberos connection...works just fine, connects using SSL: $ /s/postgresql/bin/psql -h dumbo.cs.wisc.edu -p 5432 koczan_test Welcome to psql 8.3.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) koczan_test=> And an md5 connection... $ /s/postgresql/bin/psql -h dumbo.cs.wisc.edu -p 5432 -U test_md5 koczan_test Password for user test_md5: psql: FATAL: no pg_hba.conf entry for host "128.105.162.36", user "test_md5", database "koczan_test", SSL off Here's my pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD # this part disables remote "postgres" superuser connections hostssl all postgres 127.0.0.1/32 krb5 hostssl all postgres 128.105.2.15/32 krb5 hostssl all postgres 128.105.207.19/32 krb5 hostssl all postgres 128.105.0.0/16 reject hostssl all postgres 198.133.224.0/24 reject # Testing out md5 users hostssl all test_md5 128.105.0.0/16 md5 hostssl all test_md5 198.133.224.0/24 md5 # this part enables non-superuser connections hostssl all nobody 128.105.0.0/16 trust hostssl all nobody 198.133.224.0/24 trust hostssl all all 128.105.0.0/16 krb5 hostssl all all 198.133.224.0/24 krb5 And "ssl = on" in my postgresql.conf. Am I missing something, or am I just SOL when using SSL? Thanks, Peter
>>> "Peter Koczan" <pjkoczan@gmail.com> wrote: > psql: FATAL: no pg_hba.conf entry for host "128.105.162.36", user > "test_md5", database "koczan_test", SSL off > hostssl all postgres 128.105.0.0/16 reject > hostssl all postgres 198.133.224.0/24 reject > > # Testing out md5 users > hostssl all test_md5 128.105.0.0/16 md5 > hostssl all test_md5 198.133.224.0/24 md5 I think it uses the first matching entry in pg_hba.conf. What happens with the matching "reject" line? -Kevin
>>> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: >> hostssl all postgres 128.105.0.0/16 reject >> hostssl all postgres 198.133.224.0/24 reject > I think it uses the first matching entry in pg_hba.conf. > What happens with the matching "reject" line? Never mind. Somehow I missed the fact that the reject line was just for the postgres user. -Kevin
On Wed, Oct 29, 2008 at 11:07 AM, Peter Koczan <pjkoczan@gmail.com> wrote: > Hi all, > > I'm looking to add an md5-based user to a postgres server, and I can't > seem to get psql to connect using SSL. Normally I use Kerberos to > connect, and that works flawlessly with SSL. I'd much prefer to use > SSL connections, so I'm wondering if there's something I'm doing > wrong. > > Here's what happens with a Kerberos connection...works just fine, > connects using SSL: > > $ /s/postgresql/bin/psql -h dumbo.cs.wisc.edu -p 5432 koczan_test > Welcome to psql 8.3.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) > > koczan_test=> > > And an md5 connection... > > $ /s/postgresql/bin/psql -h dumbo.cs.wisc.edu -p 5432 -U test_md5 koczan_test > Password for user test_md5: > psql: FATAL: no pg_hba.conf entry for host "128.105.162.36", user > "test_md5", database "koczan_test", SSL off According to this, you're trying to connect with SSL off, right? I don't think you've got any matching lines for that in your pg_hba.conf. But I could be reading that wrong.
Scott Marlowe escribió: > On Wed, Oct 29, 2008 at 11:07 AM, Peter Koczan <pjkoczan@gmail.com> wrote: > > And an md5 connection... > > > > $ /s/postgresql/bin/psql -h dumbo.cs.wisc.edu -p 5432 -U test_md5 koczan_test > > Password for user test_md5: > > psql: FATAL: no pg_hba.conf entry for host "128.105.162.36", user > > "test_md5", database "koczan_test", SSL off > > According to this, you're trying to connect with SSL off, right? I > don't think you've got any matching lines for that in your > pg_hba.conf. But I could be reading that wrong. libpq tries to connect with SSL on and then falls back to SSL off. If the latter fails, the error message from the first attempt has been overwritten, and only the second one is logged. This has been fixed by Magnus on CVS HEAD. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes: > libpq tries to connect with SSL on and then falls back to SSL off. If > the latter fails, the error message from the first attempt has been > overwritten, and only the second one is logged. > This has been fixed by Magnus on CVS HEAD. Right. So in the meantime, what Peter should do is try the problem case after export PGSSLMODE=require so that he can see the error message that's relevant to his desired use. regards, tom lane
On Wed, Oct 29, 2008 at 1:49 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > According to this, you're trying to connect with SSL off, right? I > don't think you've got any matching lines for that in your > pg_hba.conf. But I could be reading that wrong. Well, psql is trying to connect with SSL off even though postgres was built with SSL support, the server has SSL turned on, and psql connects with SSL by default for Kerberos connections. Where I was getting confused was that, for some reason, md5 authentication wasn't using SSL (or at least that's what the error suggested) and the only thing I changed was what user/auth-method I was using. On Wed, Oct 29, 2008 at 9:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: >> libpq tries to connect with SSL on and then falls back to SSL off. If >> the latter fails, the error message from the first attempt has been >> overwritten, and only the second one is logged. >> This has been fixed by Magnus on CVS HEAD. > > Right. So in the meantime, what Peter should do is try the problem case > after > export PGSSLMODE=require > so that he can see the error message that's relevant to his desired use. After exporting that environment variable, it works. Thanks. Peter
"Peter Koczan" <pjkoczan@gmail.com> writes: > On Wed, Oct 29, 2008 at 9:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Right. So in the meantime, what Peter should do is try the problem case >> after >> export PGSSLMODE=require >> so that he can see the error message that's relevant to his desired use. > After exporting that environment variable, it works. Thanks. Huh? That makes no sense at all ... unless you had it set to "disable" before. regards, tom lane
On Wed, Oct 29, 2008 at 10:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Peter Koczan" <pjkoczan@gmail.com> writes: >> On Wed, Oct 29, 2008 at 9:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Right. So in the meantime, what Peter should do is try the problem case >>> after >>> export PGSSLMODE=require >>> so that he can see the error message that's relevant to his desired use. > >> After exporting that environment variable, it works. Thanks. > > Huh? That makes no sense at all ... unless you had it set to "disable" > before. Oh, I was just mistyping the password and had to reset it. I was just getting confused by the "SSL off" error message. Now I feel like a fool (*turns away in shame*), though I'm glad we got that cleared up. Thanks again, Peter