Attempt to delete user via /api/v1/security/users/{pk} fails for "update or delete on table "ab_user" violates foreign key constraint"

[Yuval-Moshe]

Bug description

When trying to delete a user via the FAB Security API:
curl -s -X DELETE -H "Authorization: Bearer <auth_token>" -H "CSRFToken: <csrf-token> " http://localhost:8088/api/v1/security/users/5

receives the following response:

Response status code: 422

{
  "message": "update or delete on table \"ab_user\" violates foreign key constraint \"dbs_created_by_fk_fkey\" on table \"dbs\"\nDETAIL:  Key (id)=(5) is still referenced from table \"dbs\".\n"
}

The user is an admin user, and as a fix attempt, I've tried to deactivate it before deleting it, but it yields the same result.
User Id was verified as 5 via the GET request call.

Superset logs do no indicate in errors, just the 422 response code:
127.0.0.1 - - [07/Jul/2024:20:35:33 +0000] "DELETE /api/v1/security/users/5 HTTP/1.1" 422 198 "-" "curl/7.88.1"

Any Idea if it's indeed a bug or if there is anything I'm doing wrong? If it's indeed a bug, is there a way to work around it meanwhile

Thanks!

How to reproduce the bug

1. Open CLI
2. Obtain access token and csrf token
3. Check user id via GET to http://localhost:8088/api/v1/security/users/
4. run: curl -s -X DELETE -H "Authorization: Bearer <auth_token>" -H "CSRFToken: " http://localhost:8088/api/v1/security/users/5 receives the following response:
   Response status code: 422
   {
   "message": "update or delete on table "ab_user" violates foreign key constraint "dbs_created_by_fk_fkey" on table "dbs"\nDETAIL: Key (id)=(5) is still referenced from table "dbs".\n"
   }

Screenshots/recordings

No response

Superset version

4.0.1

Python version

3.9

Node version

16

Browser

Chrome

Additional context

No response

Checklist

• I have searched Superset docs and Slack and didn't find a solution to my problem.
• I have searched the GitHub issue tracker and didn't find a similar bug report.
• I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

[dosubot]

To delete a user via the FAB Security API without violating foreign key constraints, you need to ensure that the foreign key constraints are set to cascade on delete. This can be done by setting the on_delete property to CASCADE for the relevant foreign keys in your database schema.

Here is an example of how to set the on_delete property to CASCADE in a migration script:

def upgrade():
    for foreign_key in foreign_keys:
        redefine(foreign_key, on_delete="CASCADE")

This script sets the on_delete property to CASCADE for the foreign keys, which means that when a user is deleted, all related records in the referencing tables will also be deleted automatically.

To apply this change, you need to run the migration script. Once the migration is applied, you should be able to delete a user without encountering foreign key constraint violations.

If the migration has already been applied and you are still encountering issues, ensure that the foreign keys in your database are indeed set to cascade on delete. You can verify this by checking the foreign key constraints in your database schema ^[1].

Additionally, you can use the search_columns attribute in the DatabaseRestApi class to identify dependencies or references to a specific user in the database. The search_columns include created_by and changed_by, which can be used to search for databases created or modified by a specific user ^[2].

To continue talking to Dosu, mention @dosu.