Weblate migration script assumes single database of which it is the owner

[siepkes]

Describe the bug

weblate/memory/migrations/0007_use_trigram.py assumes that Weblate is installed in a single database to which the Weblate user has super user rights. This leads to an error when Weblate is used in it's own schema in a database of which it is not the owner.

This is a more common setup then one might think (ie. single database with a schema per application / tenant) because replication, backup, etc. is done at the database level in PostgreSQL. Meaning that if one uses a database per application / tenant you would need configure and monitor replication, backup, etc. for each application separately. Creating an unreasonable amount of administrative overhead.

I worked / hacked around this issue to change the default for my Weblate user, not the database itself:

    schema_editor.execute(
        "ALTER ROLE weblate SET pg_trgm.similarity_threshold = 0.7"
#        "ALTER DATABASE {} SET pg_trgm.similarity_threshold = 0.7".format(
#            schema_editor.connection.settings_dict["NAME"]
#        )
    )

The full error for reference:

Operations to perform:
  Apply all migrations: accounts, addons, admin, auth, authtoken, checks, contenttypes, fonts, gitexport, lang, memory, screenshots, sessions, sites, social_django, trans, weblate_auth, wladmin
Running migrations:
WARNING Handled exception: ProgrammingError: must be owner of database postgres

  Applying memory.0007_use_trigram...Traceback (most recent call last):
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InsufficientPrivilege: must be owner of database postgres

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 32, in <module>
    main()
  File "/opt/weblate/app/weblate/runner.py", line 32, in main
    execute_from_command_line(argv)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/core/management/base.py", line 328, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/core/management/base.py", line 369, in execute
    output = self.handle(*args, **options)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 233, in handle
    fake_initial=fake_initial,
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/migrations/operations/special.py", line 190, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/opt/weblate/app/weblate/memory/migrations/0007_use_trigram.py", line 18, in create_index
    schema_editor.connection.settings_dict["NAME"]
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 142, in execute
    cursor.execute(sql, params)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/weblate/py3-venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: must be owner of database postgres

To Reproduce

Update existing installation which requires the 0007_use_trigram.py migration to run.

[nijel]

Yes, it currently does. It also calls CREATE EXTENSION which is also at database level and can potentially conflict with other users of the same database. I don't see a remediation in this case, so I'm not sure this is worth addressing.

For the similarity threshold we could use in query parameters, but that seems to have negative effect on index usage compared to when setting the parameter this way. It would be good to know if setting the user parameter makes it still utilize the index.

[gioppoluca]

Can you specify better the documentation that already suggest to install that the extension needs to be installed (but not configured as the migration file try to do). Also in the code add an environment variable that allow for choosing not to execute operations that require the "OWNER" commands, leaving to be done externally? In this case the install could terminate correctly Could be "WEBLATE_IS_DB_OWNER = true/false" this way could be a workaround to this problem

[gioppoluca]

I think that the hack proposed could be activated based on the env var allowing those who are in this situation to continue to work on.

[gioppoluca]

@siepkes do you know in the docker image where is placed this file? I've found it in the source but cannot find it in the container, this way I will be able to use your workaround mounting the edited file in the proper place. Thanks

[siepkes]

@gioppoluca I don't know where the file is in the Docker image. I did a custom installation of Weblate on Triton / SmartOS (a Illumos based OS which in turn is a Solaris derivative) in a native zone.

[gioppoluca]

In the docker container we need to overwrite the files in this way:

  weblate:
    image: weblate/weblate:4.1.1-2
    volumes:
      - weblate_data:/app/data
      - ./weblate/0007_use_trigram.py:/usr/local/lib/python3.7/dist-packages/weblate/memory/migrations/0007_use_trigram.py
      - ./weblate/0008_adjust_similarity.py:/usr/local/lib/python3.7/dist-packages/weblate/memory/migrations/0008_adjust_similarity.py

where the 2 files have to be altered as @siepkes suggested in the beginning the other thing that needs to be done is to have added the extension in PostgreSQL outside weblate in the schema with a command like this one: CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA weblate;

Since the ALTER ROLE command as suggested does the work could you change it in the files so we do not have to override by mounting them this should make the application work as expected and not requiring superuser role

[github-actions[bot]]

Thank you for your report, the issue you have reported has just been fixed.

• In case you see a problem with the fix, please comment on this issue.
• In case you see a similar problem, please open a separate issue.
• If you are happy with the outcome, consider supporting Weblate by donating.

[alexsegura]

Wow thank you @siepkes & @gioppoluca for the workaround about mounting the files 🤗

@nijel As I was upgrading from 3.x, I had to go through 4.1.1, which doesn't contain bbf6f11e21dabfee760725b2ebb32fe84e8180f6. Maybe it should be backported into 4.1.x?

[reloxx13]

I just hit this problem in current update to 4.15.2

$ ./manage.py migrate
Operations to perform:
  Apply all migrations: accounts, addons, admin, auth, authtoken, checks, configuration, contenttypes, fonts, gitexport, glossary, lang, memory, metrics, screenshots, sessions, social_django, trans, utils, vcs, weblate_auth, wladmin
Running migrations:
  Applying memory.0013_reindex...Traceback (most recent call last):
  File "/usr/local/lib/python3.7/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InsufficientPrivilege: FEHLER:  keine Berechtigung, um Erweiterung »btree_gin« zu erzeugen
TIP:  Nur Superuser können diese Erweiterung anlegen.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 18, in <module>
    main(developer_mode=True)
  File "/home/weblate/weblate/runner.py", line 19, in main
    utility.execute()
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/base.py", line 89, in wrapped
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/django/core/management/commands/migrate.py", line 246, in handle
    fake_initial=fake_initial,
  File "/usr/local/lib/python3.7/dist-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.7/dist-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/usr/local/lib/python3.7/dist-packages/django/db/migrations/executor.py", line 227, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.7/dist-packages/django/db/migrations/migration.py", line 126, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/usr/local/lib/python3.7/dist-packages/django/db/migrations/operations/special.py", line 190, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/home/weblate/weblate/memory/migrations/0013_reindex.py", line 17, in create_index
    schema_editor.execute("CREATE EXTENSION IF NOT EXISTS btree_gin")
  File "/usr/local/lib/python3.7/dist-packages/django/db/backends/base/schema.py", line 145, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/dist-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.7/dist-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.7/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.7/dist-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.7/dist-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: FEHLER:  keine Berechtigung, um Erweiterung »btree_gin« zu erzeugen
TIP:  Nur Superuser können diese Erweiterung anlegen.

Workaround: Grant Superuser right to the psql weblate user

su - postgres
psql
ALTER USER weblate WITH SUPERUSER;

# Run migrations now in another shell and remove super user
# Check roles with \du in psql console

ALTER USER weblate WITH NOSUPERUSER;

[nijel]

It doesn't have to be superuser, you can create the extension before installing as documented in https://docs.weblate.org/en/latest/admin/install.html#creating-a-database-in-postgresql

[reloxx13]

It doesn't have to be superuser, you can create the extension before installing as documented in https://docs.weblate.org/en/latest/admin/install.html#creating-a-database-in-postgresql

i was afraid that the migration will fail with "extension already exist" if i create it beforehand. good to know, i try to remember it next time :D

[nijel]

You don't have to remember that, it is always documented in the upgrading instructions as well, so all you have to remember is to read the documentation :-).

https://docs.weblate.org/en/latest/admin/upgrade.html#upgrade-from-4-14-to-4-15