"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.