RLS Policy With Lookup to Permissions-by-Uid+Id Table?

[scoover]

In my application, a user logs game stats by updating records in a points table. The points records include a team_id.

I'd like a user_team table to resolve permissions for a user (who is already in the auth table) to edit point-records based on the team_id. (The idea is to manage access for one user to multiple teams by creating several linking records in the user_team table.)

points: many game-tracking columns + id (unique key) and team_id
user_team: user_id (uuid matching auth table), team_id and email (matching auth table)

TLDR; does the user with auth.uid() have permission to UPDATE a point with team_id based on user_team?

I can't seem to create an RLS policy to enforce this paradigm.
Currently, I'm trying USING expression true...
and WITH CHECK expression set to:

(auth.uid() IN (
  SELECT user_team.user_id
  FROM user_team
  WHERE (user_team.team_id = points.team_id)
))

The intent is that the points->team_id value in the app's UPDATE request would fill points.team_id in the policy check's WHERE clause. I can't find much help content on policies built on outside tables (like user_team… rather than the table being updated) and driven by non primary keys (e.g. points.team_id instead of points.id).

Are there tricks for debugging RLS policies? When the permissions don't work, the process is a black box.

Thanks for your help.

P.S. Functionally, this request is the same as this StackOverflow query, but I haven't been able to turn the responses into a functional solution.

P.P.S. For extra credit, I'd love to use user_team.email values (presumably through auth.jwt()) instead of user_id but the former feels like a more direct and literal first test.

[scoover]

I also just tried this which seems intuitive:

(team_id IN ( SELECT user_team.team_id
   FROM user_team
  WHERE (auth.uid() = user_team.user_id)))

But it fails with this error:

{
    "code": "42501",
    "details": null,
    "hint": null,
    "message": "new row violates row-level security policy for table \"points\""
}

Is it possible that a policy based on a non-primary key (i.e. points.team_id) doesn't work reliably if that key is not part of the update/patch request?

[GaryAustin1]

Just glancing at this. What is your select policy? That has to be met also for update.
Not really clear on whether team_id exists in points before the update. But if you have that as part of select policy then in must.
If this is the case you may need to use an rpc call to a security definer function to do the update with any security in the function itself.

[scoover]

Thanks for peeking at this, @GaryAustin1.

For now, my SELECT policy is set to true and you can see my UPDATE policy in the screenshot.

(Not that I have renamed team_id in user_team to simply team to clarify the reference versus points.team_id.)

I wish there were a way to see what was being fed into and coming out of these policy queries.

Any ideas?

[GaryAustin1]

Your policy looks very similar to this:
https://supabase.com/docs/guides/database/postgres/row-level-security#minimize-joins
Do you have select access to user_team? I don't see you mention it.

[scoover]

Yep... That was the issue, @GaryAustin1! ❤️
RLS wasn't allowing SELECT access to the user_team table.

I spent too many hours on this.
Thanks so much for your help.