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

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

Powercoder
1#
Powercoder Published in 2018-01-12 16:10:58Z

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?

Vao Tsun
2#
Vao Tsun Reply to 2018-01-12 16:52:38Z

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.

Gordon Linoff
3#
Gordon Linoff Reply to 2018-01-12 16:54:52Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.326085 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO