supabase row level security for append only table

Question

What I'm trying to do is to have a table that assigns a subscription to a user. All the samples I found so far do this via:

(auth.uid() = user_id)

However I would like to keep track of reassignments. With a table structured like this:

// assigments //

user_id | subscription_id | comment    | created_at
---------------------------------------------------
3       | 1001            | re-assign  | 2023-06-06
---------------------------------------------------
2       | 1002            | intial     | 2023-06-05
---------------------------------------------------
1       | 1001            | intial     | 2023-06-05
---------------------------------------------------

I'm having a difficult time wrapping my head around writing policy for this. I think it needs to be something like

auth.uid() IN (SELECT DISTINCT ON (subscription_id) user_id FROM assignments ORDER BY created_at)

Is this the right track? auth.uid() IN (...) feels super inefficient. Is it possible to first see if there is a row that matches user_id == auth.uid() and to a subquery on the resulting subscription_id with a ORDER BY created_at, LIMIT 1 and somehow use the result for the auth.uid() comparison?

Does a user and the subscription have one to one relationship, or one to many? — dshukertjr
– dshukertjr, Commented Jun 7, 2023 at 7:58

dshukertjr · Accepted Answer · 2023-06-07 08:43:37Z

0

If the user and "active" subscription have one to one relationship, then you should be able to do something like this

auth.uid() = (SELECT a.user_id
  FROM assignments a
  WHERE a.subscription_id = assignments.subscription_id
  ORDER BY a.created_at desc limit 1)

Within the a.subscription_id = assignments.subscription_id, a.subscription_id is the subscription id from the sub query, and assignments.subscription_id is the current row that is being evaluated within this RLS policy.

answered Jun 7, 2023 at 8:43

dshukertjr

19.2k11 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

catlan · Accepted Answer · 2023-06-07 12:03:40Z

With the help of @dshukertjr found a solution. Trying to run a sub query on the table for which the policy is written results in infinite recursion detected in policy for relation "assignments" (Error: 42P17). With this I was able to find https://github.com/orgs/supabase/discussions/3328 and https://github.com/orgs/supabase/discussions/1138.

The answer is to use a function that bypasses RLS to do the check.

BEGIN
        RETURN _user_id IN (
            SELECT user_id
            FROM assignments
            WHERE subscription_id = _subscription_id
            ORDER BY created_at DESC
            LIMIT 1
        );
END;

And use the function in the policy like:

is_subscription_owner(auth.uid(), subscription_id)

Collectives™ on Stack Overflow

supabase row level security for append only table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related