"Duplicate key value violates unique constraint" when adding new user via Django Admin

[ofseaandstars]

What happened

When trying to add a new user to the CERTEGO_SAAS_USER section within the Django admin interface, the server responds with an error stating that the user_id for the user being added already exists within the database, when it definitely does not. This means adding new users is impossible.

Environment

1. OS: Ubuntu 24.04
2. IntelOwl version: 6.3.1
3. Docker version: 28.0.2, build 0442a73

What did you expect to happen

The user should be added as expected with no issues.

How to reproduce your issue

1. Clone the latest release from GitHub as per the installation documentation.
2. Once up and running, create the superuser account using the provided one-liner.
3. Visit the IntelOwl page in the browser and sign-in with the superuser account.
4. Create an organization with your preferred name.
5. Then go to the Django Admin Interface and click 'Add' next to Users under CERTEGO_SAAS_USER.
6. Fill in the information for name, username, password, organization. Click 'Save'.
7. You will now be greeted with either a 500 Server error page or a detailed description of the error message when using the Debug=True environment variable.

Notes

This initially affected our production environment after an issue with upgrading our postgres database based on the instructions provided in the documentation - once the import was complete, some of the migrations that happen to the database were causing errors. As such, we opted to create a new database and experienced this particular problem.

To test, we then cloned a fresh copy of the repo, without making any custom changes outside of setting up a fresh volume for postgres and changing the port number that the UI was accessible from. The same issue occurred, even on a fresh deployment.

Repeated attempts to submit the user will increment the user_id value, but the database table will remain the same. This leads me to believe there may be an issue with order of operations when adding a user to the database.

This is very much a hypothesis, but I am wondering if perhaps the user_id is being incremented before the user data has actually been saved to the database, causing the database to think the value exists when processing the query and therefore aborts writing it to the table as expected.

Error messages and logs

2025-03-25 11:08:11.843 UTC [614] ERROR:  duplicate key value violates unique constraint "authentication_userprofile_user_id_key"
intelowl_postgres               | 2025-03-25 11:08:11.843 UTC [614] DETAIL:  Key (user_id)=(8) already exists.
intelowl_postgres               | 2025-03-25 11:08:11.843 UTC [614] STATEMENT:  INSERT INTO "authentication_userprofile" ("user_id", "company_name", "company_role", "twitter_handle", "discover_from", "task_priority", "is_robot") VALUES (8, 'My Org', 'My Job Role', '', 'other', 10, false) RETURNING "authentication_userprofile"."id"

IntegrityError at /admin/certego_saas_user/user/add/
duplicate key value violates unique constraint "authentication_userprofile_user_id_key"
DETAIL:  Key (user_id)=(8) already exists.
Request Method:	POST
Request URL:	http://localhost:8080/admin/certego_saas_user/user/add/
Django Version:	4.2.17
Exception Type:	IntegrityError
Exception Value:	
duplicate key value violates unique constraint "authentication_userprofile_user_id_key"
DETAIL:  Key (user_id)=(8) already exists.
Exception Location:	/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py, line 89, in _execute
Raised during:	django.contrib.auth.admin.add_view
Python Executable:	/usr/local/bin/uwsgi
Python Version:	3.11.7
Python Path:	
['/opt/deploy/intel_owl',
 '.',
 '',
 '/opt/deploy/intel_owl',
 '/usr/local/lib/python311.zip',
 '/usr/local/lib/python3.11',
 '/usr/local/lib/python3.11/lib-dynload',
 '/usr/local/lib/python3.11/site-packages']
Server time:	Tue, 25 Mar 2025 10:46:45 +0000

Database Entries

authentication_userprofile Table

intel_owl_db2=# SELECT * FROM authentication_userprofile;
 id | company_name | company_role | twitter_handle | discover_from | user_id | is_robot | task_priority
----+--------------+--------------+----------------+---------------+---------+----------+---------------
  3 |              |              |                | other         |       3 | t        |             7
  4 |              |              |                | other         |       4 | t        |             7
  5 |              |              |                | other         |       5 | t        |             7
  6 |              |              |                | other         |       6 | t        |             7
  7 |              |              |                | other         |       7 | f        |            10
(5 rows)

certego_saas_saas_user Table

intel_owl_db2=# SELECT * FROM certego_saas_user_user;
 id |                                         password                                         |          last_login           | is_superuser |             username             | first_name | last_name |         email          | is_staff | is_active |          date_joined          | approved
