Thread: Permissions, "soft read failure" - wishful thinking?
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.
On Mon, Dec 14, 2015 at 09:55:02AM -0800, Benjamin Smith wrote: > Is that there is a way to let the query succeed, but nullify any fields where > read permissions fail? What about real field values begin NULL ? Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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.
Not for at least a year even if someone wanted to take on this project. As far along as we are now 1.5 to 2 years would be a more reasonable minimum. That said you can always patch and distribute your own version until the patch goes mainstream.
I'm not convinced that the obligations the project takes on by implementing such a feature sufficiently surpass the benefits it would provide. Unfortunately it also doesn't seem to fit very well as an "extension" either - you probably would have to patch the core code to make anything of this form work.
I'm not sure what you expect the logs to show since these queries would not longer be "problem queries"...
David J.
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? > > 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. > > -- Adrian Klaver adrian.klaver@aklaver.com
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
On 12/14/2015 04:22 PM, Benjamin Smith wrote: Ccing list > 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 I see now. See David Johnston's post. > > > FOLLOWUP QUESTION: is there a way to ask the query planner what tables/fields > were output in a database result? > I am not following, that would be in the query output would it not? A more detailed explanation of what you want to see would be helpful. -- Adrian Klaver adrian.klaver@aklaver.com
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
On Monday, December 14, 2015 05:20:52 PM Adrian Klaver wrote: > > FOLLOWUP QUESTION: is there a way to ask the query planner what > > tables/fields were output in a database result? > > I am not following, that would be in the query output would it not? A > more detailed explanation of what you want to see would be helpful. The problem is that permissions were originally designed to be handled in the ORM (and they are, just fine) and read-level permissions were set up at the page level (it's a web based application) so you can either access the page or you can't. But in order to satisfy a large client, they want the guarantee of permissions at the field level. Problem is, our product has been in development for a LONG time (started when PHP3 was king of the hill) and if we did that, large, complex queries would break in many horrible ways. For example, it's typical to create a with prefix table with a 5-table query, then link to that two or three times over in order to develop a complex relationship with data, and then output the result. I may be wishing for magic, but would it be possible to be able to ask the DB server where the fields of data got their data from? (so we could squelch the output as it passes through our DB abstraction layer) EXPLAIN ANALYZE *almost* does this, thus the thought.
On Monday, December 14, 2015 05:25:16 PM Adrian Klaver wrote: > > 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? Ha ha, in another post, I just explained that the idea for the follow up question came from EXPLAIN ANALYZE. Yes, the idea being to see if there was a way to ask PG what tables/fields were used to output a specific result, field by field, and then squelch these fields in our DB abstraction layer rather than in the DB directly. We're being asked to satisfy some pretty strict guarantees of data privacy that were unanticipated when designing our product. Adding strict permissions now would be an expensive proposition.
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. Jack
On Monday, December 14, 2015, Benjamin Smith <lists@benjamindsmith.com> wrote:
On Monday, December 14, 2015 05:25:16 PM Adrian Klaver wrote:
> > 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?
Ha ha, in another post, I just explained that the idea for the follow up
question came from EXPLAIN ANALYZE. Yes, the idea being to see if there was a
way to ask PG what tables/fields were used to output a specific result, field by
field, and then squelch these fields in our DB abstraction layer rather than in
the DB directly.
We're being asked to satisfy some pretty strict guarantees of data privacy
that were unanticipated when designing our product. Adding strict permissions
now would be an expensive proposition.
Alter table private_data alter column ssn check (ssn = '');
I'm only partially joking...
These seem like sound ideas (or maybe not as I write more...) so the question is whether you want to fund developing them instead of fixing your application. They don't seem like magic bullets though so you still have to work on the application...
You are likely going to burn a lot of cycles on lots of queries that don't care about this stuff to cover the few that do, not a worthwhile trade-off generally.
Maybe you should tell your customer that the cost-benefit isn't there for your company...
David J.
On Monday, December 14, 2015, Jack Christensen <jack@jackchristensen.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'tIf 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.
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.
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.
Benjamin, * Benjamin Smith (lists@benjamindsmith.com) 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? Not directly, no. One approach would be to create views which nullify records based on what the user is allowed to access. These views could reference other tables in a similar manner to RLS policies and would not require DB users to exist. That implies a pretty signifigant change to the application though, I expect. Supporting column-level policies is definitly on my list of things to look at doing, specifically to address these kinds of issues. That's not going to help you in the very short term though, unfortunately. Thanks! Stephen
Attachment
On Mon, 14 Dec 2015 09:55:02 -0800, Benjamin Smith <lists@benjamindsmith.com> 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? How about using encryption? pgp_sym_decrypt() returns null if any argument is null. So encrypt the relevant column(s) and associate the decryption key(s) on a per user basis. Users who can't see the data will have a null key for it and will get null back as a result. This will get painful if you need to handle a lot of users x columns, but superficially I think it achieves what you want. George