powered by Jive Software

Guide / Tutorial / Writeup / Walkthrough / How to: Setting up Openfire 3.10.2 with a PostgreSQL DB, Active Directory / LDAP integration, and a Wildcard SSL Certificate

2017-09-04 Update: Openfire is now at version 4.1.5, but I think that most, if not all, of this guide is still relevant. Obviously, I cannot guarantee that until I actually do a fresh 4.1.5 (or higher) install on a brand new server (who knows when that will be). In the meantime, my confidence that this guide remains mostly relevant comes from the fact that I recently performed an upgrade of my server to Openfire 4.1.5 (from 3.10.2), to PostgreSQL 9.5 (from 9.3), and to Ubuntu 16.04.3 LTS (from 14.04.1 LTS) without any problem whatsoever. I have posted the upgrade guide here: Guide / Tutorial / Writeup / Walkthrough / How to: Upgrade Openfire 3.10.2 to 4.1.5, upgrade PostgreSQL 9.3 to 9.5, Upgrade Ubuntu 14.04.1 LTS to 16.04.3 LTS

I will not be maintaining or updating this document from this point onward. Any further updates (if necessary) will be posted as new documents, and those will be linked from the “upgrade” document that I linked above (forming a chain of links in chronological order). I will not be posting a new document for every upgrade - only when the upgrade process changes or introduces some new wrinkle. As the upgrade process is fairly straightforward and hassle-free, I don’t anticipate that I will need to update the “upgrade” guide for 4.1.6, etc. If someday I do a fresh install of Openfire to a new server, then I will repost the “install” guide below as a new document with an updated title (updated version numbers) and any relevant changes to the process.

Original “install” guide follows:


In case this might help anyone else out, I wrote up a little step-by-step guide for this process in case I ever have to do it again. Let me know if you come across any errors or omissions and I will try to keep this updated:

Overall Reference: https://www.igniterealtime.org/builds/openfire/docs/latest/documentation/index.h tml

**Objectives **

  1. Get Openfire running
  2. Store to a PostgreSQL database
  3. Retrieve users from Active Directory (Tested with Windows Server 2012 Active Directory and Zentyal PDC running samba4)
  4. Use AD (LDAP) Users, but Local Groups
  5. Filtering only real Openfire users
  6. Secure server with SSL certificate


For purposes of this document, $ should mean Ubuntu shell, while =# indicates PostgreSQL shell.

I use vi as my editor of choice, but feel free to replace all references to vi with vim, nano, gedit, or whatever you like.

As of the latest edit, this document applies to the following software versions:

Ubuntu 14.04.1 LTS

Openfire 3.9.3 and 3.10.2

PostgreSQL 9.3

Oracle Sun Java 7 and 8


1. Install Ubuntu

i. Setup primary user: UbuntuMainUser

Reference: http://www.simpleprecision.com/how-to-set-a-static-ip-to-ubuntu-14-04-server/

ii. Set network config

   $ sudo vi /etc/network/interfaces

Reference: https://igniterealtime.jiveon.com/thread/52470

2. Download Openfire .deb

$ wget --no-certificate-check https://download.igniterealtime.org/openfire/openfire_3.10.2_all.deb

Reference: https://help.ubuntu.com/community/Java, http://www.duinsoft.nl/packages.php

3. Install latest version of Oracle Sun Java

