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

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

Create a user
Authenticate as that user on the client
Upload an object as that user on the client
Delete that user via dashboard
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;

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 28
Comments: 19 (5 by maintainers)

Most upvoted comments

workaround:
Before deleting a user, delete all files using the supabase client.

const deleteAllUsers = async () => {
  const {
    data: { users },
  } = await client.auth.admin.listUsers();
  for (let user of users) {
    const directory = `users/${user.id}`;
    const fileRes = await client.storage.from("images").list(directory);
    for (let file of fileRes?.data ?? []) {
      console.log({ file });
      await client.storage.from("images").remove([`${directory}/${file.name}`]);
    }

    const { error } = await client.auth.admin.deleteUser(user.id);
    if (error) {
      console.log(`client.auth.admin.deleteUser(${user.id}) failed.`, {
        error,
      });
      throw new Error(error.message);
    }
  }
};

convcha on Apr 17, 2023

In my case, I want to delete all objects(photos uploaded by a user) when the user is deleted. So I fixed storage.objects table’s owner column Cascade.

Now, user and objects can be deleted without error.

kazuooooo on Sep 11, 2023

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?

mcewen87 on Aug 17, 2021

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.

k0shk0sh on Aug 11, 2021

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.

inian on Aug 12, 2021