Changed SSL Certificates

On Fri, Apr 8, 2011 at 1:42 PM, Carlos Mennens <carlos.mennens@gmail.com>wrote:

I had self signed SSL certificates on my database server but since
then removed them and received updated certificates from the security
team. I removed (backedup) the old server.crt & server.key and now
have db1_ssl.crt & db1_ssl.key in the identical location as the old
SSL certificates. I then went to /etc/postgres/8.4/main and removed
the old symbolic links for the old certificates and generated new
symbolic links:

ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key

I then restarted PostgreSQL and got the following error:

2011-04-08 09:54:34 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory
2011-04-08 10:00:43 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory

I looked for anywhere else in /var/lib/postgres/ & /etc/postgres/ but
can't find anything else that's calling the old certificates. I
changed the ownership on the certificates and symbolic links to either
root or postgres and nothing worked. It fails to start with the
following error:

root@db1:/# /etc/init.d/postgresql start
Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server
failed to start. Please check the log output: 2011-04-08 12:36:54 EDT
FATAL: could not load server certificate file "server.crt": No such
file or directory ... failed!

I checked the documentation page:

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

Table 30-4. Libpq/Client SSL File Usage

File Contents Effect
~/.postgresql/postgresql.crt client certificate requested by server
~/.postgresql/postgresql.key client private key proves client
certificate sent by owner; does not indicate certificate owner is
trustworthy
~/.postgresql/root.crt trusted certificate authorities checks server
certificate is signed by a trusted certificate authority
~/.postgresql/root.crl certificates revoked by certificate
authorities server certificate must not be on this list

Can anyone tell me what I'm doing wrong or missing here? I can't
disable SSL per DoD requirements sadly.

-Carlos

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

When linking to the certificate and key you should specify the full path.

ln -s /etc/ssl/certs/db1_ssl.crt /full/path/to/db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key /full/path/to/db1_ssl.key

HTH,

diego

On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz <dschulz@gmail.com> wrote:

Hi,
When linking to the certificate and key you should specify the full path.
ln -s /etc/ssl/certs/db1_ssl.crt      /full/path/to/db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key   /full/path/to/db1_ssl.key

Thanks for the quick reply Diego. I posted the commands above and I
used the full path to the certificates as you can see. Here's the
info:

lrwxrwxrwx 1 postgres postgres 26 Apr 8 10:43 db1_ssl.crt ->
/etc/ssl/certs/db1_ssl.crt
lrwxrwxrwx 1 postgres postgres 28 Apr 8 10:50 db1_ssl.key ->
/etc/ssl/private/db1_ssl.key

The 1st part is just the symbolic link referenced in
/var/lib/postgresql/8.4/main but you can see it knows to reference the
symbolic links to /etc/ssl/...

I'm thinking there's some random configuration file for PostgreSQL
that has pointers to the old server.crt and server.key files but I've
searched /etc/postgres/ and /var/lib/postgresql/8.4/main completely
and can't find it what so ever. I am not authorized to disable SSL per
DoD standards / requirements sadly.

Any thing else I am missing? I can't be the 1st person to switch SSL
certificates during utilization.

On Fri, Apr 8, 2011 at 2:21 PM, Carlos Mennens <carlos.mennens@gmail.com>wrote:

On Fri, Apr 8, 2011 at 1:15 PM, Diego Schulz <dschulz@gmail.com> wrote:

Hi,
When linking to the certificate and key you should specify the full path.
ln -s /etc/ssl/certs/db1_ssl.crt /full/path/to/db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key /full/path/to/db1_ssl.key

Thanks for the quick reply Diego. I posted the commands above and I
used the full path to the certificates as you can see. Here's the
info:

lrwxrwxrwx 1 postgres postgres 26 Apr 8 10:43 db1_ssl.crt ->
/etc/ssl/certs/db1_ssl.crt
lrwxrwxrwx 1 postgres postgres 28 Apr 8 10:50 db1_ssl.key ->
/etc/ssl/private/db1_ssl.key

The 1st part is just the symbolic link referenced in
/var/lib/postgresql/8.4/main but you can see it knows to reference the
symbolic links to /etc/ssl/...

I'm thinking there's some random configuration file for PostgreSQL
that has pointers to the old server.crt and server.key files but I've
searched /etc/postgres/ and /var/lib/postgresql/8.4/main completely
and can't find it what so ever. I am not authorized to disable SSL per
DoD standards / requirements sadly.

