https://supabase.com/ logo
Join Discord
Powered by
I'm trying to create a Policy on my `payment_metho...
# sql
m
I'm trying to create a Policy on my 
payment_method
table with the following SQL
Copy code
sql
SELECT 
  EXISTS(
    SELECT 
      * 
    FROM 
      payment_method 
      INNER JOIN account ON account.id = payment_method.account 
    WHERE 
      account.account_owner = auth.uid()
  );
☝️ this works fine in the sequel editor, but when I try to create a policy with it — I get back 
Error adding policy: syntax error at or near "SELECT"
✅ Realized I just needed to drop the top 
SELECT
and only provide the 
EXISTS(<SQL>)
statement
weird now that I have the policy created, when I try the following
Copy code
ts
export const fetchPaymentMethodsInfoServerSide = async (
  ctx: GetServerSidePropsContext,
  accountId: number
) => {
  try {
    const { data: paymentMethodInfo, error } = await supabaseServerClient(ctx)
      .from<definitions['payment_method']>('payment_method')
      .select('*')
      .eq('account', accountId)

    if (error) {
      console.error('whoops', error)
      return Promise.reject(error)
    }

    return paymentMethodInfo
  } catch (error) {
    console.log(
      'todo: hook up sentry for fetchPaymentMethodsInfoServerSide',
      error
    )
  }
}
I'm seeing the following error:
Copy code
infinite recursion detected in policy for relation "payment_method"
@burggraf any thoughts on to improve that sql or should I create a function and reference that in my policy?
✅ yeah looks like the function route was the thing to do here
Copy code
sql
CREATE FUNCTION can_user_fetch_payment_methods(_user_id uuid) RETURNS bool AS $$
SELECT 
  EXISTS(
    SELECT 
      1 
    FROM 
      payment_method 
      INNER JOIN account ON account.id = payment_method.account 
    WHERE 
      account.account_owner = _user_id
  );
$$ LANGUAGE sql SECURITY DEFINER;
and now in my policy I just check
Copy code
can_user_fetch_payment_methods(uid())
b
Glad you got it sorted out, but just be careful with RLS policies like this -- they can slow things down to a crawl if you try to fetch a lot of records in the table where they're defined, because the policy has to run for every row. Say this policy is a SELECT policy and that policy has 1000 rows and you select all rows.... that policy (and this function) needs to run 1000 times.
That may be fine for your use case, but think it through in terms of how often you'll need to access data in this table and at what volume. You may be fine, but this has bitten me in the past when I didn't think through the scaling ramifications. 😀
m
Good call out
I guess the easier way of handling this is to store the owner id (user uuid) as a column directly in the payments table
PreviousNext
Powered by