Re: INSERT ... ON CONFLICT UPDATE and RLS

On Wed, Jan 7, 2015 at 5:33 PM, David Fetter <david(at)fetter(dot)org> wrote:
> Same database, same constraints, different outcome.

You're missing the point, I think. UPSERT means that the user doesn't
care about whether or not a given tuple proposed for insertion was
handled with an insert or an update. If you have distinct INSERT and
UPDATE policies (not that I think that many people will), and if they
differ in such a way as to make the difference between some actual
INSERT ... ON CONFLICT UPDATE in the app throwing an error or not
throwing an error depending only on whether the UPDATE (or INSERT)
path was taken, then that combination (the combination of that UPSERT
command, that UPDATE policy and that INSERT policy) is wrong-headed.
It's not based on some particular set of data in the database, but any
and all possible sets. Stephen's objection isn't that the update
operation throws an error; it's that the insert doesn't. I'm with
Stephen; ISTM that selectively enforcing RLS like that is a foot gun.
For column level privileges, you wouldn't expect to only get an error
about not having the relevant update permissions at runtime, when the
update path happens to be taken. And so it is for RLS.

--
Peter Geoghegan