From: | Cath Lawrence <Cath(dot)Lawrence(at)anu(dot)edu(dot)au> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Rules and users |
Date: | 2003-08-07 01:54:52 |
Message-ID: | 2287458A-C87A-11D7-9EC1-00039390F614@anu.edu.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello,
I am puzzled. I have rules set up such that when a table entry is
changed or deleted, it is first copied to an archive table. This works
beautifully, and everything is fine. But _why_ does it work???
I have a group defined with permissions on a standard table (sequences
too, not included here):
grant select,insert,update,delete on TABLE to group normal;
grant select on TABLE_old to group normal;
So I thought - whoops, I have a bug, my normal user can't delete
because that requires inserting into TABLE_old, which I haven't given
them permission for. But they can! Are rules run as some other user or
what?
I created the rules when building the database as me, but I did my test
update as a "normal" user. And the permissions seem to work right for
non-rule based things - eg my read-only users can indeed only read.
An example of an update rule may be helpful here:
CREATE RULE patients_update_rule AS
ON UPDATE TO patients DO
INSERT INTO patients_old (entry, id, "timestamp",
userstamp, tid, pid, patient_info)
VALUES (old.entry, old.id, old."timestamp",
old.userstamp, old.tid, old.pid, old.patient_info);
Effects of a deletion are pretty similar, with the addition of a
"deleted" flag in the TABLE_old entry.
cheers
Cath
Cath Lawrence, Cath(dot)Lawrence(at)anu(dot)edu(dot)au
Senior Scientific Programmer, Centre for Bioinformation Science,
John Curtin School of Medical Research (room 4088)
Australian National University, Canberra ACT 0200
ph: (02) 61257959 mobile: 0421-902694 fax: (02) 61252595
From | Date | Subject | |
---|---|---|---|
Next Message | Robby Russell | 2003-08-07 03:12:32 | Re: [PHP] authentication question |
Previous Message | Jennifer Vargas | 2003-08-06 23:14:13 | database design |