Let’s create a user and table with some data:
CREATE ROLE admin;
CREATE TABLE employee (empno int, ename text, address text, salary int, account_number text);
INSERT INTO employee VALUES
(1, 'john' , '2 down str' , 20000, 'HDFC-22001')
, (2, 'clark' , '132 south avn', 80000, 'HDFC-23029')
, (3, 'soojie', 'Down st 17th' , 60000, 'ICICI-19022')
;
Now, let's create column-level permissions:
postgres=> \c postgres edb
You are now connected to database "postgres" as user "edb".
postgres=# grant select (empno, ename, address) on employee to admin;
GRANT
postgres=# \c postgres admin
You are now connected to database "postgres" as user "admin".
postgres=> select empno, ename, address, salary from employee;
ERROR: permission denied for table employee
postgres=> select empno, ename, address from employee;
empno | ename | address
-------+--------+---------------
1 | john | 2 down str
2 | clark | 132 south avn
3 | soojie | Down st 17th
Everything is working fine so far.
But is it possible in PostgreSQL to create a more complex rule — rather than completely restricting the admin user from seeing the salary column, for example, to return NULL values for all rows and show the actual value in the salary column only where, for instance, ename = 'clark'?
In other words, instead of returning an error as in the example below:
postgres=> select empno, ename, address, salary from employee;
ERROR: permission denied for table employee
Return the following result:
empno | ename | address | salary
-------+--------+---------------+--------
1 | john | 2 down str | NULL
2 | clark | 132 south avn | 80000
3 | soojie | Down st 17th | NULL
Most likely, this can be done through a view, but I am specifically interested in achieving this result using simple rules as shown above.
If possible, I would appreciate an example.
The correct way to do that is a view:
CREATE VIEW emp_view WITH (security_barrier = on) AS
SELECT empno, ename, address,
CASE WHEN ename = 'clark'
THEN salary
END AS salary
FROM employee;
GRANT SELECT ON emp_view TO admin;
Then admin can see the data as you want without having direct access to the underlying table.
security_barrier is necessary to prevent admin from accessing data she shouldn't see. See the documentation for details.