Operation level authorization in PostgreSQL

Question

I'm developing an analytics application that provides multiple levels of visualization of a data warehouse in a Postgres DB. One of its requirements is that different users should have different levels of access to the data. For example, some users should only extract metrics (counts, avgs, sums) from a particular table, while others could drill down the data to the level of columns.

Example:

employee table

 id    | name   | salary
-------+--------+--------+
     1 | josé   |  20000 
     2 | joão   |  80000 
     3 | tiago  |  60000 

user 1 (can drill down)
------
=> select name from employee where id = 1 
=> josé

user 2 (can read only aggregate data)
------
=> select avg(salary) from employee
=> 53333.3333333
=> select name from employee where id = 1 
=> ERROR

I don't think that any RDBMS would provide that natively. But I wondered if there is any tool that could help me accomplish this authorization level without having to hard code it at the application level.

Answer 1

You can do this with views. The columns of the view must be given names, they can't be specified in the functional form.

create view employee_agg as select count(*), avg(salary) from employee;
grant SELECT ON employee_agg TO user2;

Now as user2:

select * from employee;
ERROR:  permission denied for table employee

select * from employee_agg;
 count |         avg         
-------+---------------------
     3 | 53,333.333333333336

Answer 2

There is built-in mechanism Row Security Policies. enter link description here