On Wed, Jun 21, 2017 at 9:16 AM, Garry Chen <gc92@cornell.edu> wrote:
> Hi Tom,
> Thank you very much for your information. The column level select right is based on grant to user or role.
WhatI am really looking for is something like row level security that allows developer to develop policy and function
tohide column or columns. I hope the example below can clarify the request.
>
> For example: a function that only allow deptno=30 or resp=10 to see column named 'sale' and 'card_num' and a policy
thatapplied to the table that can carry out the function. So only user in deptno 30 or responsibility level equal to
10can see column named 'sale' and 'card_num' without using role. Such that the security can be relied on the data
ownernot the DBA.
The best you can hope for is the replacement of values in designated
columns with some placeholder value (i.e. removal of the column from
the query in real time is unlikely).
SELECT client_id, CASE WHEN see_ssn THEN ssn_column ELSE '<SSN Number
Redacted>' END AS ssn_display
FROM (SELECT client_id, ssn_column FROM clients) c
CROSS JOIN (SELECT user_id, see_ssn FROM permissions WHERE user_id = 42) perms
This is not something that is presently built into PostgreSQL; and has
not been announced as a work-in-progress on these lists.
David J.