Re: Review of Row Level Security

Kohei KaiGai wrote:

> If system ensures writer's permission is always equivalent or
> more restrictive than reader's permission, it also eliminates the
> problem around asymmetric row-security policy between commands.

I'm not sure we're understanding each other; so far people who
favor asymmetric read and write predicates for row level security
have been arguing that people should be able to write tuples that
they can't read back. Of course you need to be able to read a tuple
to update it, but the argument is that you should be able to
configure security so that a role can (for example) update a row to
set a "sealed" flag, and then no longer have rights to read that
row (including for purposes of update). You can "give away" data
which is yours, but you can't then "take it back" if it's not.

> The problematic scenario was that updatable but invisible rows
> are exposed;

I have not seen anyone argue that such behavior should be allowed.

> Probably, we can implement it as ((row-security of select) AND
> (row-security of update)) that ensures "always restrictive
> row-security policy".

I hadn't been paying a lot of attention to this patch until I saw
the question about whether a user with a particular role could
write a row which would then not be visible to that role. I just
took a look at the patch.

I don't think I like ALTER TABLE as a syntax for row level
security. How about using existing GRANT syntax but allowing a
WHERE clause? That seems more natural to me, and it would make it
easy to apply the same conditions to multiple types of operations
when desired, but use different expressions when desired. Without
having spent a lot of time pondering it, I think that if row level
SELECT permissions exist, they would need to be met on the OLD
tuple to allow DELETE or UPDATE, and UPDATE row level permissions
would be applied to the NEW tuple.

So, Simon's use-case could be met with:

GRANT SELECT, INSERT, UPDATE, DELETE ON tabx
 TO org12user
 WHERE org = 12;

... and similar GRANTs.  A user who should be able to access rows
for a particular value of org would be granted the appropriate
permission. These could be combined by granting a role to another
role. To go back to a Wisconsin Courts example, staff in a county
might be granted rights to access data for that county, but
district roles could be set up and granted to court officials, who
need to be able to access data for all counties in their judicial
district, because judges fill in for each other across county
lines, but only within their own district.

My use-case could be met with:

GRANT SELECT, INSERT, UPDATE, DELETE ON addr
 TO general_staff
 WHERE NOT sealed;
GRANT SELECT, INSERT, UPDATE, DELETE ON addr
 TO sealed_addr_authority
 WHERE SEALED;
GRANT general_staff TO sealed_addr_authority;

Note that I think that if one has multiple roles with row level
permissions on a table, access should be allowed if any of those
roles allows it.

I think that the above should be logically equivalent to (although
perhaps slightly less efficient at run-time):

GRANT SELECT, INSERT, UPDATE, DELETE ON addr
 TO general_staff
 WHERE NOT sealed;
GRANT SELECT, INSERT, UPDATE, DELETE ON addr
 TO sealed_addr_authority;

And just to round it out, that these could be applied to users
with:

GRANT general_staff TO bob;
GRANT sealed_addr_authority TO supervisor;
GRANT supervisor TO jean;

I realize this is a major syntactic departure from the current
patch, but it just seems a lot more natural and flexible.

-Kevin