Migration 0002_alter_testmodel_options fails with ForeignKeyViolation when permissions are assigned to users/groups

[kumarnmanoj]

Description

Migration health_check/db/migrations/0002_alter_testmodel_options.py (introduced in PR #451, fixing #443) fails with a ForeignKeyViolation when any user or group has been assigned one of the default TestModel permissions (add_testmodel, change_testmodel, delete_testmodel, view_testmodel).

The remove_test_model_default_permissions data migration deletes permissions directly from auth_permission, but does not first clear references in auth_user_user_permissions or auth_group_permissions, which have foreign keys to auth_permission.

Error

psycopg.errors.ForeignKeyViolation: update or delete on table "auth_permission" violates foreign key constraint "auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm" on table "auth_user_user_permissions"
DETAIL:  Key (id)=(105) is still referenced from table "auth_user_user_permissions".

Full traceback:

[standard:public] Running migrations:
[standard:public]   Applying db.0002_alter_testmodel_options...
Traceback (most recent call last):
  ...
  File ".../django/db/migrations/executor.py", line 249, in apply_migration
    with self.connection.schema_editor(
  ...
django.db.utils.IntegrityError: update or delete on table "auth_permission" violates foreign key constraint
"auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm" on table "auth_user_user_permissions"
DETAIL:  Key (id)=(105) is still referenced from table "auth_user_user_permissions".

Root Cause

In 0002_alter_testmodel_options.py, the data migration function does:

def remove_test_model_default_permissions(apps, schema_editor):
    ContentType = apps.get_model("contenttypes", "ContentType")
    Permission = apps.get_model("auth", "Permission")
    db_alias = schema_editor.connection.alias

    try:
        test_model_content_type = ContentType.objects.using(db_alias).get(app_label="db", model="testmodel")
        Permission.objects.using(db_alias).filter(content_type=test_model_content_type).delete()
    except ContentType.DoesNotExist:
        return

This deletes auth_permission rows directly, but the auth_user_user_permissions and auth_group_permissions M2M tables have foreign keys pointing to auth_permission.id. If any user or group was assigned one of these permissions (e.g. via the Django admin), the DELETE violates the FK constraint.

How to Reproduce

1. Install django-health-check with health_check.db in INSTALLED_APPS
2. Run migrations to create the TestModel permissions (migration 0001_initial)
3. Assign any TestModel permission to a user or group:

   from django.contrib.auth.models import Permission
   user.user_permissions.add(Permission.objects.get(codename="view_testmodel"))

4. Upgrade to a version containing migration 0002_alter_testmodel_options
5. Run python manage.py migrate — fails with IntegrityError

Suggested Fix

Clear the M2M references before deleting the permissions:

def remove_test_model_default_permissions(apps, schema_editor):
    ContentType = apps.get_model("contenttypes", "ContentType")
    Permission = apps.get_model("auth", "Permission")
    db_alias = schema_editor.connection.alias

    try:
        test_model_content_type = ContentType.objects.using(db_alias).get(
            app_label="db", model="testmodel"
        )
    except ContentType.DoesNotExist:
        return

    permissions = Permission.objects.using(db_alias).filter(
        content_type=test_model_content_type
    )

    # Clear M2M references before deleting permissions
    UserPermission = apps.get_model("auth", "User_user_permissions")  # or use raw SQL
    GroupPermission = apps.get_model("auth", "Group_permissions")

    # Alternatively, using raw SQL to be safe across custom user models:
    permission_ids = list(permissions.values_list("id", flat=True))
    if permission_ids:
        schema_editor.execute(
            "DELETE FROM auth_user_user_permissions WHERE permission_id IN %s",
            [tuple(permission_ids)],
        )
        schema_editor.execute(
            "DELETE FROM auth_group_permissions WHERE permission_id IN %s",
            [tuple(permission_ids)],
        )

    permissions.delete()

Workaround

Manually clear the references before running the migration:

DELETE FROM auth_user_user_permissions
WHERE permission_id IN (
    SELECT p.id FROM auth_permission p
    JOIN django_content_type ct ON p.content_type_id = ct.id
    WHERE ct.app_label = 'db' AND ct.model = 'testmodel'
);

DELETE FROM auth_group_permissions
WHERE permission_id IN (
    SELECT p.id FROM auth_permission p
    JOIN django_content_type ct ON p.content_type_id = ct.id
    WHERE ct.app_label = 'db' AND ct.model = 'testmodel'
);

Then re-run python manage.py migrate.

Environment

• django-health-check: 3.23.5 (also affects any version containing migration 0002)
• Django: 4.2
• Database: PostgreSQL 13
• Multi-tenant: django-tenants (fails on public schema migration)

[codingjoe]

Hi @kumarnmanoj 👋,

Thank you for reaching out. Please consider upgrading to version 4, which doesn't require migrations anymore.

Since you added the users to a group, I don't think this applies to many users. Considering that you found a workaround for your project, I would refrain from a backport to v3.

I must also urge you to upgrade to Django 5.2. Django 4.2 will end extended security support for Django 4.2 LTS in a month.

Cheers!
Joe

[kumarnmanoj]

Hey @codingjoe

Thank you. We will try the upgrade option.