Guide / Tutorial / Writeup / Walkthrough: Setting up Openfire 3.9.3 with a PostgreSQL DB and Active Directory / LDAP integration

**NOTE: I am NOT maintaining this original posting. Please see this posted doc for any updated information: 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 **

Some updates made to the above doc as of the time of this edit:

  1. Confirmed tested with Windows Server 2012 Active Directory
  2. Confirmed tested with Openfire 3.10.2
  3. Confirmed tested with Oracle JDK 1.8
  4. Added instructions for setting up a wildcard SSL certificate from Comodo
  5. Some minor errors fixed (incorrect internal references, minor missing steps, a few steps out of order, etc)
  6. Overall readability improved

If and/or when I continue to update the above-linked doc, even more changes may be made.
I will only be making updates to the above-linked doc; I will NOT be making any further edits or updates to this posting.

Original posting begins now:

=====================================================================

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 (Zentyal PDC running samba4 in this case)
  4. Use AD (LDAP) Users, but Local Groups
  5. Filtering only real Openfire users

Notes

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

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

Ubuntu 14.04.1 LTS

Openfire 3.9.3

PostgreSQL 9.3

Oracle Sun Java 7

Steps:

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.9.3_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 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. 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

7. Install Openfire .deb

$ sudo dpkg -i openfire_3.9.3_all.deb

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

iii.Database

External Database

Type: PostgreSQL

        Address: [http://localhost:5432/openfire_db](http://localhost:5432/openfire_db)

Login: db_admin

Password: (from step 6.i.)

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

iv.Profile

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

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=loc al,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

$ 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

$ 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)

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 192.168.1.0/24 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

I think you should convert it to a document (a menu on the right), so one could give you a few stars Well, so it would be editable if someone wants to improve it.

Good idea. How does that work here? If anyone can edit it, does the system keep track of revisions?

Yep, it will save revisions and let you compare them side by side. At least that worked in the older version of forums, but it should work now as well.

Now THAT is documentation. Thanks Dan.

Daniel, I’m getting notifications that this thread has been updated. Are you modifying something here? Or maybe it is better to lock this thread?