Обсуждение: PostgreSQL with SSL

--
Atenciosamente,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
81 3465.0032

Jose Berardo wrote:
> Hi,
>
> I'm new on using SSL in PostgreSQL.
>
> I've created a self-signed certificate using openssl and started the server
> with ssl suport.
>
> But I have some question, can you help me?
>
> - There is any parameter to configure the path (and name) to certificate
> (server.crt) and private key (server.key) like hba_file or ident_file in
> postgresql.conf or any other?

No, we only allow those fixed file names.

> - Is it possible to store the server.key in a ciphered  file with triple-des
> and configure the PostgreSQL to use a simetric-key to open it when it's
> necessary?
> Maybe I'm wrong but my server only works with I plain private key.

No.

> I'm trying to use the java keytool in place of openssl.
> - I believe that it not possible to start the PostgreSQL server without
> openssl (and ssl-dev package in debian), is it correct?

Yes, I don't think the java keytool works.

> - When I create keys and certificates with keytool, it creates a java
> keystore to store everything. I know how to export the certificate but I
> don't know how to export the private key and when I use the keytool
> certificate, the server crashes with this message:
>
>  FATAL:  could not load server certificate file "server.crt": no start line
>
> Sorry about too many questions, but anyone can help me to understand more
> about ssl in PostgreSQL?

Have you read the documentation about creating a server key?

    http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

Hello.

> > don't know how to export the private key and when I use the keytool
> > certificate, the server crashes with this message:
> >
> >  FATAL:  could not load server certificate file "server.crt": no start
> line
> >
> > Sorry about too many questions, but anyone can help me to understand
> more
> > about ssl in PostgreSQL?
>
> Have you read the documentation about creating a server key?
>
>     http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

It is important that the server.crt and server.key files are in so-called PEM format.
So they have to be text files and look like:

-----BEGIN CERTIFICATE-----
<some base64 encoded data>
-----END CERTIFICATE-----

and

-----BEGIN RSA PRIVATE KEY-----
<some base64 encoded data>
-----END RSA PRIVATE KEY-----

This is the default format produced by openssl.
The private key format is also openssl-specific, so probably you are out of luck with keytool.

Regards,
 Martin

--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

Hello.

As I have told, I'm new on using PostgreSQL over SSL connections.
I have worked with SSL before, but I'm really far from being an expert about it.

Sorry for asking you before read all the documentation, I have looked for It in wrong pages and didn't find anything. After writing, I read a lot about this subject because I'm a teacher, PostgreSQL enthusiastic and I've started to write a tutorial about SSL.

Maybe my questions are the same of someone, so let me talk about them (thank you Bruce for the answers):

> > - There is any parameter to configure the path (and name) to certificate
> > (server.crt) and private key (server.key) like hba_file or ident_file in
> > postgresql.conf or any other?
>
> No, we only allow those fixed file names.

Sorry, the documentation told me this just after my precipitated question.

> > - Is it possible to store the server.key in a ciphered  file with triple-des
> > and configure the PostgreSQL to use a simetric-key to open it when it's
> > necessary?
> > Maybe I'm wrong but my server only works with I plain private key.
>
> No.

I believe that it may be a good idea, it may bring another security level, I'm wrong?
Just saving the private key file inside the cluster with no privilegies for other users (the server suggests 0600 mask for it) is still sufficient to protected the key?
I believe that may be interesting the server, at start time, when reading the key, asks the administrator the password (e.g. a triple-des simetric key) the same way that OpenSSL asks when we need to generate de public key or generate crt or csr.
What do you think, Bruce?

> > I'm trying to use the java keytool in place of openssl.
> > - I believe that it not possible to start the PostgreSQL server without
> > openssl (and ssl-dev package in debian), is it correct?
>
> Yes, I don't think the java keytool works.

Oh, the documentation defeated me twice. The server reads the openssl configuration at start time too.
The keytool may be used only to generate the key pair and the certificate, but it can not export the private key from its keystore. You need another tool or to write a Java code to do that.

> > - When I create keys and certificates with keytool, it creates a java
> > keystore to store everything. I know how to export the certificate but I
> > don't know how to export the private key and when I use the keytool
> > certificate, the server crashes with this message:
> >
> >  FATAL:  could not load server certificate file "server.crt": no start line
> >
> > Sorry about too many questions, but anyone can help me to understand more
> > about ssl in PostgreSQL?
>
> Have you read the documentation about creating a server key?
>
>        http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

Thank you Bruce, but this question was about the format of the certificate generated by keytool.
I was missing the -rfc option when exporting the certificate.

Please friends, let me write an example here:

keytool -exportcert -alias myalias -file mycertificate.crt -keystore mykeystore.jks -storepass mypass -rfc

