[update] postgresql openfire_postgresql.sql some updates

Openfire Openfire Dev
1

openfire_postgresql.sql has tested for PG 7.2.

Current PG version is 9.4.

I installed OpenFire to use it. And it works fine.

Some addon for that:

Need create some user which has priviledge only for openfire database, ex. openfireadm. It’s not safety to use some superuser, because users are global in Postgresql instance.

createuser -P -e -D -A -r openfireadm

  • give the passwd

Create database openfire, lc-* is important only for sorting, example for Finland:

createdb -O openfireadm -E UTF8 --lc-collate=fi_FI.utf8 --lc-ctype=fi_FE.utf8 openfire

Add to the

The begin of the openfire_postgresql.sql:

– Postgres >=9.x

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 the old lines

CREATE TABLE ofUser ( …

Then do it:

psql -d openfire -f openfire_postgresql.sql

After those openfireadm own the schema public and all tables in schema public.

2

Are you sure this is necessary? I’ve not had issues when creating a non-superuser and giving them ownership of a single database.

3

If you have created PG instance using ex. user pgmaster, then also new database default owner for the objects is pgmaster even database owner is ex. openfireadm.

PG user and database creating using some pgadmin user:

createuser -P -e -D -A -r openfireadm

createdb -O openfireadm -E UTF8 --lc-collate=en_EN.utf8 --lc-ctype=en_EN.utf8 openfire

If your openfire server user is openfire and you create user openfireadm for PG and then run openfire_postgresql.sql using

pgsql -U openfireadm psql -h localhost -p 5432 -d openfire -f openfire_postgresql.sql

then table has created owned by user openfireadm.

It’s normal that you manage your PG database instance using some pgadmin user, then you need to tell that also openfireadm need “full access”. In this example openfireadm is user which you will use in jdbc connection.

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

If you run this using some pgadmin user

pgsql psql -h localhost -p 5432 -d openfire -f openfire_postgresql.sql

then object owner is pgadmin, not openfireadm even db is owned by openfireadm.

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

In this example openfireadm is owner of the db openfire.

With this lines any user can run openfire_postgresql.sql

and privileges is correct for openfireadm user.

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 openfireadm has also full access.