Row-level style access with sub tables - Mailing list pgsql-general

This a row-level security style question.

Suppose I have a hypothetical warehouse database. In my warehouse
everything is stored in a crate. My customers are going to rent crates
from me. I'm going to provide a way that they can keep track of what's
in each crate. Because my customers are very security conscious, I
don't want them to know what's in each other's crates.

For this example I'm going to try and implement this using postgres
security. I've created users fred, barney, wilma and betty.

So I'm going to create two tables crate and contents:

Crate:
 crate_id |   crate_location    | owner
----------+---------------------+--------
        1 | On a shelf          | fred
        2 | Under the stairs    | barney
        3 | On the floor        | wilma
        3 | Outside in the rain | betty


Contents:
 item_number | crate_id |           item
-------------+----------+---------------------------
           1 |        1 | spare stone wheel for car
           1 |        3 | lunch for dino
           1 |        2 | bowling ball
           1 |        4 | pelican washing machine


I'm not going to give them permissions on this table. Instead they can
all see these two views:

create view user_crate as select * from crate where owner=current_user;

create view user_contents as select * from contents where crate_id in
(select crate_id from crate where owner=current_user);

Great- now by using these views, all my customers can see what crates
they own and what's in them. (Don't worry, the point of this email is
coming!)

Now, I want to be able to let them alter what is in their crates. I'm
only going to let them use the user_contents view. I'm going to do
this doing rules.

Now I've cracked delete...

create rule delete_contents AS ON DELETE TO user_contents DO INSTEAD
DELETE FROM contents.records WHERE contents.item_number =
old.item_number AND contents.crate_id=old.crate_id

I tried this for update. This stops them from putting stuff in a crate
that doesn't belong to them, but alas it stops them moving stuff from
crate to crate.

create rule update_contents AS ON UPDATE TO user_contents DO INSTEAD
UPDATE contents.records SET contents.item_number=new.item_number

I've tried various things for a rule for INSERT, but I've not come up
with anything that effectively stops people from putting stuff in
other's crates. The only solutions I've come up with involve triggers,
but since I can't put triggers on views, just the underlying
tables. As a superuser (and hypothetical warehouse owner) I want to
insert, delete etc. from the original tables. unencumbered.

In  a nutshell, then, I'd like my users to be able to alter the data
in the tables, but only the data that they can see in the views, and
only in a way so it remains in their views after modification.


Has anyone got any advice?

Thanks and congratulations if you've got to the end of this email :)

David