Thread: Row level security policy
Given a database table with one policy statement FOR SELECT applied, it is necessary to apply additional policy statementsfor insert, update, and delete operations? My testing indicates that this is case but I haven’t found an explanation of this requirement in the documentation. - Mark
On 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’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.
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 <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 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:On 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’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.
On Wed, 2024-11-13 at 17:33 -0800, 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? > > My testing indicates that this is case but I haven’t found an explanation of this > requirement in the documentation. https://www.postgresql.org/docs/current/ddl-rowsecurity.html says: When row security is enabled on a table (with ALTER TABLE ... ENABLE ROW LEVEL SECURITY), all normal access to the table for selecting rows or modifying rows must be allowed by a row security policy. So if you only have a policy for SELECT, that's all you are allowed to do. Yours, Laurenz Albe
Thank you for clarifying this. I missed that even though it is there in the second paragraph. - Mark, out and about. > On Nov 14, 2024, at 1:57 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > On Wed, 2024-11-13 at 17:33 -0800, 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? >> >> My testing indicates that this is case but I haven’t found an explanation of this >> requirement in the documentation. > > https://www.postgresql.org/docs/current/ddl-rowsecurity.html says: > > When row security is enabled on a table (with ALTER TABLE ... ENABLE ROW LEVEL SECURITY), > all normal access to the table for selecting rows or modifying rows must be allowed by > a row security policy. > > So if you only have a policy for SELECT, that's all you are allowed to do. > > Yours, > Laurenz Albe