The issue is missing RLS policy for select, the delete operation could only delete the rows visible through select. see the docs
select
delete