Thread: [NOVICE] Column level security question

Garry Chen <gc92@cornell.edu> writes:
>                 PostgreSQL has row level security by using policy but is there a column level security policy or
functionfor it?  If not, is the development of column level security under the development/wish list for PostgreSQL new
features?

If you're looking for column-level granularity of GRANT/REVOKE privileges,
we have that, eg you can grant the right to SELECT only some columns from
a table.

If that's not what you have in mind, you need to be clearer.

            regards, tom lane

On Wed, Jun 21, 2017 at 8:38 AM, Garry Chen <gc92@cornell.edu> wrote:
> Hi all,
>
>                 PostgreSQL has row level security by using policy but is
> there a column level security policy or function for it?

https://www.postgresql.org/docs/current/static/sql-grant.html

The second syntax block in the list.

David J.

Hi Tom,
    Thank you very much for your information.  The column level select right is based on grant to user or role.  What I
amreally looking for is something like row level security that allows developer to develop policy and function to hide
columnor 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.  


Garry



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, June 21, 2017 11:45 AM
To: Garry Chen <gc92@cornell.edu>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Column level security question

Garry Chen <gc92@cornell.edu> writes:
>                 PostgreSQL has row level security by using policy but is there a column level security policy or
functionfor it?  If not, is the development of column level security under the development/wish list for PostgreSQL new
features?

If you're looking for column-level granularity of GRANT/REVOKE privileges, we have that, eg you can grant the right to
SELECTonly some columns from a table. 

If that's not what you have in mind, you need to be clearer.

            regards, tom lane

Garry Chen <gc92@cornell.edu> writes:
> 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.  

I think you'd be better off to think of a way to express this through
grantable privileges, perhaps with some intermediate views that different
user populations are allowed to access.  It's really hard to think of a
way that columns could be dynamically allowed or not allowed without
breaking SQL semantics pretty thoroughly.

            regards, tom lane

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.

Hi Tom,
    The column or columns can just return null for the non-authorized user then there is not SQL semantics issue.

Garry

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, June 21, 2017 12:27 PM
To: Garry Chen <gc92@cornell.edu>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Column level security question

Garry Chen <gc92@cornell.edu> writes:
> 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.  

I think you'd be better off to think of a way to express this through grantable privileges, perhaps with some
intermediateviews that different user populations are allowed to access.  It's really hard to think of a way that
columnscould be dynamically allowed or not allowed without breaking SQL semantics pretty thoroughly. 

            regards, tom lane