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:
>
> The following bug has been logged online:
>
> Bug reference: 1504
> Logged by: Nicolas Rachinsky
> Email address: nr@rachinsky.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/interactive/rules-privileges.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@postgresql.org)
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.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