Обсуждение: ssl database connection problems...
Hello,
I've just created a new instance of postgres. It's running an a Sun
server running Solaris 10. I configured it with ssl using port 5433.
The server starts and runs. I can connect to it from the local host
and list the databases, connect to them etc. I can't connect to the
database instance from a remote host. I get a message as follows:
walterc@cat:~$ psql -h db -U walterc -d walterc -p 5433
psql: could not connect to server: Connection refused
Is the server running on host "db" and accepting
TCP/IP connections on port 5433?
I know the server is running, so according to the message, it must not
be accepting TCP/IP connections on port 5433. I configured pg_port =
5433 and "with openssl" when I initially configured the server. Are
there other things that need to be done to get openssl started on the
database server? How can I diagnose this problem?
Carol
>>> Carol Walter <walterc@indiana.edu> wrote: > The server starts and runs. I can connect to it from the local host > and list the databases, connect to them etc. I can't connect to the > database instance from a remote host. Have you set listen_addresses in postgresql.conf? You likely want: listen_addresses = '*' -Kevin
On Mon, Dec 29, 2008 at 2:23 PM, Carol Walter <walterc@indiana.edu> wrote: > Hello, > > I've just created a new instance of postgres. It's running an a Sun server > running Solaris 10. I configured it with ssl using port 5433. The server > starts and runs. I can connect to it from the local host and list the > databases, connect to them etc. I can't connect to the database instance > from a remote host. I get a message as follows: Are you connecting via unix sockets or tcp/ip sockets locally? > walterc@cat:~$ psql -h db -U walterc -d walterc -p 5433 > psql: could not connect to server: Connection refused > Is the server running on host "db" and accepting > TCP/IP connections on port 5433? What does listen_addresses say in postgresql.conf for this instance?
On Mon, Dec 29, 2008 at 04:23:30PM -0500, Carol Walter wrote: > "with openssl" when I initially configured the server. Are there other > things that need to be done to get openssl started on the database server? > How can I diagnose this problem? > The files server.key, server.crt, root.crt, and root.crl are only examined during server start; so you must restart the server for changes in them to take effect. http://www.postgresql.org/docs/8.3/interactive/ssl-tcp.html It's been awhile since I played with this, but there's something about an environment var, PGSSLMODE. You can use openssl to verify the server/root ca correctness like this: openssl verify -CAfile ./root.crt testcert.pem assuming openssl in the mix.
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Mon, Dec 29, 2008 at 2:23 PM, Carol Walter <walterc@indiana.edu> wrote:
>> ... I can't connect to the database instance
>> from a remote host. I get a message as follows:
>> walterc@cat:~$ psql -h db -U walterc -d walterc -p 5433
>> psql: could not connect to server: Connection refused
>> Is the server running on host "db" and accepting
>> TCP/IP connections on port 5433?
> What does listen_addresses say in postgresql.conf for this instance?
If listen_addresses is set properly, another thing to check is whether
there is a firewall blocking connections to 5433 at the kernel or
network level.
regards, tom lane
Thanks to all of you. Many of my problems have been fixed. My "listening_addresses" was not set correctly. After I fixed that problem, I started getting an SSL error. I'm now getting this error as follows: walterc@fac-staff:~$ psql -U walterc -d walterc -h db -p 5433 psql: SSL SYSCALL error: EOF detected I've poked around a lot in my system. OpenSSL is telling me that ssl is not properly configured. I don't know if the error is accurate of it's describing differences between its configuration and Postgres'. Since ssl on my database box has never been used, there's a very good chance it's not configured properly. I've decided the best tact would be to get a new version of OpenSSL. The most current version on the Sun Freeware site, is 0.9.8i. Are there any issues with compatibility that I should know about. I'm running Solaris 10 and version 8.3.4 of postgres. Thanks, Carol On Dec 29, 2008, at 9:36 PM, Ray Stell wrote: > On Mon, Dec 29, 2008 at 04:23:30PM -0500, Carol Walter wrote: >> "with openssl" when I initially configured the server. Are there >> other >> things that need to be done to get openssl started on the database >> server? >> How can I diagnose this problem? >> > > The files server.key, server.crt, root.crt, and root.crl are only > examined > during server start; so you must restart the server for changes in > them > to take effect. > > http://www.postgresql.org/docs/8.3/interactive/ssl-tcp.html > > It's been awhile since I played with this, but there's something > about an > environment var, PGSSLMODE. > > You can use openssl to verify the server/root ca correctness like > this: > > openssl verify -CAfile ./root.crt testcert.pem > > assuming openssl in the mix. > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
On Tue, Dec 30, 2008 at 03:53:37PM -0500, Carol Walter wrote: > > OpenSSL is telling me that ssl is > not properly configured. how so? >> openssl verify -CAfile ./root.crt testcert.pem can you verify the server crt against the CA? That is the starting place.
On Dec 30, 2008, at 8:42 PM, Ray Stell wrote:
> On Tue, Dec 30, 2008 at 03:53:37PM -0500, Carol Walter wrote:
>>
>> OpenSSL is telling me that ssl is
>> not properly configured.
>
> how so?
>
Here's the output from s_client & s_server commands...
# openssl s_client
connect: Connection refused
connect:errno=146
# openssl s_server
Using default temp DH parameters
unable to get certificate from 'server.pem'
23374:error:02001002:system library:fopen:No such file or directory:/
on10/build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:
104:fopen('server.pem','r')
23374:error:2006D080:BIO routines:BIO_new_file:no such file:/on10/
build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:107:
23374:error:02001002:system library:fopen:No such file or directory:/
on10/build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:
276:fopen('server.pem','r')
23374:error:20074002:BIO routines:FILE_CTRL:system lib:/on10/build-nd/
G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:278:
23374:error:140AD002:SSL routines:SSL_CTX_use_certificate_file:system
lib:../../../../common/openssl/ssl/ssl_rsa.c:515:
>>> openssl verify -CAfile ./root.crt testcert.pem
>
I don't have a root.crt file. According to the postgres 8.3.5
documentation, the postgres should run without it. I'm not sure what
root.crt should contain at this point, and how it should be formatted.
"If the root.crt file is not present, client certificates will not
be requested or checked. In this mode, SSL provides encrypted
communication but not authentication."
# openssl verify -CAfile ./root.crt testcert.pem
Error loading file ./root.crt
27073:error:02001002:system library:fopen:No such file or directory:/
on10/build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:
104:fopen('./root.crt','r')
27073:error:2006D080:BIO routines:BIO_new_file:no such file:/on10/
build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:107:
27073:error:0B084002:x509 certificate
routines:X509_load_cert_crl_file:system lib:/on10/build-nd/G10U2B2/usr/
src/common/openssl/crypto/x509/by_file.c:274:
usage: verify [-verbose] [-CApath path] [-CAfile file] [-purpose
purpose] [-crl_check] [-engine e] cert1 cert2 ...
recognized usages:
sslclient SSL client
sslserver SSL server
nssslserver Netscape SSL server
smimesign S/MIME signing
smimeencrypt S/MIME encryption
crlsign CRL signing
any Any Purpose
ocsphelper OCSP helper
> can you verify the server crt against the CA?
>
> That is the starting place.
Here's the output I got from the command openssl ca...
# openssl ca
Using configuration from /etc/sfw/openssl/openssl.cnf
Error opening CA private key /etc/sfw/openssl/private/cakey.pem
28124:error:0E06D06C:configuration file routines:NCONF_get_string:no
value:/on10/build-nd/G10U2B2/usr/src/common/openssl/crypto/conf/
conf_lib.c:329:group=CA_default name=unique_subject
28124:error:02001002:system library:fopen:No such file or directory:/
on10/build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:
276:fopen('/etc/sfw/openssl/private/cakey.pem','r')
28124:error:20074002:BIO routines:FILE_CTRL:system lib:/on10/build-nd/
G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:278:
unable to load CA private key
I have yet to find the command I ran yesterday that explicitly stated
that there was an error in configuration.
Best Regards,
Carol
On Wed, Dec 31, 2008 at 09:19:12AM -0500, Carol Walter wrote: > Here's the output from s_client & s_server commands... > > # openssl s_client > connect: Connection refused > connect:errno=146 oh, I think you need to use some more flags. Take a look at this howto: http://www.madboa.com/geek/openssl/ > I don't have a root.crt file. > > # openssl verify -CAfile ./root.crt testcert.pem right, my file root.ca was self generated using openssl (I'm the CA). It is analogous to the CA chain you might buy from Thawte or some other trusted authority. It is the file that I used to sign my server crt file, testcrt.pem. ` Yeah, you don't need it unless you want to auth a login with pg, but we are not there yet. You need to verify that openssl is not fubar first, right? Best in 2009, everyone: Carbon-free city under construction, cool! http://cosmos.bcst.yahoo.com/up/ynews;_ylt=AgPr9FSysEdu1cF5ydA9CPr737YB?ch=4226722&cl=11310260&lang=en
Well, I cleared out other database problems and now I'm back to this
one...
When I run the OpenSSL command below I get the following output...
-bash-3.00$ /usr/local/ssl/bin/openssl verify -CAfile ./root.crt
testcert.pem
Error loading file ./root.crt
24149:error:02001002:system library:fopen:No such file or
directory:bss_file.c:126:fopen('./root.crt','r')
24149:error:2006D080:BIO routines:BIO_new_file:no such file:bss_file.c:
129:
24149:error:0B084002:x509 certificate
routines:X509_load_cert_crl_file:system lib:by_file.c:274:
usage: verify [-verbose] [-CApath path] [-CAfile file] [-purpose
purpose] [-crl_check] [-engine e] cert1 cert2 ...
recognized usages:
sslclient SSL client
sslserver SSL server
nssslserver Netscape SSL server
smimesign S/MIME signing
smimeencrypt S/MIME encryption
crlsign CRL signing
any Any Purpose
ocsphelper OCSP helper
The associated lines in my postgres log are these...
[[unknown]:[unknown]:2009-01-16 16:46:32 EST]LOG: connection
received: host=129.79.36.241 port=33869
[[unknown]:[unknown]:2009-01-16 16:46:32 EST]LOG: could not accept
SSL connection: cipher or hash unavailable
[postgres:walterc:2009-01-16 16:50:35 EST]LOG: disconnection: session
time: 0:06:03.150 user=postgres database=walterc host=[local]
There is a line concerning ssl ciphers in the postgresql.conf file.
I'm wondering if that may be causing my problem. What should this be
set to?
Carol
On Dec 29, 2008, at 9:36 PM, Ray Stell wrote:
> On Mon, Dec 29, 2008 at 04:23:30PM -0500, Carol Walter wrote:
>> "with openssl" when I initially configured the server. Are there
>> other
>> things that need to be done to get openssl started on the database
>> server?
>> How can I diagnose this problem?
>>
>
> The files server.key, server.crt, root.crt, and root.crl are only
> examined
> during server start; so you must restart the server for changes in
> them
> to take effect.
>
> http://www.postgresql.org/docs/8.3/interactive/ssl-tcp.html
>
> It's been awhile since I played with this, but there's something
> about an
> environment var, PGSSLMODE.
>
> You can use openssl to verify the server/root ca correctness like
> this:
>
> openssl verify -CAfile ./root.crt testcert.pem
>
> assuming openssl in the mix.
On Wed, Jan 21, 2009 at 12:50:23PM -0500, Carol Walter wrote:
> -bash-3.00$ /usr/local/ssl/bin/openssl verify -CAfile ./root.crt
> testcert.pem
> Error loading file ./root.crt
> 24149:error:02001002:system library:fopen:No such file or
> directory:bss_file.c:126:fopen('./root.crt','r')
root.crt is just my self signed root authority cert. It is just a file
I created/named with openssl for testing. You place whatever file is
the public side of the chain (you got it from the signing authority)
somewhere and tell the command where to look with the -CAfile flag.
The file testcert.pem was signed by that auth and so is paired with
root.crt.
On Jan 21, 2009, at 4:24 PM, Ray Stell wrote:
> On Wed, Jan 21, 2009 at 12:50:23PM -0500, Carol Walter wrote:
>> -bash-3.00$ /usr/local/ssl/bin/openssl verify -CAfile ./root.crt
>> testcert.pem
>> Error loading file ./root.crt
>> 24149:error:02001002:system library:fopen:No such file or
>> directory:bss_file.c:126:fopen('./root.crt','r')
>
>
> root.crt is just my self signed root authority cert. It is just a
> file
> I created/named with openssl for testing. You place whatever file is
> the public side of the chain (you got it from the signing authority)
> somewhere and tell the command where to look with the -CAfile flag.
> The file testcert.pem was signed by that auth and so is paired with
> root.crt.
>
I do understand that. I just wasn't sure that was causing all my
errors. Also, in the second part of my message there are lines
relating to the encryption. I'm not sure what needs to be in my
postgresql.conf file to handle this. I'm using the MD5 method.
Carol
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
On Wed, Jan 21, 2009 at 05:01:08PM -0500, Carol Walter wrote: > > On Jan 21, 2009, at 4:24 PM, Ray Stell wrote: > >> On Wed, Jan 21, 2009 at 12:50:23PM -0500, Carol Walter wrote: > Also, in the second part of my message there are lines relating to the > encryption. I'm not sure what needs to be in my postgresql.conf file to > handle this. I'm using the MD5 method. sorry, I didn't read that far and I didn't keep the post, so if I put something stupid here, well, there it is. The last time I played with this was 8.2.something, so 8.3.x may not worky the same way: I used the following in pg_hba.conf: hostssl all all CIDR-address md5 with my specific notation for the CIDR. http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html looks like you can use host and it will allow either ssl or not, so chose hostssl if you want to keep the clear text guys out.