Re: Row-security writer-side checks proposal - Mailing list pgsql-hackers

On Mon, Nov 4, 2013 at 8:00 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 11/04/2013 09:55 PM, Robert Haas wrote:
>> I continue to think that this syntax is misguided.  For SELECT and
>> DELETE there is only read-side security, and for INSERT there is only
>> write-side security, so that's OK as far as it goes, but for UPDATE
>> both read-side security and write-side security are possible, and
>> there ought to be a way to get one without the other.  This syntax
>> won't support that cleanly.
>
> That's what I was thinking earlier too - separate "FOR READ" and "FOR
> WRITE" instead.
>
> The reason I came back to insert/update/delete was that it's entirely
> reasonable to want to prohibit deletes but permit updates to the same
> tuple. Both are writes; both set xmax, it's just that one _replaces_ the
> tuple, the other doesn't.
>
> So really, there are four cases:
>
> READ
> WRITE INSERT
> WRITE UPDATE
> WRITE DELETE

Isn't READ similarly divisible into READ SELECT, READ UPDATE, and READ DELETE?

>> I would generally expect that most people would want either "read side
>> security for all commands" or "read and write side security for all
>> commands".  I think whatever syntax we come up with this feature ought
>> to make each of those things straightforward to get.
>
> but sometimes with different predicates for read and write, i.e. you can
> see rows you can't modify or can insert rows / update rows that you
> can't see after the change.

Yes, that's possible.

> Similarly, saying you can update but not delete seems quite reasonable
> to me.

If you simply want to allow UPDATE but not DELETE, you can refrain
from granting the table-level privilege.  The situation in which you
need things separate is when you want to allow both UPDATE and DELETE
but with different RLS quals for each.

> On the other hand, we might choose to say "if you want to do things with
> that granularity use your own triggers to enforce it" and provide only
> READ and WRITE for RLS.

The funny thing about this whole feature is that it's just syntax
support for doing things that you can already do in other ways.  If
you want read-side security, create a security_barrier view and select
from that instead of hitting the table directly.  If you want
write-side security, enforce it using triggers.  So essentially what
this is, I think, is an attempt to invent nicer syntax around
something that we already have, and provide a one-stop-shopping
experience rather than a roll-your-own experience for people who want
row-level security.

Now maybe that's fine.  But given that, I think it's pretty important
that we get the syntax right.  Because if you're adding a feature
primarily to add a more convenient syntax, then the syntax had better
actually be convenient.

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