PostgreSQL - additional condition over the given in WHERE clause in dependency on user's role

Question

I have a table with such columns as a int, b boolean. All users in database have privileged or non-privileged role. Privileged users have access to all rows from table, non-privileged - only to those rows where b is true.

So when non-privileged user executes SELECT, UPDATE or DELETE query it must save it's WHERE condition but also filter all rows what aren't b.

Example: if we have in table:

a | c
--+--
1 | T
2 | T
3 | F
4 | F

and privileged user executes SELECT FROM table WHERE a > 1, he must get

a | c
--+--
2 | T
3 | F
4 | F

whilst non-privileged user on the same query must get

a | c
--+--
2 | T

Is there any ways to implement it using triggers or something?

RLS is not the option?.. postgresql.org/docs/current/static/ddl-rowsecurity.html, what's your version? — Vao Tsun
– Vao Tsun, Commented Jan 12, 2018 at 16:23
@VaoTsun looks like what I need. I'm working with databases for the first time, didn't know about that, — Powercoder
– Powercoder, Commented Jan 12, 2018 at 16:43
@VaoTsun, have you any ideas? stackoverflow.com/questions/48238936/… — Powercoder
– Powercoder, Commented Jan 13, 2018 at 15:11

Vao Tsun · Accepted Answer · 2018-01-12 16:52:38Z

0

if you have version 9.5 and higher - use

https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html

In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security.

answered Jan 12, 2018 at 16:52

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

2 Comments

Powercoder Over a year ago

Can it be used when USING clause contain SELECT query from the same table on which this policy is set? I mean it's raising infinite recursion detected in policy for relation.

Vao Tsun Over a year ago

please show the code with error in new post. but in short - yes are ot supposed to recurse rules or triggers

Gordon Linoff · Accepted Answer · 2018-01-12 16:54:52Z

0

One method users a view:

create view v_table as
    select t.*
    from table t
    where c = 'F' or
          exists (select 1 from users u where u.user = current_user and u.role = 'privileged');

Then, access the table only through the view.

answered Jan 12, 2018 at 16:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - additional condition over the given in WHERE clause in dependency on user's role

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related