pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedssl database connection problems...
22+ messages / 5 participants
[nested] [flat]
* ssl database connection problems...
@ 2008-12-29 21:23 Carol Walter <walterc@indiana.edu>
0 siblings, 3 replies; 22+ messages in thread
From: Carol Walter @ 2008-12-29 21:23 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-29 21:48 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Carol Walter <walterc@indiana.edu>
2 siblings, 0 replies; 22+ messages in thread
From: Kevin Grittner @ 2008-12-29 21:48 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; pgsql-admin
>>> 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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-29 21:50 Scott Marlowe <scott.marlowe@gmail.com>
parent: Carol Walter <walterc@indiana.edu>
2 siblings, 1 reply; 22+ messages in thread
From: Scott Marlowe @ 2008-12-29 21:50 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
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?
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-30 02:36 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
2 siblings, 2 replies; 22+ messages in thread
From: Ray Stell @ 2008-12-30 02:36 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-30 03:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Tom Lane @ 2008-12-30 03:13 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Carol Walter <walterc@indiana.edu>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-30 20:53 Carol Walter <walterc@indiana.edu>
parent: Ray Stell <stellr@cns.vt.edu>
1 sibling, 1 reply; 22+ messages in thread
From: Carol Walter @ 2008-12-30 20:53 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-31 01:42 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Ray Stell @ 2008-12-31 01:42 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: 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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-31 14:19 Carol Walter <walterc@indiana.edu>
parent: Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Carol Walter @ 2008-12-31 14:19 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2008-12-31 15:09 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 0 replies; 22+ messages in thread
From: Ray Stell @ 2008-12-31 15:09 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
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&a...
^ permalink raw reply [nested|flat] 22+ messages in thread
* Fwd: ssl database connection problems...
@ 2008-12-31 16:16 Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Carol Walter @ 2008-12-31 16:16 UTC (permalink / raw)
To: pgsql-admin
Begin forwarded message:
> From: Carol Walter <walterc@indiana.edu>
> Date: December 31, 2008 11:16:01 AM GMT-05:00
> To: Ray Stell <stellr@cns.vt.edu>
> Subject: Re: [ADMIN] ssl database connection problems...
>
> Sorry, I obviously am pretty clueless.
>
> Thanks,
> Carol
>
> On Dec 31, 2008, at 10:09 AM, Ray Stell wrote:
>
>> 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/
>>
> Here's the output from the s_client command...
> walterc@iris:~$ openssl s_client -connect db.slis.indiana.edu:5433
> CONNECTED(00000005)
> 9726:error:140790E5:SSL routines:SSL23_WRITE:ssl handshake
> failure:../../../../common/openssl/ssl/s23_lib.c:226:
>
> On the web site you directed me to, the s_server command uses a file
> called 'mycert.pem'. Do you know what the system expecting for this
> file? I tried running it without having 'mycert.pem' created and
> got errors saying that it couldn't open the file, of course.
> Anyway, here's the output I got from that command...
>
> bash-3.00# openssl s_server -accept 443 -cert mycert.pem -WWW
> Using default temp DH parameters
> unable to get certificate from 'mycert.pem'
> 7408: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('mycert.pem','r')
> 7408:error:2006D080:BIO routines:BIO_new_file:no such file:/on10/
> build-nd/G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:107:
> 7408: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('mycert.pem','r')
> 7408:error:20074002:BIO routines:FILE_CTRL:system lib:/on10/build-nd/
> G10U2B2/usr/src/common/openssl/crypto/bio/bss_file.c:278:
> 7408:error:140AD002:SSL routines:SSL_CTX_use_certificate_file:system
> lib:../../../../common/openssl/ssl/ssl_rsa.c:515:
>
>>
>>> 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&a...
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Fwd: ssl database connection problems...
@ 2008-12-31 21:08 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Ray Stell @ 2008-12-31 21:08 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On Wed, Dec 31, 2008 at 11:16:42AM -0500, Carol Walter wrote:
>> On the web site you directed me to, the s_server command uses a file
>> called 'mycert.pem'. Do you know what the system expecting for this file?
It's just a file and you tell openssl where it is, where you put it
once it is created. You request this file from your CA (certificate
authority) who will provide you with the root.crt, the public side of
the chain. You can generate your own with openssl. Here's a great
demo: http://www.madboa.com/geek/openssl/#cert
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-09 20:47 Carol Walter <walterc@indiana.edu>
parent: Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Carol Walter @ 2009-01-09 20:47 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
Well, back from the flu. =P
How do I tell postgres where to look for ssl. I've got both the new
and the original versions of openssl. I don't want to remove the
original one because it's loaded on the global zone and I don't know
what other dependencies it might have. Do I have to back everything
out and run ./configure again? I don't see a parm in postgresql.conf.
Thanks,
Carol
On Dec 31, 2008, at 4:08 PM, Ray Stell wrote:
> On Wed, Dec 31, 2008 at 11:16:42AM -0500, Carol Walter wrote:
>>> On the web site you directed me to, the s_server command uses a file
>>> called 'mycert.pem'. Do you know what the system expecting for
>>> this file?
>
> It's just a file and you tell openssl where it is, where you put it
> once it is created. You request this file from your CA (certificate
> authority) who will provide you with the root.crt, the public side of
> the chain. You can generate your own with openssl. Here's a great
> demo: http://www.madboa.com/geek/openssl/#cert
>
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-09 21:08 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Ray Stell @ 2009-01-09 21:08 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
./configure --prefix=/usr/local/pgsql826 --with-libraries=/usr/local/openssl/lib --with-includes=/usr/local/openssl/include/ --with-openssl
On Fri, Jan 09, 2009 at 03:47:54PM -0500, Carol Walter wrote:
> Well, back from the flu. =P
>
> How do I tell postgres where to look for ssl. I've got both the new and
> the original versions of openssl. I don't want to remove the original one
> because it's loaded on the global zone and I don't know what other
> dependencies it might have. Do I have to back everything out and run
> ./configure again? I don't see a parm in postgresql.conf.
>
> Thanks,
> Carol
>
> On Dec 31, 2008, at 4:08 PM, Ray Stell wrote:
>
>> On Wed, Dec 31, 2008 at 11:16:42AM -0500, Carol Walter wrote:
>>>> On the web site you directed me to, the s_server command uses a file
>>>> called 'mycert.pem'. Do you know what the system expecting for this
>>>> file?
>>
>> It's just a file and you tell openssl where it is, where you put it
>> once it is created. You request this file from your CA (certificate
>> authority) who will provide you with the root.crt, the public side of
>> the chain. You can generate your own with openssl. Here's a great
>> demo: http://www.madboa.com/geek/openssl/#cert
>>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-09 21:11 Ray Stell <stellr@cns.vt.edu>
parent: Ray Stell <stellr@cns.vt.edu>
0 siblings, 0 replies; 22+ messages in thread
From: Ray Stell @ 2009-01-09 21:11 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On Fri, Jan 09, 2009 at 04:08:00PM -0500, Ray Stell wrote:
> ./configure --prefix=/usr/local/pgsql826 --with-libraries=/usr/local/openssl/lib --with-includes=/usr/local/openssl/include/ --with-openssl
>
>
> On Fri, Jan 09, 2009 at 03:47:54PM -0500, Carol Walter wrote:
> > Well, back from the flu. =P
sorry for the unsupported top post, I forgot where I was.
sorry about the flu, I did that the week of Dec 16. I think
I lost some weight...
> > How do I tell postgres where to look for ssl. I've got both the new and
> > the original versions of openssl. I don't want to remove the original one
./configure --prefix=/usr/local/pgsql826 --with-libraries=/usr/local/openssl/lib --with-includes=/usr/local/openssl/include/ --with-openssl
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-21 17:50 Carol Walter <walterc@indiana.edu>
parent: Ray Stell <stellr@cns.vt.edu>
1 sibling, 1 reply; 22+ messages in thread
From: Carol Walter @ 2009-01-21 17:50 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-21 21:24 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Ray Stell @ 2009-01-21 21:24 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-21 22:01 Carol Walter <walterc@indiana.edu>
parent: Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Carol Walter @ 2009-01-21 22:01 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-21 22:59 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 0 replies; 22+ messages in thread
From: Ray Stell @ 2009-01-21 22:59 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: 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.
^ permalink raw reply [nested|flat] 22+ messages in thread
* ssl database connection problems...
@ 2009-01-22 15:35 Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Carol Walter @ 2009-01-22 15:35 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
I'm still having problems with ssl. My ssl_ciphers line in
postgresql.conf looks as the following:
ssl_ciphers 'ALL:!ADH:!LOW:@STRENGTH'
When I try to connect to the database from another system I get this
error...
walterc@cat:~$ psql -p 5433 -U walterc -d walterc -h db
psql: SSL SYSCALL error: EOF detected
When I try to connect to the database from another host, I get this
error...
walterc@cat:~$ psql -p 5433 -U walterc -d walterc -h db
psql: SSL SYSCALL error: EOF detected
The entries in the data834.log file are as follows;
[[unknown]:[unknown]:2009-01-22 10:12:02 EST]LOG: could not accept
SSL connection: cipher or hash unavailable
[::2009-01-22 10:19:48 EST]LOG: received smart shutdown request
[::2009-01-22 10:19:48 EST]LOG: autovacuum launcher shutting down
[::2009-01-22 10:19:48 EST]LOG: shutting down
[::2009-01-22 10:19:48 EST]LOG: database system is shut down
[::2009-01-22 10:20:02 EST]LOG: could not load root certificate file
"root.crt": No such file or directory
[::2009-01-22 10:20:02 EST]DETAIL: Will not verify client certificates.
[::2009-01-22 10:20:02 EST]LOG: database system was shut down at
2009-01-22 10:19:48 EST
[::2009-01-22 10:20:02 EST]LOG: database system is ready to accept
connections
[::2009-01-22 10:20:02 EST]LOG: autovacuum launcher started
[[unknown]:[unknown]:2009-01-22 10:24:00 EST]LOG: connection
received: host=129.79.36.77 port=64671
[walterc:walterc:2009-01-22 10:24:00 EST]LOG: could not receive data
from client: Connection reset by peer
[[unknown]:[unknown]:2009-01-22 10:24:11 EST]LOG: connection
received: host=129.79.36.77 port=64673
[walterc:walterc:2009-01-22 10:24:11 EST]FATAL: password
authentication failed for user "walterc"
[[unknown]:[unknown]:2009-01-22 10:24:11 EST]LOG: connection
received: host=129.79.36.77 port=64674
[walterc:walterc:2009-01-22 10:24:11 EST]FATAL: password
authentication failed for user "walterc"
[[unknown]:[unknown]:2009-01-22 10:24:20 EST]LOG: connection
received: host=129.79.36.77 port=64675
[walterc:walterc:2009-01-22 10:24:20 EST]LOG: could not receive data
from client: Connection reset by peer
[[unknown]:[unknown]:2009-01-22 10:24:29 EST]LOG: connection
received: host=129.79.36.77 port=64676
[walterc:walterc:2009-01-22 10:24:29 EST]FATAL: password
authentication failed for user "walterc"
[[unknown]:[unknown]:2009-01-22 10:24:29 EST]LOG: connection
received: host=129.79.36.77 port=64677
[walterc:walterc:2009-01-22 10:24:29 EST]FATAL: password
authentication failed for user "walterc"
[[unknown]:[unknown]:2009-01-22 10:24:33 EST]LOG: connection
received: host=129.79.36.77 port=64679
[walterc:walterc:2009-01-22 10:24:33 EST]LOG: could not receive data
from client: Connection reset by peer
[[unknown]:[unknown]:2009-01-22 10:24:48 EST]LOG: connection
received: host=129.79.36.77 port=64680
[walterc:walterc:2009-01-22 10:24:48 EST]FATAL: password
authentication failed for user "walterc"
[[unknown]:[unknown]:2009-01-22 10:24:48 EST]LOG: connection
received: host=129.79.36.77 port=64681
[walterc:walterc:2009-01-22 10:24:48 EST]FATAL: password
authentication failed for user "walterc"
-bash-3.00$
Do you have any ideas for me to try to solve this problem?
Thanks,
Carol
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-22 18:27 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Ray Stell @ 2009-01-22 18:27 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On Thu, Jan 22, 2009 at 10:35:22AM -0500, Carol Walter wrote:
> I'm still having problems with ssl. My ssl_ciphers line in postgresql.conf
> looks as the following:
>
> ssl_ciphers 'ALL:!ADH:!LOW:@STRENGTH'
this parameter was not available in 8.2.x when I tested so what
I say here has little basis.
1. no equal sign?
2. isn't this a list of values to choose from so should it be:
ssl_ciphers='ALL' ???
3. the doc does not say what happens if the the guy is commented
out: http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html#GUC-SSL-CIPHERS
I wonder what the default is?
4. the doc: http://www.postgresql.org/docs/8.3/interactive/ssl-tcp.html
says: "a list of ciphers can be specified" which makes it sound
optional, but again, I'm without clue.
> Do you have any ideas for me to try to solve this problem?
Well, I think when I did it with 8.2 it was by trial and error in
a test environment. I just kept changing stuff and kept a
matrix of where I had been. There is a finite number of things
to change. It might be good to make a list of possible
variable/values. I've not done that, but it might be good
for us to try to construct a howto.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-23 19:04 Carol Walter <walterc@indiana.edu>
parent: Ray Stell <stellr@cns.vt.edu>
0 siblings, 1 reply; 22+ messages in thread
From: Carol Walter @ 2009-01-23 19:04 UTC (permalink / raw)
To: Ray Stell <stellr@cns.vt.edu>; +Cc: pgsql-admin
On Jan 22, 2009, at 1:27 PM, Ray Stell wrote:
> On Thu, Jan 22, 2009 at 10:35:22AM -0500, Carol Walter wrote:
>> I'm still having problems with ssl. My ssl_ciphers line in
>> postgresql.conf
>> looks as the following:
>>
>> ssl_ciphers 'ALL:!ADH:!LOW:@STRENGTH'
>
> this parameter was not available in 8.2.x when I tested so what
> I say here has little basis.
>
> 1. no equal sign?
Yes, it does need an equal sign. That was a type-o that I just didn't
see. Fixed and re-ran. Still doesn't work.
>
> 2. isn't this a list of values to choose from so should it be:
> ssl_ciphers='ALL' ???
Yes, This says "All but ADH and low." I changed this line to just be
ssl_ciphers = 'ALL' . Stopped, started, and re-ran and it still
doesn't connect. The messages in the log file say "cipher or hash
unavailable". Since the files of the ciphers are definitely on the
system, this suggests that either postgres doesn't know where to find
them or the permission on them are wrong.
>
> 3. the doc does not say what happens if the the guy is commented
> out: http://www.postgresql.org/docs/8.3/interactive/runtime-config-connection.html#GUC-SSL-CIPHERS
> I wonder what the default is?
The default is
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL
ciphers
>
> 4. the doc: http://www.postgresql.org/docs/8.3/interactive/ssl-
> tcp.html
> says: "a list of ciphers can be specified" which makes it sound
> optional, but again, I'm without clue.
>
>
It needs a cipher or a hash. I don't know what it might use as a
hash. I found the cipher files. Unfortunately, I have two sets
because I have two versions of OpenSSL running. This might be part
of my problem, but I don't want to take a chance on messing up what's
already running. I don't know how to tell postgres which set of
cipher files to use. It's in the OpenSSL path, but not the complete
path.
>> Do you have any ideas for me to try to solve this problem?
>
>
Thanks,
Carol
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: ssl database connection problems...
@ 2009-01-23 20:02 Ray Stell <stellr@cns.vt.edu>
parent: Carol Walter <walterc@indiana.edu>
0 siblings, 0 replies; 22+ messages in thread
From: Ray Stell @ 2009-01-23 20:02 UTC (permalink / raw)
To: Carol Walter <walterc@indiana.edu>; +Cc: pgsql-admin
On Fri, Jan 23, 2009 at 02:04:21PM -0500, Carol Walter wrote:
>>>
>>> ssl_ciphers 'ALL:!ADH:!LOW:@STRENGTH'
I don't understand this syntax, is it described somewhere to your
knowledge. The doc say to see the openssl docs, so I went
fishing there. Maybe one of these will work:
> openssl ciphers -v
DHE-RSA-AES256-SHA SSLv3 Kx=DH Au=RSA Enc=AES(256) Mac=SHA1
DHE-DSS-AES256-SHA SSLv3 Kx=DH Au=DSS Enc=AES(256) Mac=SHA1
AES256-SHA SSLv3 Kx=RSA Au=RSA Enc=AES(256) Mac=SHA1
EDH-RSA-DES-CBC3-SHA SSLv3 Kx=DH Au=RSA Enc=3DES(168) Mac=SHA1
...
EXP-RC4-MD5 SSLv3 Kx=RSA(512) Au=RSA Enc=RC4(40) Mac=MD5 export
EXP-RC4-MD5 SSLv2 Kx=RSA(512) Au=RSA Enc=RC4(40) Mac=MD5 export
> Yes, This says "All but ADH and low." I changed this line to just be
> ssl_ciphers = 'ALL' . Stopped, started, and re-ran and it still doesn't
> connect. The messages in the log file say "cipher or hash unavailable".
maybe that means the ALL I guessed is wrong, but idunno, the documentation
doesn't say what that string means.
> Since the files of the ciphers are definitely on the system, this suggests
> that either postgres doesn't know where to find them or the permission on
> them are wrong.
it should, seems like that would have been handled in your compile pointing to
the libs.
> The default is
> #ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL
I don't know what this means, these are not listed in the openssl docs
that is pointed to. Guess we could go read the pg source and figure
out what they do with this config line, maybe. We need a clue here...
> how to tell postgres which set of cipher files to use. It's in the OpenSSL
> path, but not the complete path.
I thinking that is covered in the compile and you are not using the config
line to pgs liking, but that's just a guess.
Sorry, I can't try this stuff myself, buried in Oracle cruft right now.
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2009-01-23 20:02 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-12-29 21:23 ssl database connection problems... Carol Walter <walterc@indiana.edu>
2008-12-29 21:48 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-12-29 21:50 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-12-30 03:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-12-30 02:36 ` Ray Stell <stellr@cns.vt.edu>
2008-12-30 20:53 ` Carol Walter <walterc@indiana.edu>
2008-12-31 01:42 ` Ray Stell <stellr@cns.vt.edu>
2008-12-31 14:19 ` Carol Walter <walterc@indiana.edu>
2008-12-31 15:09 ` Ray Stell <stellr@cns.vt.edu>
2009-01-21 17:50 ` Carol Walter <walterc@indiana.edu>
2009-01-21 21:24 ` Ray Stell <stellr@cns.vt.edu>
2009-01-21 22:01 ` Carol Walter <walterc@indiana.edu>
2009-01-21 22:59 ` Ray Stell <stellr@cns.vt.edu>
2008-12-31 16:16 Fwd: ssl database connection problems... Carol Walter <walterc@indiana.edu>
2008-12-31 21:08 ` Ray Stell <stellr@cns.vt.edu>
2009-01-09 20:47 ` Carol Walter <walterc@indiana.edu>
2009-01-09 21:08 ` Ray Stell <stellr@cns.vt.edu>
2009-01-09 21:11 ` Ray Stell <stellr@cns.vt.edu>
2009-01-22 15:35 ssl database connection problems... Carol Walter <walterc@indiana.edu>
2009-01-22 18:27 ` Ray Stell <stellr@cns.vt.edu>
2009-01-23 19:04 ` Carol Walter <walterc@indiana.edu>
2009-01-23 20:02 ` Ray Stell <stellr@cns.vt.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox