Re: RLS open items are vague and unactionable

On 11 September 2015 at 13:05, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Thanks for the updates. My thoughts:
>
> On RETURNING, it seems like we've got a fairly fundamental problem
> here. If I understand correctly, the intention of allowing policies
> to be filtered by command type is to allow blind updates and deletes,
> but this behavior means that they are not really blind. You can
> always use BEGIN/UPDATE-or-DELETE-with-RETURNING/ROLLBACK as a
> substitute for SELECT. So the only possible thing you can do with the
> ability to filter by command tag that is coherent from a security
> point of view is to make the update and delete predicates *tighter*
> than the select predicate.
>
> And if that's where we end up, then haven't we fundamentally
> mis-designed the feature? I mean, without the blind update case, it
> just seems kooky to have different commands see different rows. It
> would be better to have all the command see the same rows, and then
> have update/delete *error out* if you try to update a row you're not
> allowed to touch.
>

I think blind updates are a pretty niche case, and I think that it
wasn't the intention to support them, but more of an unintentional
side effect of the implementation. That said, there are
just-about-plausible use-cases where they might be considered useful,
e.g., allow a password to be nulled out, forcing a reset, but don't
allow the existing value to be read. But then, as you say, RETURNING
blows a hole in the security of that model.

I still think the answer is to make RETURNING subject to SELECT
policies, with an error thrown if you attempt a blind-update-returning
for a row not visible to you, e.g.:

DELETE FROM foo WHERE id=10; -- OK even if row 10 is not visible

DELETE FROM foo WHERE id=10 RETURNING *;
ERROR: row returned by RETURNING is not visible using row level
security policies for "foo"

Regards,
Dean