Re: Row-Level Security

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>
Cc: KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Row-Level Security
Date: 2009-12-15 02:06:52
Message-ID: 20091215020652.GV17756@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

KaiGai,

* KaiGai Kohei (kaigai(at)ak(dot)jp(dot)nec(dot)com) wrote:
> IIRC, one headache issue is that user may provide well indexable conditions,
> such as "SELECT * FROM view_x WHERE id = 1234". In this case, if we strictly
> keep the order of evaluation between inside and outside of the view, its
> performance penalty will over reasonable tradeoff to the better security.
>
> Someone pointed out user given conditions which can be replaced by index scan
> are "trusted", so all we need to focus on are conditions which need to check
> for each tuples. I also think it is a right direction, as long as we can
> restrict who can define index access method in appropriate way.

It sounds like that might help, but I feel that a whole solution will be
more complex than just differentiating between seq scan nodes and index
scan ones.

> If we can focus on the order of evaluation on the non-indexed conditions,
> the point is order_qual_clauses() which sort the given qual list based on
> the cost evaluation. If we can mark condition node a flag which means this
> node come from inside of view or row-level policy, it is not difficult to
> keep evaluation order.

Identifying where this matters is important. Anyone have suggestions on
how to do that? There was some discussion on IRC about that but it
didn't really go anywhere. I don't like the idea of presuming the user
will always want to limit the planner in this way. Perhaps we can
convince ourselves, once we have an implementation, that it doesn't
poorly affect performance (the primary reason to avoid constraining the
planner), or that it's what our users would really want (I might be able
to buy off on this..), but I doubt it.

A couple of options about how the user could ask us to constrain the
planning to eliminate this issue are, off-hand:
Global GUC which enables/disables
Attribute of the view, perhaps indicated as 'CREATE SECURITY VIEW' or
similar
Something in the definition of the WHERE clause, eg: select * from x
where security(q = 50);

Anyone have thoughts about this? Perhaps it's too early to discuss
this anyway, just trying to keep the discussion moving in some way.

> However, it is just my quick idea. It might miss something.
> We need to consider the matter for more details...

I agree, this needs more thought and input from others who are very
familiar with the planner, executor, etc. Additionally, this needs
to be done before we can really go anywhere with row-level security.

Thanks,

Stephen

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Fred Janon 2009-12-15 02:08:46 Re: pgAdmin III: timestamp displayed in what time zone?
Previous Message Takahiro Itagaki 2009-12-15 01:51:21 Re: pgbench: new feature allowing to launch shell commands