PostgreSQL SSL root.crt not loading
Permissions are OK. I have working:
-rw-r--r-- 1 postgres postgres 615 2011-04-25 16:23 root.crt
-rw------- 1 postgres postgres 692 2011-04-25 17:20 server.crt
-rw------- 1 postgres postgres 887 2011-04-25 17:17 server.key
Try to put this files in data directory (/var/lib/postgresql/9.0/{clustername}), not config directory (/etc/postgresql/9.0/{clustername}).
When cluster is created there are automatically provided snakeoil server.key and server.crt in data directory, but there is no root.crt. Probably you put your certs in config directory.
To start in SSL mode, the files server.crt and server.key must exist in the server's data directory. These files should contain the server certificate and private key, respectively. If the private key is protected with a passphrase, the server will prompt for the passphrase and will not start until it has been entered.
To require the client to supply a trusted certificate, place certificates of the certificate authorities (CA) you trust in the file root.crt in the data directory.
In Ubuntu:
cat /etc/postgresql/9.0/main/postgresql.conf | grep data_dir
data_directory = '/var/lib/postgresql/9.0/main' # use data in another directory
Related videos on Youtube
malaverdiere
Updated on September 17, 2022Comments
-
malaverdiere over 1 year
I am running PostgreSQL 9 on Ubuntu (from their PPA repository). I am using OpenSSL 0.9.8o.
I have generated keys and certificates using TinyCA2 for both a pg server and the psql client. I essentially followed the instructions.
My pg_hba.conf file is configured with this:
hostssl all abc ::1/128 cert clientcert=1
I have put the root certificate generated by TinyCA along with the server's certificate and key in the DATA directory as follows.
sudo unzip database_server.zip sudo mv sudo mv cacert.pem root.crt sudo mv cert.pem server.crt sudo openssl rsa -in key.pem -out server.key sudo chmod 0600 server.key sudo chmod ga=r root.crt sudo chown postgres:postgres root.crt server.key server.crt
Yet I am unable to start the server. This is what I get on startup:
$ sudo /etc/init.d/postgresql start 9.0 * Starting PostgreSQL 9.0 database server * The PostgreSQL server failed to start. Please check the log output: 2011-03-17 16:39:13 IST LOG: client certificates can only be checked if a root certificate store is available 2011-03-17 16:39:13 IST HINT: Make sure the root.crt file is present and readable. 2011-03-17 16:39:13 IST CONTEXT: line 93 of configuration file "/etc/postgresql/9.0/main/pg_hba.conf" 2011-03-17 16:39:13 IST FATAL: could not load pg_hba.conf
Interestingly, the root.crt file is very much present and readable:
$ ll <snip> -rw-r--r-- 1 postgres postgres 143 2010-12-01 17:06 pg_ctl.conf -rw-r----- 1 postgres postgres 4.3K 2011-03-17 16:35 pg_hba.conf -rw-r----- 1 postgres postgres 1.7K 2011-03-17 15:58 pg_ident.conf -rw-r--r-- 1 postgres postgres 18K 2011-02-07 18:38 postgresql.conf -rw-r--r-- 1 postgres postgres 2.8K 2011-03-17 16:39 root.crt -rw------- 1 postgres postgres 2.2K 2011-03-17 14:37 server.crt -rw------- 1 postgres postgres 891 2011-03-17 16:18 server.key -rw------- 1 postgres postgres 963 2011-03-17 14:37 server.key.encrypted
What is going on? What do I have to do for this certificate to load???