Re: Permissions, "soft read failure" - wishful thinking?

On Monday, December 14, 2015, Jack Christensen <jack(at)jackchristensen(dot)com>
wrote:

> On 12/14/2015 11: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.
>>
>> This is old hat, but our system has a large number of complex queries that
>> immediately break if *any* field permission fails. So, implementing this
>> for
>> customers could be *very* painful....
>>
>> Is that there is a way to let the query succeed, but nullify any fields
>> where
>> read permissions fail? (crossing fingers) We'd be watching the PG logs to
>> identify problem queries in this case.
>>
>>
>> If userX is a real database user you create a customers view in the userX
> schema that selects from the real customers table and either omits the
> field entirely or nullifies it. Permissions could be used to deny access to
> the underlying table, and search_path could be used to avoid most if not
> all application level changes.
>
>
>
I suspect that previously installed views and functions using this table
may need attention as well...especially if other users do need the column
data to appear.

But a replacement view for read usage seems the most efficient way to alter
the database to implement the additional logic.

It doesn't solve the "we don't trust the application writers to do the
correct thing" though.

David J.