The following bug has been logged online:

Bug reference: 1504
Logged by: Nicolas Rachinsky
Email address: nr (AT) rachinsky (DOT) de
PostgreSQL version: 7.4.7
Operating system: FreeBSD 4.10
Description: Wrong user is used for sequences through rules
Details:

As the user (nicolas) owning the database (webtempl) I execute:
DROP TABLE log CASCADE;
CREATE TABLE log(
id BIGSERIAL PRIMARY KEY,
msg TEXT NOT NULL DEFAULT ''
);


DROP TABLE log_insert CASCADE;

CREATE TABLE log_insert(
msg TEXT NOT NULL DEFAULT ''
);


CREATE RULE log_ins AS ON INSERT TO log_insert
DO INSTEAD
INSERT INTO log (msg) VALUES (NEW.msg);


GRANT insert
ON log_insert
TO web;
----snip----


Now as user 'web' I try the following:
webtempl=> insert into log_insert (msg) VALUES ('foo');
ERROR: permission denied for sequence log_id_seq
webtempl=>


It works after executing the following as the user owning the database:
GRANT update
ON log_id_seq
TO web;
---snip---

As user 'web':
webtempl=> insert into log_insert (msg) VALUES ('foo');
INSERT 1078083 1
webtempl=>



As I read
http://www.postgresql.org/docs/7.4/i...rivileges.html the
insert should
work without granting the update privilege.


version
----------------------------------------------------------------------
PostgreSQL 7.4.7 on i386-portbld-freebsd4.10, compiled by GCC 2.95.4


Thanks,
Nicolas

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

I tested this in 8.0.X and can confirm the failure. Seems the rule
permission checks for DEFAULT clauses is incorrect.

Perhaps the problem is that the DEFAULT action is external and not part
of the actual rewrite, meaning when we introduce new tables as part of
the rule we can do proper permission assignment but it seems we are
failing for DEFAULT clause actions.

---------------------------------------------------------------------------

Nicolas Rachinsky wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster