Question 185069 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – supabase row level security for append only table

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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)

  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search