Thread: Accessing serials through rules

With rules, I can allow people to insert into a table that they don't
otherwise have access to.  And default values seem to get inserted fine
except when the field is type serial (and the default is from a
sequence).  Then you get write permission errors on the associated
sequence.  Should it be like this?

Example:

CREATE TABLE mine (
  id serial,
  pub integer,
  priv integer
);
CREATE VIEW ours AS SELECT id, pub FROM mine;
CREATE RULE ours_ins AS ON INSERT TO ours DO INSTEAD
  INSERT INTO mine (pub) VALUES (NEW.pub);
GRANT ALL ON ours TO public;

Now connect as someone else...

INSERT INTO ours (pub) VALUES (1);
ERROR:  mine_id_seq.nextval: you don't have permissions to set sequence
mine_id_seq

I thought rules were supposed to just run as the owner of the table they
were defined on.  If I grant permissions on the sequence directly, then
those users could mess with its values, couldn't they?

select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 7.1beta4 on i586-pc-linux-gnu, compiled by GCC egcs-2.91.66

--
Rick Delaney
rick@consumercontact.com

Rick Delaney <rick@consumercontact.com> writes:
> With rules, I can allow people to insert into a table that they don't
> otherwise have access to.  And default values seem to get inserted fine
> except when the field is type serial (and the default is from a
> sequence).  Then you get write permission errors on the associated
> sequence.  Should it be like this?

Probably not, but I don't see any reasonable way to fix it at the
moment.  The sequence function's access to the sequence isn't part
of the rule's rangetable and so is not subject to the permission
adjustments that happen for the rule.  More generally, an SQL or PLPGSQL
function invoked by a rule would also be executed with the current
user's permissions, not the rule owner's.  That might or might not be
what you want, but in any case it's quite difficult to change.

We need a rather thoroughgoing redesign of the permissions mechanism
in Postgres, and I guess this is something we'll need to consider when
that happens.

            regards, tom lane