takahe

takahe copied to clipboard

Trying to deploy Takahe with dockerized database, I got PostgreSQL 15 by default. There appears to be an unresolved incompatibility with Django and PG15 due to the latter's revocation of create privileges on the public schema: first of many errors shown:

Operations to perform:
  Apply all migrations: activities, admin, api, auth, contenttypes, core, sessions, stator, users
Running migrations:
Traceback (most recent call last):
  File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.InsufficientPrivilege: permission denied for schema public
LINE 1: CREATE TABLE "django_migrations" ("id" bigint NOT NULL PRIMA...
                     ^

The workaround for this error is to grant the deprecated privilege back to the database user on the correct database (PGNAME):

postgres=# \c $PGNAME
takahe=# grant create on schema public to public

However, this rolls back a protection against CVE-2018-1058 so it's not great.

The better solution would be to modify the Django database connector configuration as well as to instruct the database to be created with a schema specific to PGUSER. The capabilities this requires have been in PostgreSQL since v7, this is just taking into account the changed defaults. Some instructions for this which I found while debugging the issue:

https://gist.github.com/axelbdt/74898d80ceee51b69a16b575345e8457#configuring-postgresql-usage

Yeah, this is probably ultimate a Django/PG bug we happen to be in the middle of. I'm not entirely sure we can do anything about it, other than change some default settings files or add some extra docs.

I know basically nothing at all about Django, but the gist I linked appears to propose that modifying the search path should work. I tried to do the same by changing the postgresql role's defaults: alter role takahe set search_path = takahe, but that apparently wasn't sufficient for the schemaless create table statements to work. I ran out of energy to investigate and just went with granting the (insecure) privilege..

        'OPTIONS': {
                'options': '-c search_path=myschema'
            },

Unfortunately we can't just add that to our default settings as it will break all existing installs. Sigh.

I guess we need to figure out some way to extract a schema name from the database URI and then tell people to either set it or change the permissions if they're on PostgreSQL 15 or higher.

search path can include multiple schemas, ie search_path=takahe,public, which should take care of existing installs. However for some reason I can't figure out, Django insists on trying to create the tables in public schema even when the search path is modified. That certainly looks like a Django-side issue.

Django does not support database schemas. The search path option is getting passed into the database driver, but doesn't actually change anything in django when it has to explicitly provide a schema name (such as when creating tables). This is tracked in https://code.djangoproject.com/ticket/6148 which has had various implementations over the last 15 years, but no consensus and no merges.

The "proper" fix for this for now is to temporarily alter permissions during migrations. The quick fix is for the dockerfile to just alter permissions as part of the database container init.