I've got another one....first...is it necessary to...
# helpt
tandyman
03/27/2022, 5:08 AMI've got another one....first...is it necessary to enable RLS if a table is a child to a parent which is RLS restricted properly? If that isn't good enough, and I have to also enable RLS for the child table as well, is there a rule where I can reference a column from that parent table...such as an id to compare against the uid? I'm trying to avoid littering potentially 20 tables, with the user id on every single row of every table...
n
Needle
03/27/2022, 5:08 AMHello @tandyman!
This thread has been automatically created from your message in #843999948717555735 a ``few seconds ago``.
Pinging @User so that they see this as well!
Want to unsubscribe from this thread?
Right-click the thread in Discord (or use the ... menu) and select Leave Thread to unsubscribe from future updates.
Want to change the title?
Use the
/titlet
tandyman
03/27/2022, 5:08 AMI'm hoping the answer is it's safe to leave RLS off for the child table, provided it's never accessed any other way but nested.
tandyman
03/27/2022, 5:10 AMWhen I refer to parent-child, I mean one to many.
a
Avin
03/27/2022, 6:23 AMIf a parent row is not accessible, I don't believe the child row has anything to be joined to, so it won't be retrieved.
> is there a rule where I can reference a column from that parent table
Policies support any sub-queries
n
Needle
03/27/2022, 6:23 AMtandyman (2022-03-27)
a
Avin
03/27/2022, 6:24 AMCheck examples here.
https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Avin
03/27/2022, 6:26 AM>I'm hoping the answer is it's safe to leave RLS off for the child table
If is not safe. A malicious actor can do a direct select on any public table using your anon public key. You should use RLS on every table that is not public.
t
tandyman
03/27/2022, 8:07 PMOK, I read through all of that, gives some good background. Thank you for that.
tandyman
03/27/2022, 8:08 PMThe next step, is I tried to take my time and figure out how to write a policy, that would check from the parent table...so, it appears to work after a quick test.
tandyman
03/27/2022, 8:08 PMHere is what I wrote that seems to work:
tandyman
03/27/2022, 8:08 PM Copy code
(( SELECT client_profiles.agent_id
FROM client_profiles
WHERE (clients.client_profile_id = client_profiles.id)) = uid())tandyman
03/27/2022, 8:10 PM^ This policy is on the child table. It has a foreign key called
client_profiles_idauth.uid()uid()tandyman
03/27/2022, 8:11 PM---
tandyman
03/27/2022, 8:12 PMDoing it this way avoids me having to add the agent_id to this child table. I may have as many as 10 child tables. I'm doing it this way so RLS can be turned all for all of them, but they can all look to the agent from the parent table to determine access.
tandyman
03/27/2022, 8:13 PMI have no idea about scaling yet and what that will look like, but I figure it's a better optimization to do it this way, rather than sprinkle around that agent_id everywhere?
tandyman
03/27/2022, 8:14 PMThis seems like it would scale better. I'm open to (and welcome!) better suggestion(s).
tandyman
03/27/2022, 8:15 PM@User thanks for your help on that other question I had awhile ago with the is_admin() thing. It helped me write this policy here.