Is the import schema step even necessary anymore?

Openfire Openfire Support
1

I was trying to follow the openfire documentation but this part doesn’t seem to be working for me.

I’m using openfire with an AD connection and a postgresql database. I was able to get everything setup WITHOUT importing the database schema, but after changing some (openfire) system settings and rebooting my server, I was getting some database access errors in the openfire log, so I decided to start over and try importing the schema.

First of all, I tried importing the schema into the already existing database, but I just got a whole string of errors that seemed to indicate all the entries already existed. This was my first clue that perhaps the import step is not necessary.

Then I decided to drop the database completely and recreate it. Then I was able to import the openfire schema successfully. (see here for more details: I’m dying trying to import the postgresql schema )

Then I restarted the initial openfire config, and after inputting all my database authentication data, I’m getting this error from the web config:

“The Openfire database schema does not appear to be installed. Follow the installation guide to fix this error.”

But that is exactly what I did!

In contrast, when I used the webconfig WITHOUT importing the schema, I got no errors at that part of the setup. It seems to me that perhaps the webconfig is doing the schema import automatically but the instructions are still out of date. In fact, that page of the webconfig specifically states:

**Note: **Database scripts for most popular databases are included in the server distribution at [Openfire_HOME]/resources/database.

Is the

psql -d mydatabase -f /usr/share/openfire/resources/database/openfire_postgressql.sql

step still necessary?

2

OK I think I finally got everything working, but again, in case any other idiots find themselves in my same situation, I’m going to answer my own questions:

  1. Importing the database schema IS necessary.

  2. I believe that on my initial setup of openfire, I was using a 24-character password for my DB login, but the webconfig only accepts 20-characters max. Somehow, it was still letting me continue past the DB setup page on the web config, even though the passwords did not match (the webconfig clearly still needs more work). I didn’t realize that the webconfig was truncating my password because I was copy-pasting. This defect in the webconfig was hiding my problem with the schema.

  3. On the second go-round, using a 20-character DB password, the webconfig was working as intended and giving me the, valid, error that my DB schema was not imported.

  4. However, the DB schema WAS imported. So what was wrong? Well you’d have to reference my other thread here: I’m dying trying to import the postgresql schema.

So basically, I was able to import the schema as my main user on my Ubuntu box. I also created the database as that same user. Let’s call that user UbuntuMainUser. At the same time, I was telling openfire to login as a different user OpenfireDBUser. Now, I have granted both UbuntuMainUser and OpenfireDBUser ALL PRIVILEGES to the postgresql database, but apparently that is not enough for the webconfig. Since I had both created the database AND imported the schema using UbuntuMainUser, UbuntuMainUser was listed as owner for both the database AND all of its tables and webconfig didnt like this mismatch. I had to issue the following commands in psql to fix the problem:

ALTER DATABASE openfireDB OWNER TO OpenfireDBUser;

\c openfireDB;

REASSIGN OWNED BY UbuntuMainUser TO OpenfireDBUser;

To explain what is happening here: the first line changes the owner of the database, but DOES NOT affect the tables or any other database objects. The second line connects to the openfireDB database. Once connected, the last line reassigns ownership of ALL tables and database objects.

3

Own database is not same as default priviledges for the tables and so on.

I added this line before CREATE TABLE … lines in openfire_postgresql.sql

In this example openfireadm is owner of the db openfire.

GRANT ALL ON SCHEMA public TO openfireadm;

GRANT USAGE ON SCHEMA public TO openfireadm;

ALTER DEFAULT PRIVILEGES IN SCHEMA public

GRANT ALL ON TABLES TO openfireadm;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO openfireadm;

Then run the CREATE TABLES … and objects owner is openfireadm, not the user who has created the PG instance.