Re: leaky views, yet again - Mailing list pgsql-hackers

On Tue, Oct 5, 2010 at 1:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Oct 5, 2010 at 12:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> That doesn't make permissions on them useless, so you're attacking a
>>> straw man.
>
>> Really?  I'm confused.  What is the use case for the status quo?
>
> Access to tables that you have no direct privileges on, mainly.
> (This is significantly more useful when you consider updates on
> views than when you consider selects alone.)  Even if it were totally
> useless from the point of view you're choosing to adopt, you'd have
> a hard row to hoe to convince us to take out a SQL-standard feature.
>
> The SQL facility *is* leak-proof AFAICS for column filtering.
> Row filtering, not so much.

OK, that's true.  I guess if you only want to filter columns, and not
rows, then the way it's done right now works.  I didn't think of that.

> We could make it leak-proof (by making
> the view into a hard optimization boundary) but I think everyone's
> agreed that the performance consequences would be so bad as to make
> such a feature useless.  Unfortunately I don't see any design that
> avoids the performance penalty while still being guaranteed
> leak-proof.  Once you realize that performance itself can be a leak
> channel, it may well be that that's simply a contradiction in terms.
> And I don't see a lot of use in plugging some side channels while
> others remain open.  At least, not without a much more explicit
> threat model than anyone's actually offered for this patch, which
> would explain exactly why we need to close these particular side
> channels and not others.

Well, the only thing I've ever wanted to do this for was to allow
sales reps to see their own customers but not the customers of other
sales reps (because if they could pull our complete customer list,
then once they left and went to work for $COMPETITOR they'd start
trying to pick off our customers; of course, we couldn't prevent them
from maintaining a list of their own customers, and no doubt they knew
who some of the other customers were, but they couldn't just dump out
the complete list from the database).  I agree it's hopeless to
prevent all side-channel leaks, but I'd describe the goal like this:

Prevent access to the actual tuple contents of the hidden rows.

Failing to solve this problem at the database level doesn't remove the
business requirement.  I've solved this problem in the past by
ensuring that only trusted users have access to the database, and
forcing everyone else to go through an application that restricts the
set of queries they can issue.  That doesn't eliminate the
side-channel leak, though: they can still pull out a stopwatch and
attempt to infer the size of the table from the query execution time.
But in the above example, the total number of our customers isn't
particularly secret, just the identities of those customers (and
related information like who the main contact is and when their
contract term is up and how much they're paying and all those sorts of
things).  I think this is a common pattern; and it seems to me that if
I can enforce this security policy by writing an application wrapper
around the database, I should also be able to have the database itself
enforce it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company