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

Document created by Daniel Castellanos on Sep 22, 2014Last modified by Daniel Castellanos on Sep 5, 2017
Version 12Show Document
  • View in full screen mode

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: tml




  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



  ii. Set network config

       $ sudo vi /etc/network/interfaces




2. Download Openfire .deb

  $ wget --no-certificate-check




3. Install latest version of Oracle Sun Java

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

       Add line: deb debs all

  ii. Import gpg key

       $ sudo apt-key adv --keyserver --recv-keys 5CB26B26

  iii. Install Java

       $ sudo apt-get update

       $ sudo apt-get install update-sun-jre



Reference:, 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


  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;

       CTRL + D


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, put '' 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   (Use database name from step 5.ii.)

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

            Password: (from step 5.iii.)



  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



       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




9. Ignore Active Directory (LDAP) Groups

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

  ii. Erase any value and leave blank

  iii. Save

  iv. Restart Openfire server

       $ sudo service openfire restart



Reference:, 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:, 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 files:

                 $ sudo openssl pkcs12 -export -in "/etc/ssl/certificate.crt" -inkey "/etc/ssl/private-key-decrypted.key” -certfile "/etc/ssl/” -out "/etc/ssl/combined.p12" -name ""

                         Export Password: changeit

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

                         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 ""

                         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

          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