Re: [v9.3] Row-Level Security

On Mon, Oct 22, 2012 at 12:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> The documentation lists several documented limitations that I would
>> like to analyze a little bit. First, it says that row-level security
>> policies are not applied on UPDATE or DELETE. That sounds downright
>> dangerous to me. Is there some really compelling reason we're not
>> doing it?
>
> [ blink... ] Isn't that a security hole big enough for a Mack truck?
>
> UPDATE tab SET foo = foo RETURNING *;
>
> sucks out all the data just fine, if RLS doesn't apply to it.

Yep.

> Having said that, I fear that sensible row-level security for updates is
> at least one order of magnitude harder than sensible row-level security
> for selects. We've speculated about how to define that in the past,
> IIRC, but without any very satisfactory outcome.

Uh, I don't agree. SELECT and DELETE are pretty much identical cases.
UPDATE needs all the same stuff that those two cases need, plus it
has an additional problem that it shares with INSERT - namely, someone
might insert a tuple that they cannot see or update a tuple such that
they can no longer see it. However, both of those problems can be
handled via triggers, for now and maybe forever. In contrast, the
problem that SELECT has - which UPDATE and DELETE also share - namely,
of rows being visible that should not be - is not nearly so
susceptible to that approach, both for performance reasons and because
there's no such thing as a trigger on SELECT.

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