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

[George Neuner]

On Mon, 14 Dec 2015 09:55:02 -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? 

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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Stephen Frost]

Benjamin,

* Benjamin Smith (li...@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


signature.asc
Description: Digital signature

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

[David G. Johnston]

On Monday, December 14, 2015, Jack Christensen 
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.

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

[rob stone]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Benjamin Smith]

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. 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jack Christensen]

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Benjamin Smith]

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. 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

On Monday, December 14, 2015, Benjamin Smith 
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.

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

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

On Mon, Dec 14, 2015 at 10: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
> ​.
>

​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.​

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

[Karsten Hilbert]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Permissions, "soft read failure" - wishful thinking?

[Benjamin Smith]

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. 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general