Dissatisfied with new authentik releases > 202310: Database migration of authentik is disastrous

[volker-raschek]

Hello everyone, I would like to share my dissatisfaction with the new authentik releases for kubernetes.

Last year I installed authentik via helm v2023.10.4. The database used was an external postgres database. The documentation for configuring the postgres database left every freedom open. So it was decided to create a dedicated user on an existing database and use its schema for authentik. The following SQL statements were executed:

CREATE SCHEMA ${USER};
CREATE USER ${USER} WITH LOGIN PASSWORD '${PASSWORD}';
GRANT ALL ON SCHEMA ${USER} TO ${USER}; 
ALTER USER ${USER} SET search_path=${USER};

The installation and operation worked perfectly. Until the first disillusionment...

With the first update in February, the complete helm chart and its values changed. This already led to a nightmare. Now the update was paused until this week due to lack of time and decided to update to 2024.4.2 right away.

The merging of the values.yaml went without problems for the time being. However, starting the Worker immediately gave me a headache when I saw the error message that the schema template could not be created. I was quite annoyed because I couldn't find anything in the documentation about authentik needing permissions to create the schema template.

Well, I created the schema and granted the permissions to the user. The worker pod was restarted to trigger the migration again. The next surprise follows promptly, authentik cannot create the table django_migrations in the schema public. I wonder what the hell authentik is doing under the hood. My conviction that authentik was the right choice half a year ago is slowly beginning to crumble.

Now, if I create the schema template for the user, I can also give them access to the schema public, which is fortunately empty. Unfortunately, this doesn't help either. Now the migration aborts with a stacktrace permission denied for database postgres from which I cannot recognize what exactly the problem is.

=== Starting migration
Operations to perform:
  Apply all migrations: auth, authentik_blueprints, authentik_brands, authentik_core, authentik_crypto, authentik_enterprise, authentik_events, authentik_flows, authentik_outposts, authentik_policies, authentik_policies_dummy, authentik_policies_event_matcher, authentik_policies_expiry, authentik_policies_expression, authentik_policies_password, authentik_policies_reputation, authentik_providers_ldap, authentik_providers_oauth2, authentik_providers_proxy, authentik_providers_rac, authentik_providers_radius, authentik_providers_saml, authentik_providers_scim, authentik_rbac, authentik_sources_ldap, authentik_sources_oauth, authentik_sources_plex, authentik_sources_saml, authentik_sources_scim, authentik_stages_authenticator_duo, authentik_stages_authenticator_sms, authentik_stages_authenticator_static, authentik_stages_authenticator_totp, authentik_stages_authenticator_validate, authentik_stages_authenticator_webauthn, authentik_stages_captcha, authentik_stages_consent, authentik_stages_deny, authentik_stages_dummy, authentik_stages_email, authentik_stages_identification, authentik_stages_invitation, authentik_stages_password, authentik_stages_prompt, authentik_stages_source, authentik_stages_user_delete, authentik_stages_user_login, authentik_stages_user_logout, authentik_stages_user_write, authentik_tenants, contenttypes, guardian, sessions
Running migrations:
  Applying authentik_tenants.0001_initial...
{"domain_url": null, "event": "releasing database lock", "level": "info", "logger": "lifecycle.migrate", "pid": 5, "schema_name": "public", "timestamp": "2024-05-10T09:34:31.288393"}
Traceback (most recent call last):
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django_prometheus/db/common.py", line 69, in execute
    return super().execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.InsufficientPrivilege: permission denied for database postgres

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

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/manage.py", line 37, in <module>
    run_migrations()
  File "/lifecycle/migrate.py", line 113, in run_migrations
    execute_from_command_line(["", "migrate_schemas"])
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django_tenants/management/commands/migrate_schemas.py", line 63, in handle
    executor.run_migrations(tenants=[self.PUBLIC_SCHEMA_NAME])
  File "/ak-root/venv/lib/python3.12/site-packages/django_tenants/migration_executors/standard.py", line 11, in run_migrations
    run_migrations(self.args, self.options, self.codename, self.PUBLIC_SCHEMA_NAME)
  File "/ak-root/venv/lib/python3.12/site-packages/django_tenants/migration_executors/base.py", line 59, in run_migrations
    migrate_command_class(stdout=stdout, stderr=stderr).execute(*args, **options)
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 106, in database_forwards
    self._run_sql(schema_editor, self.sql)
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 133, in _run_sql
    schema_editor.execute(statement, params=None)
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/ak-root/venv/lib/python3.12/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/django_prometheus/db/common.py", line 69, in execute
    return super().execute(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/ak-root/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: permission denied for database postgres

All in all, the migration of authentik is disastrous, we have decided to roll back and wait for the next versions, in which it is described in detail which additional authorizations the database user needs.

[rissson]

We've been explicit about this in the 2024.2 release notes https://docs.goauthentik.io/docs/releases/2024.2#manual-action-may-be-required

authentik now uses PostgreSQL schemas other than public.

If you have a custom PostgreSQL deployment, please ensure that the authentik user is allowed to create schemas. Usually, if the authentik user is owner of the database, it already can.

As for your last error message, it is pretty clear that the user you're running authentik with does not have sufficient permissions to either connect to, or create schemas in the postgres database, which really should be left alone for postgres' stuff.

[rissson]

Also, #8760

[volker-raschek]

Hi @rissson,
we have created a dedicated database for authentik and get the application running again after a migration. I think the issue can be closed.

Thanks a lot for the link to the documentation.