Peter,
On Sunday, January 3, 2016, Peter Geoghegan <
pg@heroku.com> wrote:
On Sun, Jan 3, 2016 at 6:09 PM, Peter Geoghegan <pg@heroku.com> wrote:
>> I think we should get rid of it altogether (since I also agree with the
>> upthread comment that it's in the wrong place) and instead put an example
>> into section 5.7 saying directly that sub-selects, or in general
>> references to rows other than the one under test, are risky in RLS
>> policies. We could also show the FOR UPDATE workaround there.
>
> I agree that there should be a worked out example.
I should remind you that SELECT FOR UPDATE requires conventional
UPDATE privilege (at least on columns appearing in the SELECT list).
So, among SELECT commands, SELECT FOR UPDATE is special in that it
requires UPDATE privilege. This is not true of equivalent RLS
policies, though.
So, as part of documenting the SELECT FOR UPDATE workaround, you're
going to have to advise admins that they need to have (lesser
privileged) user accounts with conventional UPDATE privilege on a
(USING qual referenced) table that they're most certainly not supposed
to be able to UPDATE at all (since they can totally undermine RLS with
such an UPDATE). RLS can make it impossible to actually proceed with
such an UPDATE, which makes the SELECT FOR UPDATE workaround possible,
but it's all pretty messy.
A security defined function could be used to address that, of course. That could have performance implications, naturally.
Thanks,
Stephen
