I revisited an old post of mine http://community.igniterealtime.org/message/196252#196252 and wanted to update it with procedures so perhaps it may help others.
I had the need to connect to postgres (on a separate machine) via a secure connection with the trust being one-way(self signed server certs only).
postgres and openfire are running on linux machines.
On the postgres side there was already server.cert pointer to ssl-cert-snakeoil.pem and server.key pointer to ssl-cert-snakeoil.key located in postgres’s data directory.
I moved these and created my own.
1.openssl req -new -text -out server.req
2.openssl rsa -in privkey.pem -out server.key
4.openssl req -x509 -in server.req -text -key server.key -out server.crt
5.chmod og-rwx server.key
6.chown postgres.postgres server.* (or whichever user postgres runs under)
7.openssl x509 -in server.crt -out server.crt.der -outform der
8.now copy server.crt.der over to you openfire machine and import this into the cacerts keystore.
9.ps aux | grep openfire will tell you which jre (if you have multiple jres) openfire is running with.
- navigate to the proper jre jre/lib/security
11.keytool -import -alias postgres -keystore path_to_your_jre/lib/security/cacerts -file path_to_cert_from_postgres/server.crt.der
enter password (default is changeit) and answer yes to “Do you trust this cert”
on openfire machine#vim /etc/openfire/openfire.xml
locate the jdbc serverURL parameter and append “?ssl=true”
on postgres machine# vim /etc/postgresql/8.x/main/pg_hba.conf and add the following:
hostssl openfire openfire_connection_user openfire_server_ipaddress md5
16.remove the following line:
host openfire … … …
- restart postgres
Hopefully this should be error free. If there are errors, I have found that openfire will not stay running.