I’m currently working on a Django app that has been maintained for 4+ years at this point. I joined the team not too long ago. I was getting complaints about group permission issues, and after a lot of inspection, I found out that there were many duplicate permissions.
id | name | content_type_id |codename
----+----------------+-----------------+--------
1 can add x 1 add_x
2 can add y 2 add_y
3 can add y 1 add_y
I see this kind of behavior in the table where the name/codename is a duplicate of some other valid entry, but then the content_type_id arbitrarily uses some other content_type_id that doesn’t relate to it at all. It doesn’t happen to every permission, but there are a quite a few in which it is happening. This is a pattern I have observed if the database that this app is connected to has been continually migrated/upgraded over years, then the auth_permission table seems to have this issue of duplicate permissions with random content_type_id. However, when I run the app on my local with the latest version and run the migrations on a fresh db, I get no such issues.
I suspect it has to do something with migrations. I’ve noticed that the auth_permission table does not look clean. I know that relational db’s aren’t going to necessarily be in order but the pk order in this table is all over the place, and the pk’s are not sequential. The pk order goes something like 100->120,
1->99, 121->150. There are some missing pk’s so I am assuming permissions were deleted at some point.
As a note, this issue does not raise any errors with Django or the db. But I believe it is definitely a bug and shouldn’t happen. Also there aren’t any missing perms, just duplicate perms with a non-matching content_type_id.
The closest resemblance to this issue I could find was here. But there are no proxy models in this app. I looked all through version control and was not able to find any instances of proxy models being used. Does anyone have any ideas that could point me in the right direction as to why this happens and how to potentially resolve it? I am mostly interested in the why it happens. I have no one else to ask at the moment.
Permissions are created from the current set of models and content types in a function that’s called after migrations run, create_permissions (source). The same for content types (source).
Given you have permissions that seem to relate different content type ID’s to the same model names, I suspect multiple database dumps have been loaded into your database. This could have been early in development where migrate was run, the production database was assumed to be completely empty, and all the data from a staging/local database was loaded into it.
You could clean this up by iterating over all the permissions, and delete the unused ones. Then check they’re unique per name, and merge the duplicates. When you next run python manage.py migrate they’ll be synchronized for all existing models by create_permissions.
@adamchainz Hi. The solution you gave helps me a lot because the number of duplicates is small enough that iterating through the table manually and deleting the invalid entries wouldn’t take too much time. So thanks for that.
As for your theory to the why this happened, it sounds very possible. I tried following the upgrade path on my local machine, starting from the earliest version, doing the migrations on my local db, then upgrading, then migrating, rinse and repeat until I was at the current version where I am seeing these issues in production. Unfortunately, I was unable to recreate the issue.
One thing that I just discovered that is quite troublesome is that there are multiple production db’s in which this duplicate perms issue seems to persist. The affected db’s seem to duplicate the same content_types as well.
Maybe the older database’s used the same dump to initialize for testing? Not 100% sure on that one. If that’s not the case, I have no clue why this happened. Maybe something with incremental migrations over time? I do know that some Models were deleted as versions progressed. Otherwise I do not know. Do you have any thoughts?
@adamchainz I just wanted to reply and thank you. Looking through the multiple prod db’s, I was able to find a pattern that they all have in common with duplicate permissions. And this pattern can really only be explained by your multiple db dump theory. So I believe that the mystery to the ‘why’ this happened is solved. Thank you again.