From rschumeyer@ieee.org Fri Jan  7 16:54:33 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 9FBCA3A4E86
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Jan 2005 16:54:33 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 15126-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Jan 2005 16:54:12 +0000 (GMT)
Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64])
	by svr1.postgresql.org (Postfix) with ESMTP id C83FC3A4ED2
	for <pgsql-sql@postgresql.org>; Fri,  7 Jan 2005 16:54:12 +0000 (GMT)
Received: from dell8200
 (pcp05158174pcs.rte20201.de.comcast.net[69.139.132.79])
	by comcast.net (sccrmhc13) with SMTP
	id <200501071654110160023pr8e>; Fri, 7 Jan 2005 16:54:11 +0000
From: "Rick Schumeyer" <rschumeyer@ieee.org>
To: <pgsql-sql@postgresql.org>
Subject: simulating row ownership
Date: Fri, 7 Jan 2005 11:52:07 -0500
Message-ID: <001901c4f4d9$395726f0$0200a8c0@dell8200>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_001A_01C4F4AF.50811EF0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.6626
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-Virus-Scanned: by amavisd-new at hub.org
X-Archive-Number: 200501/24
X-Sequence-Number: 19812

This is a multi-part message in MIME format.

------=_NextPart_000_001A_01C4F4AF.50811EF0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

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.

=20

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.
Someone

had suggesting using views, but since I can't update a view in postgres, =
I'm

not sure that views help here.

=20

I assume if I use rules, then I need to grant all to public, and let the
rules prevent

users from updating the wrong rows?

=20

Any advice is appreciated.

=20


------=_NextPart_000_001A_01C4F4AF.50811EF0
Content-Type: text/html;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

<html>

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">


<meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">

<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{font-family:Arial;
	color:windowtext;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'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>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>But they should only be able to update and delete =
rows that
they &#8220;own&#8221;.&nbsp; The table</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>has a column indicating the owner.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>What is the best way to accomplish this?&nbsp; =
I&#8217;m not
real familiar with rules, but it seems</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>that I can do this with rules for update and delete =
applied
to the table.&nbsp; Someone</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>had suggesting using views, but since I can&#8217;t =
update a
view in postgres, I&#8217;m</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>not sure that views help here.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'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></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>users from updating the wrong rows?</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Any advice is appreciated.</span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;</span></font></p>

</div>

</body>

</html>

------=_NextPart_000_001A_01C4F4AF.50811EF0--




From mike@fuhr.org Fri Jan  7 20:06:44 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 17A963A482B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Jan 2005 20:06:44 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 03598-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri,  7 Jan 2005 20:06:19 +0000 (GMT)
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by svr1.postgresql.org (Postfix) with ESMTP id 5696D3A4EFE
	for <pgsql-sql@postgresql.org>; Fri,  7 Jan 2005 20:06:23 +0000 (GMT)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.1/8.13.1) with ESMTP id j07K6Ie3044695
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Fri, 7 Jan 2005 13:06:20 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.1/8.13.1) with ESMTP id j07K6HGZ091134;
	Fri, 7 Jan 2005 13:06:17 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.1/8.13.1/Submit) id j07K6HIv091133;
	Fri, 7 Jan 2005 13:06:17 -0700 (MST) (envelope-from mfuhr)
Date: Fri, 7 Jan 2005 13:06:17 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: simulating row ownership
Message-ID: <20050107200617.GA91058@winnie.fuhr.org>
References: <001901c4f4d9$395726f0$0200a8c0@dell8200>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001901c4f4d9$395726f0$0200a8c0@dell8200>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/26
X-Sequence-Number: 19814

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/



From rpeterso@mtholyoke.edu Tue Jan  11 13:38:39 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6E9783A574C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Jan 2005 13:38:39 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 89384-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Jan 2005 13:38:20 +0000 (GMT)
Received: from pagoda.mtholyoke.edu (pagoda.mtholyoke.edu [138.110.30.68])
	by svr1.postgresql.org (Postfix) with ESMTP id 785C53A5882
	for <pgsql-sql@postgresql.org>; Tue, 11 Jan 2005 13:38:22 +0000 (GMT)
Received: from pagoda.mtholyoke.edu (rpeterso@pagoda [127.0.0.1])
	by pagoda.mtholyoke.edu (8.12.11/8.12.11/Debian-5) with ESMTP id
	j0BDcML4027658; Tue, 11 Jan 2005 08:38:22 -0500
Received: (from rpeterso@localhost)
	by pagoda.mtholyoke.edu (8.12.11/8.12.11/Debian-5) id j0BDcM3u027655;
	Tue, 11 Jan 2005 08:38:22 -0500
From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Tue, 11 Jan 2005 08:38:21 -0500
To: Rick Schumeyer <rschumeyer@ieee.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: simulating row ownership
Message-ID: <20050111133821.GA27537@mtholyoke.edu>
Mail-Followup-To: Rick Schumeyer <rschumeyer@ieee.org>,
	pgsql-sql@postgresql.org
References: <001901c4f4d9$395726f0$0200a8c0@dell8200>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <001901c4f4d9$395726f0$0200a8c0@dell8200>
Organization: Mount Holyoke College
X-Operating-System: Debian GNU/Linux
User-Agent: Mutt/1.5.6+20040907i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200501/36
X-Sequence-Number: 19824

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



From rpeterso@mtholyoke.edu Tue Jan  11 13:41:04 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B12513A551A
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Jan 2005 13:41:04 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 89674-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 11 Jan 2005 13:40:51 +0000 (GMT)
Received: from pagoda.mtholyoke.edu (pagoda.mtholyoke.edu [138.110.30.68])
	by svr1.postgresql.org (Postfix) with ESMTP id 64DE73A57BC
	for <pgsql-sql@postgresql.org>; Tue, 11 Jan 2005 13:40:53 +0000 (GMT)
Received: from pagoda.mtholyoke.edu (rpeterso@pagoda [127.0.0.1])
	by pagoda.mtholyoke.edu (8.12.11/8.12.11/Debian-5) with ESMTP id
	j0BDeqTH027706; Tue, 11 Jan 2005 08:40:52 -0500
Received: (from rpeterso@localhost)
	by pagoda.mtholyoke.edu (8.12.11/8.12.11/Debian-5) id j0BDeqFT027703;
	Tue, 11 Jan 2005 08:40:52 -0500
From: Ron Peterson <rpeterso@mtholyoke.edu>
Date: Tue, 11 Jan 2005 08:40:52 -0500
To: Rick Schumeyer <rschumeyer@ieee.org>, pgsql-sql@postgresql.org
Subject: Re: simulating row ownership
Message-ID: <20050111134052.GB27537@mtholyoke.edu>
Mail-Followup-To: Rick Schumeyer <rschumeyer@ieee.org>,
	pgsql-sql@postgresql.org
References: <001901c4f4d9$395726f0$0200a8c0@dell8200>
	<20050111133821.GA27537@mtholyoke.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20050111133821.GA27537@mtholyoke.edu>
Organization: Mount Holyoke College
X-Operating-System: Debian GNU/Linux
User-Agent: Mutt/1.5.6+20040907i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.107 tagged_above=0 required=5 tests=AWL,
	UPPERCASE_25_50
X-Spam-Level: 
X-Archive-Number: 200501/37
X-Sequence-Number: 19825

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