With the -rfc, the keytool prints out the certificate in accordance with the RFC 1421.

Martin, thank you too.
The PEM format is exactly the base64 encoded format in this RFC.

Thanks a lot,

--
Jose Berardo





--
Atenciosamente,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032

Jose Berardo <joseberardo@gmail.com> writes:
>>> - Is it possible to store the server.key in a ciphered  file with

>> No.

> I believe that it may be a good idea, it may bring another security level,

Not really.

> Just saving the private key file inside the cluster with no privilegies for
> other users (the server suggests 0600 mask for it) is still sufficient to
> protected the key?

If someone can access that file, they can also attach to the running
server process and pull the decrypted key out of it.  In any case,
providing the server with the key to decrypt the ssl key is not going
to be convenient in operation.  You're not going to want to store that
key on disk are you?  Do you want somebody around to manually provide
it every time the server restarts?  That gets old pretty fast, when
all it's buying you is a largely-imaginary security gain.

            regards, tom lane

Hello.

> > > I'm trying to use the java keytool in place of openssl.
> > > - I believe that it not possible to start the PostgreSQL server
> without
> > > openssl (and ssl-dev package in debian), is it correct?
> >
> > Yes, I don't think the java keytool works.
>
> Oh, the documentation defeated me twice. The server reads the openssl
> configuration at start time too.
> The keytool may be used only to generate the key pair and the certificate,
> but it can not export the private key from its keystore. You need another
> tool or to write a Java code to do that.

OpenSSL has two ways to store private keys:
1. an own proprietary format
2. standard PKCS#8

The default as used in the postgresql doc is to produce the proprietary format.
Don't know if PostgreSQL can handle PKCS#8 keys.

If you'd like to check, here is a command to produce PKCS#8:
openssl pkcs8 -in server.key  -out server.p8 -topk8

Jose, writing a tutorial sounds promising. If I can be of any help, just contact me.

Martin

--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

Hello,



On Thu, Apr 15, 2010 at 6:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jose Berardo <joseberardo@gmail.com> writes:
>>>> - Is it possible to store the server.key in a ciphered  file with
>
>>> No.
>
>> I believe that it may be a good idea, it may bring another security level,
>
> Not really.
>
>> Just saving the private key file inside the cluster with no privilegies for
>> other users (the server suggests 0600 mask for it) is still sufficient to
>> protected the key?
>
> If someone can access that file, they can also attach to the running
> server process and pull the decrypted key out of it.  In any case,
> providing the server with the key to decrypt the ssl key is not going
> to be convenient in operation.  You're not going to want to store that
> key on disk are you?  Do you want somebody around to manually provide
> it every time the server restarts?  That gets old pretty fast, when
> all it's buying you is a largely-imaginary security gain.
>
>                        regards, tom lane
>

Thanks Tom. Your few words were a very elucidative explanation.
I thought that attacking the running server process was much more
difficult than just open a file, and the needs of someone to provide
the symmetric key which will open the private key was just a question
of trade-off (security vs availability).


--
Regards,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032

Hello,

On Fri, Apr 16, 2010 at 4:34 AM, "Martin Münstermann" <mmuenst@gmx.de> wrote:
> Hello.
>
>> > > I'm trying to use the java keytool in place of openssl.
>> > > - I believe that it not possible to start the PostgreSQL server
>> without
>> > > openssl (and ssl-dev package in debian), is it correct?
>> >
>> > Yes, I don't think the java keytool works.
>>
>> Oh, the documentation defeated me twice. The server reads the openssl
>> configuration at start time too.
>> The keytool may be used only to generate the key pair and the certificate,
>> but it can not export the private key from its keystore. You need another
>> tool or to write a Java code to do that.
>
> OpenSSL has two ways to store private keys:
> 1. an own proprietary format
> 2. standard PKCS#8
>
> The default as used in the postgresql doc is to produce the proprietary format.
> Don't know if PostgreSQL can handle PKCS#8 keys.
>
> If you'd like to check, here is a command to produce PKCS#8:
> openssl pkcs8 -in server.key  -out server.p8 -topk8

I've test your suggestion, but it didn't work. Results bellow:

$ Enter PEM pass phrase:
FATAL:  could not load private key file "server.key": problems getting password

As Tom Laine has explained, the service don't work with encrypted keys
because it would need to ask the administrator for the password (like
above), and would not start until he gives it. The server would need
to keep the clear key in memory for every connection. Anyway, would
still be possible one attacker obtain the key.

>
> Jose, writing a tutorial sounds promising. If I can be of any help, just contact me.

Thanks Martin, I'm writing in portuguese, but I will ask a friend for
help me in translation to english.
When it's done, I will send to you. Any consideration will be extreme welcome.

>
> Martin
>
> --
> GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
> Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01
>



--
Regards,

Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032