Postgresql row policy by user and row data

Question

I have a table named products in my postgresql 9.5 database. And this table fields are like this:

id
name
sales_area

And data is like this:

    id       name       sales_area
    1        prod1      A1
    2        prod2      A1
    3        prod3      A2
    4        prod4      A3

And I want to create a database user named user1, and this user should select, update and delete only A1 sales_area datas. Other database user will select, update and delete all datas.

Is this rule possible using policy? And How?

I don't think that is possible, but you can create a sample view(applying a condition where sales_area = 'A1') and grant permission to "user1" over new view. — Vivek
– Vivek, Commented Jul 17, 2018 at 9:04
Yes, it should. there are some keywords and rule that should be taken care then it will allow to perform all the dml — Vivek
– Vivek, Commented Jul 17, 2018 at 9:12

Laurenz Albe · Accepted Answer · 2018-07-18 06:22:28Z

1

I think that this can be done using row level security as follows:

ALTER TABLE products ENABLE ROW LEVEL SECURITY;

CREATE POLICY for_user1 ON products AS PERMISSIVE
   FOR ALL TO PUBLIC
   USING (current_user <> 'user1' OR sales_area = 'A1');

Then user1 can only access sales_area A1 and everybody else can access everything.

Some explanations:

FOR ALL means “for all actions”, see the documentation:

Using ALL for a policy means that it will apply to all commands, regardless of the type of command.
The lack of a WITH CHECK clause does not mean that data modifications won't be checked. Again a quote from the documentation:

ALL policies will be applied to both the selection side of a query and the modification side, using the USING expression for both cases if only a USING expression has been defined.

edited Jul 18, 2018 at 6:22

answered Jul 17, 2018 at 17:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

barteloma Over a year ago

Does "For all to public" contains select insert update?

Laurenz Albe Over a year ago

I have extended the answer in the hope to cover this and other questions.

Vivek · Accepted Answer · 2018-07-17 09:17:46Z

0

About permission am not sure, but alternatively, you can create a view as below and give them permission to "user1" to a new view.

create table tx1(id int,name varchar(20),sales_area varchar(20));
insert into tx1 values(1,'prod1','A1');
insert into tx1 values(2,'prod2','A1');
insert into tx1 values(3,'prod3','A2');
insert into tx1 values(4,'prod4','A3');

create view tx1_view
as select * from tx1 where sales_area='A1';

insert into tx1_view values(5,'prod5','A1');

select * from tx1_view;

Demo

answered Jul 17, 2018 at 9:17

Vivek

8036 silver badges13 bronze badges

Collectives™ on Stack Overflow

Postgresql row policy by user and row data

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related