I would say that if you pass credentials for an account (database account) which is the owner of the database (not superuser), then it should already work. Then the ​only thing would be to "notify" users about it.

I am kind of hesitant to complicate logic on the Tigase just for this. I would rather have 2 options:

• automatic - current mode which requires user credentials with correct permissions or
• manual - where user can upload DB schema manually and then use whatever user account and method he wants

@kobit It wouldn't complicate the logic that much (not more than it already is).

Benefit would be purely PR - "look, we care about security and privacy" et al.

As @andrzej.wojcik correctly pointed out, this should work just fine right now (passing user credential as root credential) so the work would be:

• add parameter to schema-loader to use user credentials for root as default
• update web-installer to present only db-name, username, password (and possibly a checkbox to enable explicitly root username/password).

This could possibly make updating (update-schema) easier as it wouldn't ask for root credentials to run the procedure.

ok

in #helpdeskpr-572 and #helpdeskpr-585 and interesting issue was risen: we don't pass all configured parameters from the configuration file to the SchemaUpgrade. It should be relatively straightforward to handle this in tigase.db.util.DBSchemaLoader.Parameters#parseUri and additional map with all unknown properties.

Feature implemented. Please merge https://github.com/tigase/tigase-server/pull/110 and https://github.com/tigase/tigase-http-api/pull/7

Note: There is a -post database creation task for PostgreSQL which sets permissions for created procedures but I think it is irrelevant as we are creating those procedures and tables with user-level permissions.

Note: -post and -pre scripts are not called anymore without root credentials as those required higher permissisons.

Note: -post and -pre scripts are not called anymore without root credentials as those required higher permissisons.

Yes, this is OK and was part of the task.

I reviewed the changes and tested them. There was an issue with installing uuid extension in postgres (result of #prv-485) but it turns out that it can be installed using regular user if it's owner of the database (which it is) thus I moved the installation to installer-create-db file (to have it setup in general in the database) and postgresql-message-archiving-3.0.0 (to have it supported during upgrade as it's the only place that uses it).

Small addendum - it seems that the limitations to install uuid extension were changed in recent versions of the postgresql. After reviving our TTS-NG postgresql build failed with "Failure: ERROR: permission denied to create extension "uuid-ossp"" (https://tc.tigase.net/buildConfiguration/TigaseTtsNgTests_PostgreSQL/94409?showLog=94409_1142_81.1184). I bumped postgresql version from 11 to 13 and it works. Minimal postgresql version will have to be updated in the documentation.

Documentation updated, minimum recommended version set to 13, added note that older version requires manual installation of of uuid extension.