CODEWITHSUNDEEP
postgresql
MarkatFM
MarkatFM

Reputation: 45

Row based security with application users strategy

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.

Upvotes: 2

Views: 226

Answers (2)

MarkatFM
MarkatFM

Reputation: 45

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;

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246308

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.

Upvotes: 2

Related Questions