Thread: simulating row ownership

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have a table where I want everyone to be able to be able to insert and select.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">But they should only be able to update and delete rows that they “own”.  The table</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">has a column indicating the owner.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">What is the best way to accomplish this?  I’m not real familiar with rules, but it
seems</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">that I can do this with rules for update and delete applied to the table.  Someone</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">had suggesting using views, but since I can’t update a view in postgres, I’m</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">not sure that views help here.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I assume if I use rules, then I need to grant all to public, and let the rules
prevent</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">users from updating the wrong rows?</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Any advice is appreciated.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font></div>

On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote:

> I have a table where I want everyone to be able to be able to insert and
> select.
> 
> But they should only be able to update and delete rows that they "own".  The
> table has a column indicating the owner.

What does the owner column refer to?  A database user?  If so, then
you could use a trigger that checks CURRENT_USER or SESSION_USER
and raises an exception if the user doesn't have permission to
update or delete the affected row.  In the PostgreSQL documentation,
see the "Triggers" chapter and the "Trigger Procedures" section of
the "PL/pgSQL - SQL Procedural Language" chapter.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Jan 07, 2005 at 11:52:07AM -0500, Rick Schumeyer wrote:

> I have a table where I want everyone to be able to be able to insert
> and select.

> But they should only be able to update and delete rows that they
> "own".  The table has a column indicating the owner.

> What is the best way to accomplish this?  I'm not real familiar with
> rules, but it seems that I can do this with rules for update and
> delete applied to the table.

Using rules, you could do something like the following:

CREATE TABLE test ( aname TEXT PRIMARY KEY
);

INSERT INTO test ( aname ) VALUES ( 'aaa' );
INSERT INTO test ( aname ) VALUES ( 'yourusername' );

CREATE RULE lock_test_user_update
AS ON UPDATE TO test
WHERE old.aname = CURRENT_USER
DO INSTEAD nothing;

CREATE RULE lock_test_user_delete
AS ON DELETE TO test
WHERE old.aname = CURRENT_USER
DO INSTEAD nothing;

-- 
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso

On Tue, Jan 11, 2005 at 08:38:21AM -0500, rpeterso wrote:

> CREATE RULE lock_test_user_update
> AS ON UPDATE TO test
> WHERE old.aname = CURRENT_USER
> DO INSTEAD nothing;
> 
> CREATE RULE lock_test_user_delete
> AS ON DELETE TO test
> WHERE old.aname = CURRENT_USER
> DO INSTEAD nothing;

For your example, these rules should say !=, of course...

-- 
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso