PostgreSQL root.crt not loading

Question

I am running postgresql 9.1 on ubuntu 12.04.

I wanted to change the data directory of postgres. So I created a new directory and run initdb. I also made symbolic links of the certificate to the data directory.

But still postgres cant find root.crt:

sudo /etc/init.d/postgresql start * Starting PostgreSQL 9.1 database server * The PostgreSQL server failed to start. Please check the log output: 2012-08-17 16:02:33 CEST FATAL: could not load root certificate file "root.crt": no SSL error reported 

What am I doing wrong? The data directoy looks like this:

ls -la total 92 drwx------ 13 postgres postgres 4096 Aug 16 14:36 . drwxr-xr-x 3 root root 4096 Aug 16 13:57 .. drwx------ 5 postgres postgres 4096 Aug 16 14:00 base drwx------ 2 postgres postgres 4096 Aug 16 14:36 global drwx------ 2 postgres postgres 4096 Aug 16 14:00 pg_clog -rw------- 1 postgres postgres 4476 Aug 16 14:00 pg_hba.conf -rw------- 1 postgres postgres 1636 Aug 16 14:00 pg_ident.conf drwx------ 4 postgres postgres 4096 Aug 16 14:00 pg_multixact drwx------ 2 postgres postgres 4096 Aug 16 14:35 pg_notify drwx------ 2 postgres postgres 4096 Aug 16 14:00 pg_serial drwx------ 2 postgres postgres 4096 Aug 16 14:36 pg_stat_tmp drwx------ 2 postgres postgres 4096 Aug 16 14:00 pg_subtrans drwx------ 2 postgres postgres 4096 Aug 16 14:00 pg_tblspc drwx------ 2 postgres postgres 4096 Aug 16 14:00 pg_twophase -rw------- 1 postgres postgres 4 Aug 16 14:00 PG_VERSION drwx------ 3 postgres postgres 4096 Aug 16 14:00 pg_xlog -rw------- 1 postgres postgres 19169 Aug 16 14:00 postgresql.conf -rw------- 1 postgres postgres 126 Aug 16 14:35 postmaster.opts lrwxrwxrwx 1 postgres postgres 31 Aug 16 14:13 root.crt -> /etc/postgresql-common/root.crt lrwxrwxrwx 1 postgres postgres 36 Aug 16 14:13 server.crt -> /etc/ssl/certs/ssl-cert-snakeoil.pem lrwxrwxrwx 1 postgres postgres 38 Aug 16 14:13 server.key -> /etc/ssl/private/ssl-cert-snakeoil.key 

so root.crt should be there and visible.

Any suggestions?

Answer 1

Thank you Chida for your hint!

The output was:

 sudo -u postgres cat /etc/postgresql-common/root.crt This is a dummy root certificate file for PostgreSQL. To enable client side authentication, add some certificates to it. Client certificates must be signed with any certificate in this file to be accepted. A reasonable choice is to just symlink this file to /etc/ssl/certs/ssl-cert-snakeoil.pem; in this case, client certificates need to be signed by the postgresql server certificate, which might be desirable in many cases. See file:///usr/share/doc/postgresql-doc-8.3/html/ssl-tcp.html for details (in package postgresql-doc-8.3). 

So I pointed root.crt in the data directory to /etc/ssl/certs/ssl-cert-snakeoil.pem:

 ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem root.crt 

And then postgresql started without any error!

Answer 2

I ran into this issue, toyed with the symlink and actual /etc/ssl/* directory/file permissions and still kept getting this error.

Eventually, I fixed it by removing the symlinks in /var/lib/postgresql/*/main and copying the .pem and .key file out of the respective directories in /etc/ssl, changed their names to whatever the related symlink name was, set the group owner to ssl-cert, and was able to get the database to finally start back up.