Re: Review of Row Level Security

On 19 December 2012 17:25, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Dec 18, 2012 at 3:39 PM, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>> postgres=> INSERT INTO t1 VALUES (4,'ddd');
>> INSERT 0 1
>> postgres=> INSERT INTO t1 VALUES (5,'eee');
>> ERROR: new row for relation "t1" violates row-secirity
>> DETAIL: Failing row contains (5, eee).
>
> I've argued against this before - and maybe I should drop my
> objection, because a number of people seem to be on the other side.
> But I still think there will be some people who don't want this
> behavior. Right now, for example, you can give someone INSERT but not
> SELECT permission on a table, and they will then be able to put rows
> into the table that they cannot read back. Similarly, in the RLS
> case, it is not necessarily undesirable for a user to be able to
> insert a row that they can't read back; or for them to be able to
> update a row from a value that they can see to one that they cannot.
> Some people will want to prohibit that, while others will not.

I can see a use case for not having security apply for users who have
*only* INSERT privilege. This would allow people to run bulk loads of
data into a table with row security. We should add that. That is not
the common case, so with proper documentation that should be a useful
feature without relaxing default security.

Never applying security for INSERT and then forcing them to add BEFORE
triggers if they want full security is neither secure nor performant.

> Previously, I suggested that we handle this by enforcing row-level
> security only on data read from the table - the OLD row, so to speak -
> and not on data written to the table - the NEW row, so to speak -
> because the latter case can be handled well enough by triggers. (The
> OLD case cannot, because not seeing the row is different from erroring
> out when you do see it.) There are other alternatives, like allowing
> the user to specify which behavior they want. But I think that simply
> decreeing that the policy will apply not only to rows read but also
> rows written in all cases will be less flexible than we will
> ultimately want to be.

As discussed, we should add a security feature that is secure by
default. Adding options to make it less secure can follow initial
commit. We might even make it in this release if the review of the
main feature goes well.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services