Обсуждение: [NOVICE] Column level security question
Hi all,
PostgreSQL has row level security by using policy but is there a column level security policy or function for it? If not, is the development of column level security under the development/wish list for PostgreSQL new features?
Thank you very much,
Garry
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