Re: Review of Row Level Security

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.

There is also precedent for your opinion in the spec-mandated behavior
of updatable views: it is perfectly possible to INSERT a row that you
can't read back via the view, or UPDATE it to a state you can't see
via the view. The RLS patch's current behavior corresponds to a view
created WITH CHECK OPTION --- which we don't support yet. Whether
we add that feature soon or not, what seems important for the current
debate is that the SQL spec authors chose not to make it the default
behavior. This seems to weigh heavily against making it the default,
much less only, behavior for RLS cases.

I'd also suggest that "throw an error" is not the only response that
people are likely to want for attempts to insert/update non-compliant
rows, so hard-wiring that choice is insufficiently flexible even if you
grant that local policy is to not allow such updates. (As an example,
they might prefer to log the attempt and substitute some other value.)

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

+1. I'm less than excited about RLS in the first place, so the less
complexity we have to put into the core system for it the better IMO.

regards, tom lane