On Mon, 2015-12-14 at 09:55 -0800, 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.
>
>
This is something one usually addresses during the design phase.
You need some sort of mechanism to categorise the users of the app.
Usually a many to one relationship where a bunch of users all share the
same set of privileges.
Then there's your forms. Always display a label "Social Security No."
but leave the field blank, or substitute "Supplied" when it exists or
"Not Supplied" when it is null? You can achieve that via views but you
need a separate view for each category of user and the app needs to
call the select from the appropriate view which means altering the app
after solving how to categorise the users.
You can also create a huge matrix of table_name.column_name accessible
by user category which means you app has to read this first in order to
dynamically build the select list prior to running it. This makes your
forms dynamic as well as you need to pull the labels from somewhere,
input type, etc. all adding I/O time to your DB server.
You need to consider your classes and data factories. You don't want
multiple copies of getter's and setter's for the same table columns.
If there are only a tiny number of restrictions -- such as for a
customer you want to restrict reading social security number and their
credit card details -- you could split this data off into child tables
and disallow drill-down access based upon user category.
Happy to discuss this further off list.
HTH,
Rob
