powered by Jive Software

Openfire connect to postgres via ssl

Hello all,

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

3.rm privkey.pem

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.

  1. 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

  1. enter password (default is changeit) and answer yes to “Do you trust this cert”

  2. on openfire machine#vim /etc/openfire/openfire.xml

  3. locate the jdbc serverURL parameter and append “?ssl=true”

  4. 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 … … …

  1. restart postgres

Hopefully this should be error free. If there are errors, I have found that openfire will not stay running.

Good Luck!

1 Like

Most useful - thanks for recording this.