pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsimulating row ownership
4+ messages / 3 participants
[nested] [flat]
* simulating row ownership
@ 2005-01-07 16:52 Rick Schumeyer <rschumeyer@ieee.org>
0 siblings, 2 replies; 4+ messages in thread
From: Rick Schumeyer @ 2005-01-07 16:52 UTC (permalink / raw)
To: pgsql-sql
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.
Someone
had suggesting using views, but since I can't update a view in postgres, I'm
not sure that views help here.
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?
Any advice is appreciated.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: simulating row ownership
@ 2005-01-07 20:06 Michael Fuhr <mike@fuhr.org>
parent: Rick Schumeyer <rschumeyer@ieee.org>
1 sibling, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-01-07 20:06 UTC (permalink / raw)
To: Rick Schumeyer <rschumeyer@ieee.org>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: simulating row ownership
@ 2005-01-11 13:38 Ron Peterson <rpeterso@mtholyoke.edu>
parent: Rick Schumeyer <rschumeyer@ieee.org>
1 sibling, 1 reply; 4+ messages in thread
From: Ron Peterson @ 2005-01-11 13:38 UTC (permalink / raw)
To: Rick Schumeyer <rschumeyer@ieee.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: simulating row ownership
@ 2005-01-11 13:40 Ron Peterson <rpeterso@mtholyoke.edu>
parent: Ron Peterson <rpeterso@mtholyoke.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Ron Peterson @ 2005-01-11 13:40 UTC (permalink / raw)
To: Rick Schumeyer <rschumeyer@ieee.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-01-11 13:40 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-07 16:52 simulating row ownership Rick Schumeyer <rschumeyer@ieee.org>
2005-01-07 20:06 ` Michael Fuhr <mike@fuhr.org>
2005-01-11 13:38 ` Ron Peterson <rpeterso@mtholyoke.edu>
2005-01-11 13:40 ` Ron Peterson <rpeterso@mtholyoke.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox