On 12/14/2015 04:22 PM, Benjamin Smith wrote:
> On Monday, December 14, 2015 01:02:00 PM you wrote:
>> On 12/14/2015 09:55 AM, Benjamin Smith wrote:
>>> Is there a way to set PG field-level read permissions so that a deny
>>> doesn't cause the query to bomb, but the fields for which permission is
>>> denied to be nullified?
>>>
>>> In our web-based app, we have a request to implement granular permissions:
>>> table/field level permissions. EG: userX can't read
>>> customers.socialsecurity in any circumstance. We'd like to implement
>>> DB-level permissions; so far, we've been using an ORM to manage CRUD
>>> permissions.
>>
>> The new Row Level Security only extends down to the row AFAIK, so how
>> are you doing this or planning on doing this?
>
> We aren't looking for row-level permissions, but field-level, which is quite
> mature. EG, for the above example of customers.socialsecurity:
>
> GRANT select(socialsecurity) ON customers TO frontdeskuser;
>
>
> My guess for implementation would look something like:
>
> REVOKE select(socialsecurity) ON customers FROM frontdeskuser;
> GRANT selectasnull(socialsecurity) ON customers TO frontdeskuser;
>
> So that when frontdesk ran
>
> select * from customers where id = 123;
>
> They'd get something like
>
> id | name | socialsecurity
> ------+--------------+--------
> 123 | Bobby tables | null
>
>
> FOLLOWUP QUESTION: is there a way to ask the query planner what tables/fields
> were output in a database result?
Just dawned on me, are you asking if EXPLAIN can output more detailed
information?
>
--
Adrian Klaver
adrian.klaver@aklaver.com
