public inbox for [email protected]
help / color / mirror / Atom feedRe: Row level security policy
3+ messages / 2 participants
[nested] [flat]
* Re: Row level security policy
@ 2024-11-14 01:36 David G. Johnston <[email protected]>
2024-11-14 02:13 ` Re: Row level security policy Mark Phillips <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-11-14 01:36 UTC (permalink / raw)
To: Mark Phillips <[email protected]>; +Cc: [email protected] <[email protected]>
On Wednesday, November 13, 2024, Mark Phillips <[email protected]>
wrote:
> Given a database table with one policy statement FOR SELECT applied, it is
> necessary to apply additional policy statements for insert, update, and
> delete operations?
>
It isn’t necessary but most conventional use cases would involve
establishing policies for writing as well as reading. But it is use case
dependent.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Row level security policy
2024-11-14 01:36 Re: Row level security policy David G. Johnston <[email protected]>
@ 2024-11-14 02:13 ` Mark Phillips <[email protected]>
2024-11-14 05:05 ` Re: Row level security policy Mark Phillips <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Mark Phillips @ 2024-11-14 02:13 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: [email protected]
--Apple-Mail-85E717FE-9172-4FCD-8EB8-06CF7BE2C29A
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Thank you. I will revisit my test cases to b=
e sure I have the use cases covered.<br id=3D"lineBreakAtBeginningOfSignatur=
e"><div dir=3D"ltr"> - Mark, out and about.</div><div dir=3D"ltr"><br><=
blockquote type=3D"cite">On Nov 13, 2024, at 5:36=E2=80=AFPM, David G. Johns=
ton <[email protected]> wrote:<br><br></blockquote></div><blo=
ckquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BFOn Wednesday, November 13, 2=
024, Mark Phillips <<a href=3D"mailto:[email protected]">mphillips@m=
ophilly.com</a>> wrote:<br><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Given a database=
table with one policy statement FOR SELECT applied, it is necessary to appl=
y additional policy statements for insert, update, and delete operations?<br=
>
</blockquote><div><br></div><div>It isn=E2=80=99t necessary but most convent=
ional use cases would involve establishing policies for writing as well as r=
eading. But it is use case dependent.</div><div><br></div><div>David J=
.</div><div><br></div>
</div></blockquote></body></html>=
--Apple-Mail-85E717FE-9172-4FCD-8EB8-06CF7BE2C29A--
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Row level security policy
2024-11-14 01:36 Re: Row level security policy David G. Johnston <[email protected]>
2024-11-14 02:13 ` Re: Row level security policy Mark Phillips <[email protected]>
@ 2024-11-14 05:05 ` Mark Phillips <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Mark Phillips @ 2024-11-14 05:05 UTC (permalink / raw)
To: [email protected]; +Cc: David G. Johnston <[email protected]>
Well, things did not work as I expected, which means there is more for me to learn. I am new to RLS usage. I want to implement this in a proper manner, so is the behavior described below correct? Are there other aspects of this I need to study?
Thanks, in advance. Advice and links to articles are greatly appreciated.
- Mark
Here are two tests I ran using pg 12 (upgrade on the schedule).
Given a table “customer” with a column “deadfiled” with a default of false. If deadfile is true, then exclude row from queries executed by role “staff”.
Test 1
CREATE POLICY filter_customer_deadfiled
ON public.customer
AS PERMISSIVE
FOR SELECT
TO staff
USING ((deadfiled IS NOT TRUE));
Select queries by staff do not include row where deadfiled is true. Update and insert queries by staff on visible rows fail.
Test 2
CREATE POLICY filter_customer_deadfiled
ON public.customer
AS PERMISSIVE
FOR ALL
TO prm_staff
USING ((deadfiled IS NOT TRUE));
Select queries by staff do not include row where deadfiled is true. Update insert queries by staff on visible rows succeed.
This indicates that policy using FOR ALL allows CRUD, but if the policy states FOR SELECT then additional policies are needed for insert, update and delete.
> On Nov 13, 2024, at 6:13 PM, Mark Phillips <[email protected]> wrote:
>
> Thank you. I will revisit my test cases to be sure I have the use cases covered.
> - Mark, out and about.
>
>> On Nov 13, 2024, at 5:36 PM, David G. Johnston <[email protected]> wrote:
>>
>> On Wednesday, November 13, 2024, Mark Phillips <[email protected] <mailto:[email protected]>> wrote:
>>> Given a database table with one policy statement FOR SELECT applied, it is necessary to apply additional policy statements for insert, update, and delete operations?
>>
>> It isn’t necessary but most conventional use cases would involve establishing policies for writing as well as reading. But it is use case dependent.
>>
>> David J.
>>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-11-14 05:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-11-14 01:36 Re: Row level security policy David G. Johnston <[email protected]>
2024-11-14 02:13 ` Mark Phillips <[email protected]>
2024-11-14 05:05 ` Mark Phillips <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox