https://supabase.com/ logo
#off-topic
Title
# off-topic
i

irrationaljared

03/02/2022, 8:13 PM
I'm trying to implement a RLS policy using the following "Using expression": >
Copy code
auth.uid() in (
>   select   profile."userId"
>   from     profile, "_profileToteam", project
>   where    profile.id = "_profileToteam"."A"
>     and    "_profileToteam"."B" = project."teamId"
>     and    project.id = id
> )
but I'm getting the error: > Error adding policy: column reference "id" is ambiguous My understanding from the documentation here: https://supabase.com/docs/guides/auth/row-level-security that the "id" would be the project id. Why is it "ambiguous" or is referring to something else?
g

garyaustin

03/02/2022, 8:38 PM
Can you clarify table name this policy is on. It is clear you have a profile table you are doing the where on, but then you refer to a project table (project.id) and then I assume id itself is supposed to be in the table the policy is on.
i

irrationaljared

03/02/2022, 8:40 PM
yes, the policy is on the project table
Is it possible that the policy should look like:
Copy code
auth.uid() in (
  select   profile."userId"
  from     profile, "_profileToteam"
  where    profile.id = "_profileToteam"."A"
    and    "_profileToteam"."B" = teamId
)
where teamId is coming from the project table? When I do that I get the errorr: > Error adding policy: column "teamid" does not exist Is that because all table column names need to be lowercase?
s

Scott P

03/02/2022, 8:47 PM
It's ambiguous because multiple tables have an
id
column, and it doesn't know which table you're referring to. In your first code sample, it's not obvious what the final
= id
is referencing. In general, using camelCasing in table names opens up a big can of worms, and underscore_case is generally preferable.
i

irrationaljared

03/02/2022, 8:54 PM
Are you sure about camelCasing? The only table name that is camelCased was generated by Prisma (a database ORM)
s

silentworks

03/02/2022, 9:00 PM
Prisma generates table names in a weird way, its based on the model name rather than following the DB engine's best/recommended practices
g

garyaustin

03/02/2022, 9:07 PM
At a minimum teamId would need to be "teamId"
s

silentworks

03/02/2022, 9:20 PM
In your original message the issue is what @User has stated about having more than one table with a
id
column. You also stated that the
id
in reference would be the project id, but why are you checking if
project.id = project.id
?
i

irrationaljared

03/03/2022, 2:17 AM
I was thinking that I only had the "id" of the project to reference, but I needed to reference against the team_id field (I've changed the column names to be all lower case as well) - That seems to have fixed it
Thanks for the help