Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBS3F-003w7D-Fv for pgsql-general@arkaria.postgresql.org; Thu, 14 Nov 2024 05:05:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tBS3C-0014QX-QK for pgsql-general@arkaria.postgresql.org; Thu, 14 Nov 2024 05:05:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBS3C-0014QP-DV for pgsql-general@lists.postgresql.org; Thu, 14 Nov 2024 05:05:51 +0000 Received: from artstreetboys.org ([206.214.166.96] helo=mophilly.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tBS3A-001lwm-4s for pgsql-general@lists.postgresql.org; Thu, 14 Nov 2024 05:05:49 +0000 Received: (qmail 31203 invoked by uid 453); 14 Nov 2024 05:05:45 -0000 X-Virus-Checked: by ClamAV 0.103.11 on mophilly.com X-Virus-Found: No Authentication-Results: mophilly.com; auth=pass (plain) smtp.auth=mphillips Received: from wsip-98-173-51-151.sd.sd.cox.net (HELO smtpclient.apple) (98.173.51.151) by mophilly.com (qpsmtpd/0.96) with ESMTPSA (ECDHE-RSA-AES256-GCM-SHA384 encrypted); Wed, 13 Nov 2024 21:05:45 -0800 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=mophilly.com; h=from:message-id:content-type:mime-version:subject:date:in-reply-to:cc:to:references; s=default; bh=KEHVPTTRyz+UvT1s00YxoHMR9hkR0epWItNcwdkucPU=; b=Gub+dSL1xmTci9vvn1w0TC14KDevXk4h2Dwdl5foRXHzovwFYoHxggcQinOd7KJc+6ei7+Wp7q61VGo5ioDbstHpYqS+ruCL7xdFDE8SRZD1VGL71ALol+CTLiORQaeEN0YPJ1AupSff5rT2L5L+kJlJaSMmeh1on/J1ULH7BpmwC/+dnR4974oUGLs2/Pmi18YKJ5gcgS2nLAjCZJtiBVsTQzTql1rgROxlpatQ8CAnKBIpEHnv7RtNSno3UlwdNAJeVkSDp2bc1pKLmEA2oT9we/kqJHA/OuvqSlHjetIfuVuBR9K7fRr3H9u8MTn5HeakrAIFslzjhSwDQIvrVA== From: Mark Phillips Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_2B5399C1-9B04-4F35-86BA-FEC0D2BFF970" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\)) Subject: Re: Row level security policy Date: Wed, 13 Nov 2024 21:05:34 -0800 In-Reply-To: <3D0A30B0-0A39-4F71-9449-415196B631DE@mophilly.com> Cc: "David G. Johnston" To: pgsql-general@lists.postgresql.org References: <3D0A30B0-0A39-4F71-9449-415196B631DE@mophilly.com> X-Mailer: Apple Mail (2.3826.200.121) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_2B5399C1-9B04-4F35-86BA-FEC0D2BFF970 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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).=20 Given a table =E2=80=9Ccustomer=E2=80=9D with a column =E2=80=9Cdeadfiled=E2= =80=9D with a default of false. If deadfile is true, then exclude row = from queries executed by role =E2=80=9Cstaff=E2=80=9D. 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.=20 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.=20 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=E2=80=AFPM, Mark Phillips = wrote: >=20 > Thank you. I will revisit my test cases to be sure I have the use = cases covered. > - Mark, out and about. >=20 >> On Nov 13, 2024, at 5:36=E2=80=AFPM, David G. Johnston = wrote: >>=20 >> =EF=BB=BFOn Wednesday, November 13, 2024, Mark Phillips = > 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? >>=20 >> It isn=E2=80=99t necessary but most conventional use cases would = involve establishing policies for writing as well as reading. But it is = use case dependent. >>=20 >> David J. >>=20 --Apple-Mail=_2B5399C1-9B04-4F35-86BA-FEC0D2BFF970 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 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 = =E2=80=9Ccustomer=E2=80=9D with a column =E2=80=9Cdeadfiled=E2=80=9D = with a default of false. If deadfile is true, then exclude row from = queries executed by role =E2=80=9Cstaff=E2=80=9D.

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=E2=80=AFPM, Mark Phillips = <mphillips@mophilly.com> 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=E2=80=AFPM, David G. Johnston = <david.g.johnston@gmail.com> = wrote:

=EF=BB=BFOn Wednesday, November 13, 2024, Mark Phillips = <mphillips@mophilly.com> = 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=E2=80=99t necessary but most = conventional use cases would involve establishing policies for writing = as well as reading.  But it is use case = dependent.

David J.

=

<= /html>= --Apple-Mail=_2B5399C1-9B04-4F35-86BA-FEC0D2BFF970--