i. $ sudo vi /etc/apt/sources.list

   Add line: deb [http://www.duinsoft.nl/pkg](http://www.duinsoft.nl/pkg) debs all

ii. Import gpg key

   $ sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 5CB26B26

iii. Install Java

   $ sudo apt-get update

   $ sudo apt-get install update-sun-jre

Reference: https://help.ubuntu.com/community/PostgreSQL, https://www.igniterealtime.org/builds/openfire/docs/latest/documentation/databas e.html

4. Install latest version of PostgreSQL

$ sudo apt-get install postgresql postgresql-contrib

**5. Setup PostgreSQL database **

i. Set postgres user password

   $ sudo -u postgres psql postgres

   =# \password postgres

Ctrl + D

ii. Create Database for Openfire

   $ sudo -u postgres createdb -E UNICODE openfire_db

iii. Create User and User Password for Openfire Database access

   $ sudo -u postgres createuser -D -A -P db_admin

   Reference: [http://www.postgresql.org/docs/9.3/static/sql-grant.html](http://www.postgresql.org/docs/9.3/static/sql-grant.html)

iv. Give User Access to Openfire Database

   $ sudo -u postgres psql postgres

   =# GRANT ALL PRIVILEGES ON openfire_db TO db_admin;

Ctrl + D

6. Install Openfire .deb

$ sudo dpkg -i openfire_3.10.2_all.deb

7. Import Openfire’s PostgreSQL schema

i. Add user UbuntuMainUser to PostgreSQL

   $ sudo -u postgres createuser --superuser UbuntuMainUser

   $ sudo -u postgres psql

   =# \password UbuntuMainUser

Ctrl + D

ii. Grant all privileges to openfire_db to UbuntuMainUser

   $ sudo -u postgres psql postgres

   =# GRANT ALL PRIVILEGES ON DATABASE openfire_db TO "UbuntuMainUser";

Ctrl + D

iii. Copy schema file to UbuntuMainUser’s home directory

   $ cp ../openfire/resources/database/openfire_postgresql.sql /home/UbuntuMainUser/

iv. Take ownership of schema file

   $ sudo chown UbuntuMainUser:UbuntuMainuser openfire_postgresql.sql

v. Import schema file to Openfire Database

   $ psql -d openfire_db -f /home/UbuntuMainUser/openfire_postgresql.sql

vi. Give ownership of Openfire Database back to Openfire Database User

   $ sudo -u postgres psql postgres

   =# ALTER DATABASE openfire_db OWNER to db_admin;

   =# \c openfire_db;

   =# REASSIGN OWNED BY "UbuntuMainUser" TO db_admin;


8. Run Openfire webconfig at http://your.ubuntu.server.IP:9090

i. Language: English (or your choice)

ii. Server Info

   *Domain*: Put here whatever you want to be after @ for your Openfire usernames.  For example, if you want Jessica@openfire.company.com, put 'openfire.company.com' here.

Blowfish Encryption: Create your own long string of characters, letters, numbers, and symbols here


External Database

Type: PostgreSQL

        Address: [http://localhost:5432/openfire_d ](http://localhost:5432/openfire_db)  (Use database name from step 5.ii.)

        Login: db_admin (User name from step 5.iii.)

Password: (from step 5.iii.)

Reference: https://forum.zentyal.org/index.php/topic,23324.0.html

iv.Profile (Directory Server / LDAP)

Double check your DN information on your samba4 server with the following command:

        $ sudo ldbsearch  -H /var/lib/samba/private/sam.ldb > ldbsearchresults.txt

        $ vi ldbsearchresults.txt

Double check your DN information on your Windows Server using the dsquery CLI tool

Domain: local.company.com

Base DN: CN=Users,DC=local,DC=company,DC=com

Administrator DN: CN=AdministratorName,CN=Users,DC=local,DC=company,DC=com

Administrator Password: (from your PDC)

v. Admin User

If you have done everything right you should be able to add a user as an admin from your LDAP

Reference: https://igniterealtime.jiveon.com/thread/33144

9. Ignore Active Directory (LDAP) Groups

i. In Openfire webconfig: Server -> System Properties -> provider.group.className -> Edit

ii. Erase any value and leave blank

iii. Save

iv. Restart Openfire server

   $ sudo service openfire restart

Reference: https://igniterealtime.jiveon.com/thread/30063, https://www.igniterealtime.org/builds/openfire/docs/latest/documentation/ldap-gu ide.html

10. Setup Filter

i. On your Primary Domain Controller (PDC), create a new group called “OpenfireUsers”

ii. Add all the users to whom you want to provide access to group “OpenfireUsers”

iii. In Openfire webconfig: Server -> System Properties -> ldap.searchFilter -> Edit

iv. Insert value: (&(objectClass=organizationalPerson)(memberOf=CN=OpenfireUsers,CN=Users,DC=local ,DC=Company,DC=com))

v. Save

vi. Restart Openfire server

 $ sudo service openfire restart

Reference: https://help.ubuntu.com/community/IptablesHowTo, https://www.linode.com/docs/applications/messaging/instant-messaging-services-wi th-openfire-on-ubuntu-12-04-lts-precise-pangolin

11. Setup Ubuntu Firewall

 i. Setup rules

$ sudo iptables -A INPUT -m conntrack --ctstate ESTABLISHED,RELATED -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport ssh -j ACCEPT (Ignore if you don’t want SSH access; Change if using different SSH port)

$ sudo iptables -A INPUT -p tcp --dport 3478 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 3479 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 5222 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 5223 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 5229 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 7070 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 7443 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 7777 -j ACCEPT

$ sudo iptables -A INPUT -p tcp --dport 9090 -j ACCEPT (Ignore if you don’t want to allow http webconfig access)

$ sudo iptables -A INPUT -p tcp --dport 9091 -j ACCEPT (Ignore if you don’t want to allow https webconfig access)

$ sudo iptables -I INPUT 1 -i lo -j ACCEPT

$ sudo iptables -A INPUT -j DROP

ii. Setup iptable rules to survive reboot

$ sudo iptables -L -v (Check IPTables)

$ sudo sh -c “iptables-save > /etc/iptables.rules”

$ sudo vi /etc/network/interfaces

Add line at end of eth0: pre-up iptables-restore < /etc/iptables.rules

$ sudo shutdown -r now

$ sudo iptables -L -v (Confirm New Rules survive reboot)

12. Install Security Certificate

** ** i. Upload your certificate files to your Openfire server.

** **In this case I used a wildcard certificate from Comodo.

      Copy files to a working directory:

            $ sudo mkdir /etc/ssl

            $ sudo cp /home/UbuntuUser/certificate_files* /etc/ssl

            $ cd /etc/ssl

 ii. Import your certificates into Openfire

      If you have a separate certificate.crt and private-key-decrypted.key and intermediates.ca-bundle files:

             $ sudo openssl pkcs12 -export -in "/etc/ssl/certificate.crt" -inkey "/etc/ssl/private-key-decrypted.key” -certfile "/etc/ssl/intermediates.ca-bundle” -out "/etc/ssl/combined.p12" -name "myserver.domain.com.certificate.2015.06.30"

Export Password: changeit

             $ sudo keytool -importkeystore -destkeystore keystore.mydomain.2015.06.30 -srckeystore combined.p12 -srcstoretype pkcs12 -alias "myserver.domain.com.certificate.2015.06.30"

Destination Keystore Password: changeit

Source Keystore Password: changeit

OR If you have a single certificate.pfx file

             $ sudo keytool -importkeystore -destkeystore keystore.mydomain.2015.06.30 -srckeystore certificate.pfx -srcstoretype pkcs12 -alias "myserver.domain.com.certificate.2015.06.30"

Destination Keystore Password: changeit

Source Keystore Password: changeit


                $ sudo /etc/init.d/openfire stop

                $ sudo -i

                $ cd /usr/share/openfire/resources/security

                $ cp keystore keystore.bak

                $ rm keystore

                $ cp /etc/ssl/keystore.mydomain.2015.06.30 ./keystore

                $ chmod 640 keystore

                $ chown openfire:openfire keystore

                $ ls -l (double-check)

                $ su UbuntuUser

                $ sudo /etc/init.d/openfire restart

 iii. Confirm certificates are working:

      Try connecting through a web browser to the web admin via https:// and port 9091

      Try connecting to the server from a Jabber client using SSL encryption

** Test your OpenFire server at xmpp.net**

      DO NOT WORRY if the web config under System Settings -> Security Certificates reports that One or more certificates are missing.  This is a bug in the web config.

      Note: Documentation states (Openfire: SSL Guide) that it is possible to change the default keystore and certificate passwords by using the xmpp.socket.ssl.keypass server property, but I was never able to get my certificates properly loaded using anything but the default passwords of changeit.  Similarly, you should be able to change the default keystore location using xmpp.socket.ssl.keystore but I was again unsuccessful.

13. Setup DNS SRV Records

** ** Note: If you use a split-horizon DNS, you will need to add DNS SRV records to both your local private DNS server and your public DNS server. In my case I have a public DNS server with namecheap (but you might use DynDNS or whatever), and I have a local private DNS server running on Windows Server 2012 R2). Consult the Openfire webadmin page (https://your.ubuntu.server.IP:9091/dns-check.jsp) for more info on the exact DNS SRV records that your install is expecting.

Optional step: Allow external access to PostgreSQL database via PGAdminIII tool

i. Allow incoming connections

   $ sudo vi /etc/postgresql/9.3/main/pg_hba.conf

Add line: host all all md5

ii. Listen on all interfaces

   $ sudo vi /etc/postgresql/9.3/main/postgresql.conf

Add line: listen_addresses=’*’

iii. Restart postgresql

   $ sudo service openfire stop

   $ sudo service postgresql restart

   $ sudo service openfire start

iv. Download and instal pgAdminIII for your client and connect to the IP of the Openfire/PostgreSQL server

This document was generated from the following discussion: Guide / Tutorial / Writeup / Walkthrough: Setting up Openfire 3.9.3 with a PostgreSQL DB and Active Directory / LDAP integration

**2017-09-04 Edit / Update: **The Edit button has reappeared, so I was able to update the original document above with the three changes I listed below. I have also successfully updated my server to Ubuntu 16.04.3 LTS, OpenFire 4.1.5, PostgreSQL 9.5 without any problems. See upgrade guide here: Guide / Tutorial / Writeup / Walkthrough / How to: Upgrade Openfire 3.10.2 to 4.1.5, upgrade PostgreSQL 9.3 to 9.5, Upgrade Ubuntu 14.04.1 LTS to 16.04.3 LTS

Original comment follows (but there is no need to read it as of 2017-09-04):


Hm, I’d like to update this, but I can’t seem to find the edit button anymore? Is there a time-limit past which revisions are no longer accepted?

Three issues with this guide (as of the moment):

1. In section 12.ii., I forgot to include intermediate certificates (though these could be included in a combined .pfx file if I remember correctly). Just remember to also include your intermediate certificates (if necessary) and not just your private key and certificate file.

The following line from the original guide is incomplete, because it does not expressly include the intermediate certificate chain:

$ sudo openssl pkcs12 -export -in “/etc/ssl/certificate.crt” -inkey “/etc/ssl/private-key-decrypted.key” -out “/etc/ssl/combined.p12” -name “myserver.domain.com.certificate.2015.06.30”

This updated line includes an additional -certfile "/etc/ssl/intermediates.ca-bundle” which will ensure you don’t run into any certificate problems:

$ sudo openssl pkcs12 -export -in “/etc/ssl/certificate.crt” -inkey "/etc/ssl/private-key-decrypted.key” -certfile "/etc/ssl/intermediates.ca-bundle” -out “/etc/ssl/combined.p12” -name “myserver.domain.com.certificate.2015.06.30”

This caused me intermittent problems later (as revealed by xmpp.net testing - make sure you are passing most all of the tests there!). This may also (I can’t remember) have been related to my problem in 12.iii…

2. In section 12.iii. I may have incorrectly stated DO NOT WORRY if the web config under System Settings -> Security Certificates reports that the certificate is Pending Activation. This is a bug in the web config.

(See SSL Certificates stuck in "Pending Verification” for more info on the “Pending” message, though I can’t vouch for whether that solution is legitimate or not).

Again, I can’t remember exactly, but the “Pending Activation” “bug” may have been a result of not uploading my Intermediate Certificates. I can’t check right now because most of this guide was written for Openfire 3.9.3 and, although I reused the guide myself for a fresh install of 3.10.2, I did not check every detail. What I can definitively say is that I am running 3.10.2 now, and I do have all my certificates correctly installed, and there is still a bug in the web config, but it is a different error message. So I will rewrite the above disclaimer to say (for 3.10.2):

DO NOT WORRY if the web config under System Settings -> Security Certificates reports that One or more certificates are missing. This is a bug in the web config.

3. Openfire is now on version 4, and I have no idea how much, if any, or if all, of this guide still applies. I am about to attempt an upgrade of my 3.10.2 system and see what happens

I need to edit this document. The formatting is all screwed up, perhaps since the migration? Like, any of the lines that should be [code] are not, and vice versa.

Sorry about that, our conversion of forum software ruined some posts. Are you saying that you can’t edit the post or that you just need to find the time to edit it sometime in the future?

I don’t see the option to edit this.

Under the first message you don’t see a pencil icon? Right besides the usual place of like and flag icons.

Nope. You told me before that posts have an editing “expiration” date, and they can’t be edited after a certain time has passed. Then you said you extended the editing date for me.

Do you see one now?


How about now?