CODEWITHSUNDEEP
databasepostgresqlsupabase
PhyDev
PhyDev

Reputation: 73

Limiting insertion into Supabase table with same values using RLS policies

I have a payments table which has a foreign key to a table called bundles, the payments table has the methods is_successful, bundle_id and user_id (the rest are irrelevant). I need to not allow a user to pay for the same bundle twice (or a disabled bundle), this means not allow them to insert into payments table if it already has a row with their auth.uid() and the same bundle_id and is_successful set to true.

I wrote this RLS policy,

  ((( SELECT auth.uid() AS uid) = user_id) AND ((NOT (EXISTS ( SELECT 1
   FROM bundles
  WHERE ((bundles.is_disabled = true) AND (bundles.id = payments.bundle_id))))) AND (NOT (EXISTS ( SELECT 1
   FROM payments p
  WHERE ((p.is_successful = true) AND (auth.uid() = p.user_id) AND (p.bundle_id = payments.bundle_id)))))))

(This is inside the WITH CHECK). The part for the disabled bundle works, the part of paying twice doesn't, what am I doing wrong?

Upvotes: 0

Views: 61

Answers (1)

PhyDev
PhyDev

Reputation: 73

After a very long time of trial and error I figured out that the solution was to define a security definer function and use it inside the RLS policy.

Upvotes: 0

Related Questions