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?.. https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html, what's your version? — Vao Tsun, Jan 12 '18 at 16:23
@VaoTsun looks like what I need. I'm working with databases for the first time, didn't know about that, — Powercoder, Jan 12 '18 at 16:43
@VaoTsun, have you any ideas? https://stackoverflow.com/questions/48238936/postgresql-infinite-recursion-detected-in-policy-for-relation — Powercoder, Jan 13 '18 at 15:11

score 0 · Accepted Answer · answered Jan 12 '18 at 16:52

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 '18 at 16:52

Vao Tsun

47,234
13
100
132

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`. – Powercoder Jan 13 '18 at 09:21
please show the code with error in new post. but in short - yes are ot supposed to recurse rules or triggers – Vao Tsun Jan 13 '18 at 09:29

score 0 · Answer 2 · answered Jan 12 '18 at 16:54

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 '18 at 16:54

Gordon Linoff

1,242,037
58
646
786

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

2 Answers2