Thread: Rules and users

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.Lawrence@anu.edu.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

Cath Lawrence <Cath.Lawrence@anu.edu.au> writes:
> 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???

Because the rule-added action occurs before the original UPDATE or DELETE.
This is stated in the fine print somewhere in the manual's discussion
of rules.

> 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?

Yes, as the rule owner.  (More specifically, any table accesses added
by the rule are checked using the owner's permissions.  If you have,
say, a function that's called in a rule query, it still executes using
the caller's permissions.  There has been debate over whether this is a
bug.)

            regards, tom lane