Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f67J40a04309 for ; Sat, 7 Jul 2001 15:04:00 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f67J3jJ28565; Sat, 7 Jul 2001 15:03:45 -0400 (EDT) To: Dado Feigenblatt cc: josh@agliodbs.com, pgsql-sql@postgresql.org Subject: Re: creating variable views In-reply-to: <3B463AAA.D891C6F@wildbrain.com> References: <3B463AAA.D891C6F@wildbrain.com> Comments: In-reply-to Dado Feigenblatt message dated "Fri, 06 Jul 2001 15:24:42 -0700" Date: Sat, 07 Jul 2001 15:03:44 -0400 Message-ID: <28562.994532624@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200107/85 X-Sequence-Number: 3869 Dado Feigenblatt writes: > One thing that still isn't clear for me is how to implement access level > control on a per row basis. The SQL GRANT/REVOKE stuff doesn't deal with anything finer-grain than tables. The best way I know to cope with a need for row-level read access control is to create a view that shows only the records you want someone to be able to see, then grant them access to the view not the original table. For write access control, you have a choice of putting the controls into the ON INSERT etc rules you make for the view, or attaching triggers to the underlying table and checking access permissions in the triggers. The trigger method is probably easier to deal with, but bear in mind that such triggers will fire for everyone, including people who've been granted direct access to the underlying table. This might or might not be just what you want... regards, tom lane