Hi I have a question regarding the approach I can take regarding limiting the data an application user has access to potentially using row level security in PostgreSQL 12.
In my scenario I have a set of report tables where each table has a column called site_id. All the rows that have the same site_id are common to each other.
For example:
CREATE TABLE public.devices (
page_views int8 NOT NULL,
agent_id int4 NOT NULL,
site_id int4 NOT NULL,
date_visit date NOT NULL,
CONSTRAINT devices_pkey PRIMARY KEY (site_id, date_visit, agent_id))
There is a separate set of tables having to do with application users. Application users have a relationship to a set of site_ids.
So, user 'bob' is related to site_id (17, 18, 400, 122). Therefore when 'bob' logs into the web report tool and executes a query bob should only see rows that have 17, 18, 400 and 122 as their site_id. Is this something that can be implemented using PostgreSQL row level security and can it be implemented without having to create a second user in PostgreSQL representing application user 'bob'? I limit what 'bob' sees now via the interface I just want to know if it can be implemented using row level security instead.
All you need is some way in SQL to determine who the current user is.
So you need some state in the database.
You could use a placeholder parameter:
SET application.user = 'bob';
and test with
current_setting('application.user')
or you could use a temporary table with a single entry.
Laurenz, Thanks for setting me on the right path. This is what I have decided to do for now:
create policy test_policy on devices
using (site_id in ((select unnest (string_to_array(current_setting('application.siteids'), ',')::int[]))));
set application.siteids = '244,681';
select * from devices;