----+------------------------------------------------------------------------------------------+-------------------------------+--------------+----------------------------------+------------+-----------+------------------------+----------+-----------+-------------------------------+----------
  3 |                                                                                          |                               | f            | ThreatfoxIngestor                |            |           |                        | f        | t         | 2025-03-25 11:01:24.537524+00 |
  4 |                                                                                          |                               | f            | MalwarebazaarIngestor            |            |           |                        | f        | t         | 2025-03-25 11:01:24.632413+00 |
  5 |                                                                                          |                               | f            | VirusTotal_Example_QueryIngestor |            |           |                        | f        | t         | 2025-03-25 11:01:24.994617+00 |
  6 |                                                                                          |                               | f            | GreedyBearIngestor               |            |           |                        | f        | t         | 2025-03-25 11:01:25.281209+00 |
  7 | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX                                                | 2025-03-25 11:07:16.849793+00 | t            | superuser                        | XXXXX      | XXX       | XXXXX@XXXXXXXXXXXXXXXX | t        | t         | 2025-03-25 11:06:57.852047+00 |
(5 rows)

[fgibertoni]

Hello, thanks for opening this issue. Can you please provide the command that you ran to start the project ? Also, you were in master branch ?

[ofseaandstars]

Hello, thanks for opening this issue. Can you please provide the command that you ran to start the project ? Also, you were in master branch ?

Yes, I ran the standard ./start prod up command and pulled from the master branch using git clone - no specific version was checked out.

[fgibertoni]

I was able to reproduce your issue. Can you please try also in develop branch with ./start test up ?

[Adarshgupta1127]

i want to work on this issue can you please assign thisissue to me?

[ofseaandstars]

I was able to reproduce your issue. Can you please try also in develop branch with ./start test up ?

Apologies for the delay in reply, but yes, this does appear to happen in the develop branch with the test build as well.

[anshikasharmaa1517]

Kindly assign this issue to me.

[ofseaandstars]

Has anyone had a chance to look at this yet? Given it means there is no way to manually add users, it makes things difficult.

[github-actions[bot]]

This issue has been marked as stale because it has had no activity for 10 days. If you are still working on this, please provide some updates.

[drosetti]

Hi @ofseaandstars, I followed you steps and works. The only thing I did:

1 - ./start test down -- -v

I set the project down because I was running IO, the -- -v is used to prune the volumes. You can do the same with docker volume prune in case the project is not running BE CAREFUL! Pruning the volumes load to a data loss

2 ./start test build

I was working on develop, for this reason I had to build again.

I created the user successfullly. Idk if you error could be related to the username chars (even if it's strange) I choose for both first name and last name a and the username automatically generatad is a-a.

[github-actions[bot]]

This issue has been marked as stale because it has had no activity for 10 days. If you are still working on this, please provide some updates.

[ofseaandstars]

Apologies for the delay on this - I have had other things take priority. I will provide an update shortly.

[ofseaandstars]

An update:

I can confirm this with the user John Smith which auto-generated john-smith and the issue still occurred. So it is not the username format I'm providing that is the issue (i.e., special characters).

Steps:

1. Take down the test environment with ./start test down -- -v
2. Pull from the develop branch to get latest version with git pull
3. Build the latest develop branch with ./start test build
4. Run the container with ./start test up -- -d
5. Create the superuser account with docker exec -ti intelowl_uwsgi python3 manage.py createsuperuser
6. Login with superuser account and go to the Django Admin Interface and attempt to create a new user account with name John Smith which auto-fills username with john-smith.
7. Click 'SAVE' and get error message:

IntegrityError at /admin/certego_saas_user/user/add/
duplicate key value violates unique constraint "authentication_userprofile_user_id_key"
DETAIL:  Key (user_id)=(10) already exists.
Request Method:	POST
Request URL:	http://X.X.X.X:8080/admin/certego_saas_user/user/add/
Django Version:	4.2.17
Exception Type:	IntegrityError
Exception Value:	
duplicate key value violates unique constraint "authentication_userprofile_user_id_key"
DETAIL:  Key (user_id)=(10) already exists.
Exception Location:	/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py, line 89, in _execute
Raised during:	django.contrib.auth.admin.add_view
Python Executable:	/usr/local/bin/python
Python Version:	3.11.7
Python Path:	
['/opt/deploy/intel_owl',
 '/opt/deploy/intel_owl',
 '/opt/deploy/intel_owl',
 '/usr/local/lib/python311.zip',
 '/usr/local/lib/python3.11',
 '/usr/local/lib/python3.11/lib-dynload',
 '/usr/local/lib/python3.11/site-packages']

Confirmation I am on the latest develop branch:

root@XXX:/path/to/IntelOwl# git status
On branch develop
Your branch is up to date with 'origin/develop'.

[github-actions[bot]]

This issue has been marked as stale because it has had no activity for 10 days. If you are still working on this, please provide some updates.