Обсуждение: ssl database connection problems...

Поиск
Список
Период
Сортировка

ssl database connection problems...

От
Carol Walter
Дата:
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

Re: ssl database connection problems...

От
"Kevin Grittner"
Дата:
>>> 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

Re: ssl database connection problems...

От
"Scott Marlowe"
Дата:
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?

Re: ssl database connection problems...

От
Ray Stell
Дата:
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.

Re: ssl database connection problems...

От
Tom Lane
Дата:
"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

Re: ssl database connection problems...

От
Carol Walter
Дата:
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


Re: ssl database connection problems...

От
Ray Stell
Дата:
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.

Re: ssl database connection problems...

От
Carol Walter
Дата:
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

Re: ssl database connection problems...

От
Ray Stell
Дата:
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

Re: ssl database connection problems...

От
Carol Walter
Дата:
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.


Re: ssl database connection problems...

От
Ray Stell
Дата:
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.

Re: ssl database connection problems...

От
Carol Walter
Дата:
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


Re: ssl database connection problems...

От
Ray Stell
Дата:
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.