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

Version 12

    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