Unable to delete `auth.users` row due to `objects_owner_fkey` FK constraint

[liaujianjie]

Bug report

Describe the bug

When a user uploads an object to a bucket, the object's row in storage.objects has a column owner that has a FK constraint objects_owner_fkey to auth.users.id. However, it's not set up with on delete {cascade|set null}—which prevents the user from actually being deleted.

Attempting to delete a user with a storage.object referencing the user results in a FK constraint violation.

To Reproduce

1. Create a user
2. Authenticate as that user on the client
3. Upload an object as that user on the client
4. Delete that user via dashboard
5. You'll get a FK constraint violation error:

   Deleting user failed: update or delete on table "users" violates foreign key constraint "objects_owner_fkey" on table "objects"

Expected behavior

Should be able to delete user whilst retaining the object in the database.

Suggested fix & temporary workaround

Add on delete set null to the objects_owner_fkey constraint:

alter table storage.objects
drop constraint objects_owner_fkey,
add constraint objects_owner_fkey
   foreign key (owner)
   references auth.users(id)
   on delete set null;

[liaujianjie]

Also as a side question, is it a good idea or good practice to run our own migrations on the auth and storage schemas?

[k0shk0sh]

I believe the idea here is that they don't want to delete the row because then the file will forever be stored without any reference. Until they have a proper way of handling deleting files as well on user deleted I don't think this will come anytime soon.

[kiwicopple]

good practice to run our own migrations on the auth and storage schemas

No - it would not be a good idea to modify the auth or storage schema. If you need to extend the functionality it would be best to add your own schemas. (and you can create views if you want to "join" schemas)

[liaujianjie]

I believe the idea here is that they don't want to delete the row because then the file will forever be stored without any reference. Until they have a proper way of handling deleting files as well on user deleted I don't think this will come anytime soon.

If we use set null instead of cascade, the database will still maintain a reference to the underlying S3 object though. Would that be sufficient?

[liaujianjie]

good practice to run our own migrations on the auth and storage schemas

No - it would not be a good idea to modify the auth or storage schema. If you need to extend the functionality it would be best to add your own schemas. (and you can create views if you want to "join" schemas)

Then, what would be the recommended practice here? I can't delete all objects uploaded by the user to storage before deleting the user because some of the objects still have to be retained after a user deletion.

For example, user Z uploads an avatar image for his team and later deletes his account. In this scenario, we would expect that deleting user Z wouldn't also delete the avatar image he uploaded.

[inian]

Thats a good point Jian. I will add a trigger to make the owner as null in storage.objects when a row is deleted from auth.users.

[mcewen87]

Hi @inian

As I understand, the solution to this is to add this SQL

drop constraint objects_owner_fkey,
add constraint objects_owner_fkey
   foreign key (owner)
   references auth.users(id)
   on delete set null;

Is that correct?

Additionally, can you share some insight into what is going on behind the scenes to create this restriction, and why it is in place?

[inian]

I haven't tested it out on an actual project but that does seem like it would work @mcewen87. The constraint is in place so that each storage owner is linked to an auth user and cos owner field shouldn't be any UUID.

[mcewen87]

I haven't tested it out on an actual project but that does seem like it would work @mcewen87. The constraint is in place so that each storage owner is linked to an auth user and cos owner field shouldn't be any UUID.

Thank you! I'll give it a try.

[theodufort]

On my side, I had a "users" table with constraint users_id_fkey between my stripe table and "auth.users" table.

I simply added :
alter table users drop constraint users_id_fkey, add constraint users_id_fkey foreign key (id) references auth.users(id) on delete cascade;

[fenos]

Feel free to re-open when providing more info