Thread: ssl database connection problems...

ssl database connection problems...

From
Carol Walter
Date:
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





Re: ssl database connection problems...

From
Ray Stell
Date:
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.

Re: ssl database connection problems...

From
Carol Walter
Date:
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


Re: ssl database connection problems...

From
Ray Stell
Date:
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.