https://supabase.com/ logo
Join Discord
Powered by
i did create a policy on the join table.. in the u...
# off-topic
l
i did create a policy on the join table.. in the using textarea i used is_member_of_investorstartup(startup_id, investor_id)
j
Is a_investorstartups the join table? Can you post your policy?
l
yes sorry i forgot to add the a_ prefix in my description.. How can i provide that? i used the GUI to create the policy
j
So from what I can tell this isn’t doing anything. You’re trying to define a policy on a table based on the data in the table with no external data
From what I understand of what you’ve done it’s the equivalent of “give access to my table if table.a = table.a”
l
thanks! i've no clue what i'm doing 😅
j
So start from the top, what associates the person trying to access the data with the data itself
l
the foreign key of the person? For a startup, it will be id in startups and the FK startup_id in a_investors_startup
j
So supabase has auth.user.id
So where in your tables do you reference that?
l
ah thats good point.. I do have it in a_startups as user_id column
j
Perfect
So start with your policy there, is that all setup?
l
i don't think so.. based on my security definer function. i think i may need to join the a_startup and a_investor into it and then i can match the auth.id with a_startup.user_id.. but thats a wild guess.. really appreciate your replies!
Copy code
CREATE OR REPLACE FUNCTION is_member_of_investorstartup(_user_id uuid) RETURNS bool AS $$SELECT

EXISTS (  
  SELECT 1  FROM a_investors_startups om 
  JOIN a_startups s ON om.startup_id = s.id
  WHERE s.user_id = _user_id
j
So I’d say: 1. Define a policy on a_startup that checks if user_id matches auth.uid() 2. Define a policy on _a_inverstorstartups where the Boolean is if the current startup_id equals a list of startup ids the user can see
Don’t complicate things with security definer functions for now
Just put the conditional straight into the body of the policy
So the policy conditions will be something like: 1. 
user_id = auth.uid()
2. 
startup_id = any(select id from a_startups)
No join needed
l
thanks! For
Copy code
2. startup_id = any(select id from a_startups)
How can i provide the startup_id? or rather how does it know what startup_id to use
j
RLS means row level security right?
So the policy is run for every row
This means that you get access to the data in a single row every time the policy is executed
So startup_id comes from that table’s row that the policy is on
If the column name was sid then that’s what you’d write 🙂
Make sense?
l
yeah i does.. but still getting null on the client side 😅
j
Well what are your policies now? Maybe screenshot your table layouts too
Ok now RLS is enabled for all those tables and can you send me the policies for each?
Can I see the contents of the policies sorry?
Oh nvm
I can see from the screenshot
l
😄 thanks man
j
A startups is wrong. Missing 
auth.uid()
not 
uid()
l
i tried that but after saving.. it returns as uid() = user_id
j
Okayyyy
Check
Can you get results from that table?
l
yes! seems like something is returning
j
Ok great!
So a_investors_startups still isn’t working?
l
appreciate it.. how can i get the reverse for investors
this is working for me.. it returns the user_id for a startups' own id
is it possible if i can do an OR clause in policies like
Copy code
(startup_id IN ( SELECT a_startups.id   FROM a_startups)) OR (investor_id IN ( SELECT a_investors.id   FROM a_investors))
j
Yup. I’d create a separate policy for it though. Just to keep it logically together. I believe permissive policies act like an OR anyway
l
very nice! it works as well..
thanks jaitaiwan!
j
No problems 💪
l
is it ok if i can ask 1 more last question 😅
j
Hey @lanbau it’s asynchronous communication man. Just ask away anytime and when I or someone else can we’ll answer
l
thanks @jaitaiwan maybe its easier to continue in this thread as all the context is here.. So i tried to query for a_startups when i'm logged in as an investor.. and i got null.. i finally realised it is due to the RLS on the a_startups table that is preventing me from reading which is good security! But i would like to show some columns from the startup e.g name any idea how i should approach this?
j
You have two accounts? 😹
l
oh crap. i logged in via the mac app 😆
j
Alright let me go back at the screenshots
Actually
Show me the policies again
l
sure
j
I see 3 policies missing
[note pseudocode] 1. a_startups if investor_id in (select * from a_investors) 2. a_investors if startup_id in (select * from a_startups) 3. a_investors_startups if startup_id in (select * from a_startups) 1 & 2 are going to need a bit more thinking
This might be a good case for functions 
investor_has_startups
startups_has_investors
that would make the policies (again pseudocode): 1. a_startups if startup_id in (investor_has_startups(select id from a_investors)) 2. a_investors if investor_id in (startup_has_investors(select id from a_startups))
You'd need policy #3 in place before you could iterate on the functions
l
thanks. how does the functions look like? e.g using the 1) a_startups if startup_id in (investor_has_startups(select id from a_investors)) The parameter is an id.. and within the function should it be - select 1 from a_investors_startups where investor_id = _investor_id
j
Have you not written those functions before?
l
i've got a question.. these policies will allow the investor to query for the startup details but what if there are private columns that needs to be hidden from the investor
j
Separate table for columns that need to be private. There's no column level security
l
thanks. is there a way to restrict access to this table based on custom roles. For instance, only the startup and admin can access this row
j
Sure there's a number of ways to implement this
Talk more through what you eventually hope to achieve
l
thanks! maybe i should explain my usecase - an investor have 2 tables - public and private - a startup have 2 tables - public and private Viewing - an investor can see all startups' public profile - an investor can only see its private profile - a startup can see all investors' public profile - a startup can only see its private profile Interaction - an investor can invite a startup - an investor can see which startups it has invited - a startup can see which investors made an invitation - a startup can accept the invitation An admin - maybe not so important as there is a service key to bypass the RLS... i might just use server side to make admin changes.
j
The admin part might be easier, but I wouldn't split up permissions into both code + db
Number of ways you could create an admin... I assume you're not having multi-org?
l
no.. no multi org.. i've got a question.. is it ok to put user_id uuid in both private and public tables?
j
For sure
That can really simplify your policies
l
yeah also i was thinking of just using the uuid for the join table as well..
j
You could also implement something like https://github.com/supabase-community/supabase-custom-claims to allow some users to have super user access with the custom admin claim
Then you could write a permissive policy that allows all actions
l
coool
j
Have fun!
l
thanks @jaitaiwan i think i managed to achieve what i wanted.. i'm not sure if my approach is secure.. could you kindly have a look at my policies please if it makes sense
j
What you've done doesn't make sense to me
I'd need to see the table structure
l
ok
j
Oh gotcha
seems ok to me
l
🙏 thanks!
hi @jaitaiwan based on the above tables.. if i login as a user, what is the best practice to check if the user is in the investors or startups table? My current approach is to make 2 selects from both tables and if there is a result, set the role for the user..
j
Will get back to you in about 16 hours. 1am and busy in the morning 😀
l
hey no worries appreciate it 😀
tried the above but pretty sure there is a better way to get or set the user role 😅
5 Views
PreviousNext
Powered by