[Storage RLS] the "name" variable is replaced by another table's "name" column

[ouvreboite]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I'm building a image storage system, and I'm setting up a RLS. A user can only access images of a task if they have created the project containing the tasks.

My tables look like that:

• Projects: id, created_by, ...
• Tasks: id, name, project_id, ...
  My bucket looks like that:
• tasks_images:
  • /taskid1(one folder per task)
    • /file1
    • /file2
  • /taskid2
    • /file3

My storage RLS

create policy "User must be the creator of the project to access a tasks images"
on "storage"."objects"
as permissive
for select
to public
using (
  bucket_id = 'tasks_images'
  AND auth.role() = 'authenticated'
  AND EXISTS (
    SELECT 1
    FROM tasks task JOIN project proj ON proj.id = task.project_id
    WHERE task.id::text = (storage.foldername(name))[1]
    AND proj.created_by = auth.uid()
  )
);

Here, I'm using WHERE task.id::text = (storage.foldername(name))[1] as I'm creating a subfolder for each task.

The problem: when I create this RLS (either by applying a migration or using the studio), it gets rewritten. When I view it (or query directly pg_policies), the condition is changed to:

using (
  bucket_id = 'tasks_images'
  AND auth.role() = 'authenticated'
  AND EXISTS (
    SELECT 1
    FROM tasks task JOIN project proj ON proj.id = task.project_id
    WHERE task.id::text = (storage.foldername(task.name))[1] --🐛 task.name instead of name
    AND proj.created_by = auth.uid()
  )
);

I look like some optimization step is thinking "table Tasks has a 'name' column, so this 'name' variable must be that".

To Reproduce

Expected behavior

• The 'name' variable in storage RLS should not be changed to match an existing table's column
• Is there a more strongly type alias for the 'name' variable ?

Screenshots

System information

• supabase version: 1.165.0 (via npx)

Additional context

[GaryAustin1]

It is reflecting what Postgres will do with your SQL. As it is in a select all non table.column references are assumed to belong to the table being selected. You need to prefix it with objects.name I'm pretty sure.

[dikshantrajput]

@ouvreboite The name column is residing where? It is part of which table. You need to mention that i.e. objects.name