Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id A7A34632349 for ; Thu, 15 Apr 2010 18:22:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 57747-09 for ; Thu, 15 Apr 2010 21:22:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com [74.125.82.174]) by mail.postgresql.org (Postfix) with ESMTP id 550CE63219C for ; Thu, 15 Apr 2010 18:22:12 -0300 (ADT) Received: by wyb39 with SMTP id 39so780290wyb.19 for ; Thu, 15 Apr 2010 14:22:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:content-type; bh=06nlmztskv0Pk3+dW+TE1P+mrKJwxD/uvPzPjaBTb4g=; b=WCKdUZ9FZCYrgYA8kgVIjfSTrYTPkqVNk1ZLrYxqnUVXiiIfQodHQvptsuHRT0YATC tux0ji3Spu2HmaHdiMVl9QBsex5SMI8n7jElkvUUZMbufQvxu/Q0lnryrmsIY8JSphJp tgwoeOUEkN/JfP4Bg+7wrMu2JXPat/h55BOnI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=TfEdjJX35I2MMpc3dlC4IZYiy2PQAyhpZiHnN5Vnd6pkbED+WzEQM8sZenQWRgD3MH JUe3qXMcOTY74L6zZyMniEALlT/hL4tm5+rkyTGLIEx5KZCLJ5gLAoaAOnpM+eTclBWS UpiGQF6KeU298fUmAxl/UNX5ZVJ3Os24kT4+k= MIME-Version: 1.0 Received: by 10.216.168.135 with HTTP; Thu, 15 Apr 2010 14:22:10 -0700 (PDT) In-Reply-To: <20100415074542.137300@gmx.net> References: <201004150206.o3F26U427784@momjian.us> <20100415074542.137300@gmx.net> Date: Thu, 15 Apr 2010 18:22:10 -0300 Received: by 10.216.90.141 with SMTP id e13mr656717wef.166.1271366530844; Thu, 15 Apr 2010 14:22:10 -0700 (PDT) Message-ID: Subject: Re: PostgreSQL with SSL From: Jose Berardo To: =?ISO-8859-1?Q?Martin_M=FCnstermann?= , Bruce Momjian , pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0016e6db2d81734a2904844d16ee X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/200 X-Sequence-Number: 34294 --0016e6db2d81734a2904844d16ee Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 abou= t it. Sorry for asking you before read all the documentation, I have looked for I= t 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 starte= d to write a tutorial about SSL. Maybe my questions are the same of someone, so let me talk about them (than= k you Bruce for the answers): > > - There is any parameter to configure the path (and name) to certificat= e > > (server.crt) and private key (server.key) like hba_file or ident_file i= n > > 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 th= e RFC 1421. Martin, thank you too. The PEM format is exactly the base64 encoded format in this RFC. Thanks a lot, -- Jose Berardo On Thu, Apr 15, 2010 at 4:45 AM, "Martin M=FCnstermann" wro= te: > 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 star= t > > 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----- > > -----END CERTIFICATE----- > > and > > -----BEGIN RSA PRIVATE KEY----- > > -----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=FCr alle GMX-Mitglieder: Die maxdome Movie-FLAT! > Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 > --=20 Atenciosamente, Jose Berardo Especializa Treinamentos www.especializa.com.br +55 81 3465.0032 --0016e6db2d81734a2904844d16ee Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hello.

As I have told, I'm new on using PostgreSQL over SSL conn= ections.
I have worked with SSL before, but I'm really far from bein= g an expert about it.

Sorry for asking you before read all the docum= entation, 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 t= hose fixed file names.

Sorry, the documentation told me this just af= ter my precipitated question.

> > - Is it possible to store th= e server.key in a ciphered=A0 file with triple-des
> > and configure the PostgreSQL to use a simetric-key to open it whe= n 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 st= ill sufficient to protected the key?
I believe that may be interesting the server, at start time, when reading t= he 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 k= eytool in place of openssl.
> > - I believe that it not possible t= o start the PostgreSQL server without
> > openssl (and ssl-dev pac= kage 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 pai= r and the certificate, but it can not export the private key from its keyst= ore. You need another tool or to write a Java code to do that.

> > - When I create keys and certificates with keytool, it create= s a java
> > keystore to store everything. I know how to export th= e certificate but I
> > don't know how to export the private k= ey and when I use the keytool
> > certificate, the server crashes with this message:
> >> >=A0 FATAL:=A0 could not load server certificate file "serve= r.crt": no start line
> >
> > Sorry about too many q= uestions, but anyone can help me to understand more
> > about ssl in PostgreSQL?
>
> Have you read the docume= ntation about creating a server key?
>
>=A0=A0=A0=A0=A0=A0=A0 <= a href=3D"http://www.postgresql.org/docs/8.4/static/ssl-tcp.html">http://ww= w.postgresql.org/docs/8.4/static/ssl-tcp.html

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

Please friends, let me write an example here:

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

With the -rfc, the keytool print= s 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.

Tha= nks a lot,

--
Jose Berardo


= On Thu, Apr 15, 2010 at 4:45 AM, "Martin M=FCnstermann" <mmuenst@gmx.de> wrote:
Hello.

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

It is important that the server.crt and server.key files are in so-ca= lled 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,
=A0Martin

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



--
Atenciosamente,<= br>
Jose Berardo
Especializa Treinamentos
www.especializa.com.br
+55 81 3465.0032
--0016e6db2d81734a2904844d16ee--