Any thing else I am missing? I can't be the 1st person to switch SSL
certificates during utilization.

Make sure the files have the right ownership and permissions.
It looks like ownership is correct (postgres:postgres) but permissions might
be too loose.
Try chmod 400 on your key and certificate and see what happens.

cheers,

diego

On 04/08/2011 09:42 AM, Carlos Mennens wrote:

I had self signed SSL certificates on my database server but since
then removed them and received updated certificates from the security
team. I removed (backedup) the old server.crt& server.key and now
have db1_ssl.crt& db1_ssl.key in the identical location as the old
SSL certificates. I then went to /etc/postgres/8.4/main and removed
the old symbolic links for the old certificates and generated new
symbolic links:

ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key

I then restarted PostgreSQL and got the following error:

2011-04-08 09:54:34 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory
2011-04-08 10:00:43 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory

I looked for anywhere else in /var/lib/postgres/& /etc/postgres/ but
can't find anything else that's calling the old certificates. I
changed the ownership on the certificates and symbolic links to either
root or postgres and nothing worked. It fails to start with the
following error:

root@db1:/# /etc/init.d/postgresql start
Starting PostgreSQL 8.4 database server: mainThe PostgreSQL server
failed to start. Please check the log output: 2011-04-08 12:36:54 EDT
FATAL: could not load server certificate file "server.crt": No such
file or directory ... failed!

I checked the documentation page:

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

Table 30-4. Libpq/Client SSL File Usage

File Contents Effect
~/.postgresql/postgresql.crt client certificate requested by server
~/.postgresql/postgresql.key client private key proves client
certificate sent by owner; does not indicate certificate owner is
trustworthy
~/.postgresql/root.crt trusted certificate authorities checks server
certificate is signed by a trusted certificate authority
~/.postgresql/root.crl certificates revoked by certificate
authorities server certificate must not be on this list

Can anyone tell me what I'm doing wrong or missing here? I can't
disable SSL per DoD requirements sadly.

Per here:
http://www.postgresql.org/docs/8.4/static/ssl-tcp.html
File Contents Effect
server.crt server certificate requested by client
server.key server private key proves server certificate sent by owner;
does not indicate certificate owner is trustworthy
root.crt trusted certificate authorities checks that client certificate
is signed by a trusted certificate authority
root.crl certificates revoked by certificate authorities client
certificate must not be on this list

Rename your certs to above.

-Carlos

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Apr 8, 2011 at 12:42 PM, Carlos Mennens <carlos.mennens@gmail.com>wrote:

ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key

I then restarted PostgreSQL and got the following error:

2011-04-08 09:54:34 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory
2011-04-08 10:00:43 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory

Perhaps using the file name "server.crt" postgres is looking for instead of
"db1_ssl.crt" would help?

That advice for full paths on the symlink not so useful. If you are in the
right directory then they are equivalent commands.

Carlos Mennens <carlos.mennens@gmail.com> writes:

I had self signed SSL certificates on my database server but since
then removed them and received updated certificates from the security
team. I removed (backedup) the old server.crt & server.key and now
have db1_ssl.crt & db1_ssl.key in the identical location as the old
SSL certificates. I then went to /etc/postgres/8.4/main and removed
the old symbolic links for the old certificates and generated new
symbolic links:

ln -s /etc/ssl/certs/db1_ssl.crt db1_ssl.crt
ln -s /etc/ssl/private/db1_ssl.key db1_ssl.key

I then restarted PostgreSQL and got the following error:

2011-04-08 09:54:34 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory
2011-04-08 10:00:43 EDT FATAL: could not load server certificate file
"server.crt": No such file or directory

Well, yeah. The server's key and cert files have to be named exactly
server.crt and server.key. They can be symlinks, I think, but you
can't just randomly use some other names and expect the server to intuit
that those are the files to use.

regards, tom lane

On Fri, Apr 8, 2011 at 2:01 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:

Per here:
http://www.postgresql.org/docs/8.4/static/ssl-tcp.html
File    Contents        Effect
server.crt      server certificate      requested by client
server.key      server private key      proves server certificate sent by
owner; does not indicate certificate owner is trustworthy
root.crt        trusted certificate authorities checks that client
certificate is signed by a trusted certificate authority
root.crl        certificates revoked by certificate authorities client
certificate must not be on this list

Rename your certs to above.

Oh I mis-understood. I just need to rename my symbolic links, not my
actual certificate file names. Changed symbolic link names and
everything is happy again.

Thanks so much for everyones help!