Re: Column level security question

On Wed, Jun 21, 2017 at 9:16 AM, Garry Chen <gc92(at)cornell(dot)edu> wrote:
> Hi Tom,
> Thank you very much for your information. The column level select right is based on grant to user or role. What I am really looking for is something like row level security that allows developer to develop policy and function to hide 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 that applied to the table that can carry out the function. So only user in deptno 30 or responsibility level equal to 10 can see column named 'sale' and 'card_num' without using role. Such that the security can be relied on the data owner not 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.