Thread: BUG #4808: Rules on system catalogs are allowed but not executed.
The following bug has been logged online:
Bug reference: 4808
Logged by: Ian Turner
Email address: ian.turner@deshaw.com
PostgreSQL version: 8.3.6
Operating system: Ubuntu 8.10
Description: Rules on system catalogs are allowed but not executed.
Details:
It appears that (unlike with triggers), PostgreSQL will accept the creation
of rules on system catalogs, but these rules are not actually executed.
Consider this example:
testdb=# select rulename from pg_rules where rulename = 'badrule';
select rulename from pg_rules where rulename = 'badrule';
rulename
----------
(0 rows)
testdb=# create rule badrule as on insert to pg_class do also notify
changemaster;
create rule badrule as on insert to pg_class do also notify changemaster;
CREATE RULE
testdb=# select rulename from pg_rules where rulename = 'badrule'; select
rulename from pg_rules where rulename = 'badrule';
rulename
----------
badrule
(1 row)
testdb=# listen changemaster;
listen changemaster;
LISTEN
testdb=# notify changemaster;
notify changemaster;
NOTIFY
Asynchronous notification "changemaster" received from server process with
PID 30010.
testdb=# select relname from pg_class where relname = 'testtable';
select relname from pg_class where relname = 'testtable';
relname
---------
(0 rows)
testdb=# create table testtable ();
create table testtable ();
CREATE TABLE
testdb=# -- Note no notification received.
testdb=# select relname from pg_class where relname = 'testtable';
select relname from pg_class where relname = 'testtable';
relname
-----------
testtable
(1 row)
I would suggest blocking this kind of rule in the same way that triggers are
blocked:
testdb=# CREATE TRIGGER test_trigger
testdb-# BEFORE INSERT OR UPDATE on pg_class
testdb-# FOR EACH ROW EXECUTE PROCEDURE test_trigger();
CREATE TRIGGER test_trigger
BEFORE INSERT OR UPDATE on pg_class
FOR EACH ROW EXECUTE PROCEDURE test_trigger();
ERROR: permission denied: "pg_class" is a system catalog
"Ian Turner" <ian.turner@deshaw.com> writes:
> It appears that (unlike with triggers), PostgreSQL will accept the creation
> of rules on system catalogs, but these rules are not actually executed.
They're not honored by internal catalog updates, no.
regards, tom lane
On Wed, May 13, 2009 at 3:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Ian Turner" <ian.turner@deshaw.com> writes: >> It appears that (unlike with triggers), PostgreSQL will accept the creation >> of rules on system catalogs, but these rules are not actually executed. > > They're not honored by internal catalog updates, no. Shouldn't their creation be prevented in the first place, like triggers? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes:
> On Wed, May 13, 2009 at 3:36 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> They're not honored by internal catalog updates, no.
> Shouldn't their creation be prevented in the first place, like triggers?
Well, maybe --- normally I'd say that superusers shouldn't need training
wheels, but making it consistent with triggers is a legitimate argument.
Comparing the code, I notice that DefineRule isn't being very picky
about the relkind either; it looks like it'd let you create rules on
sequences or toast tables, neither of which seems tremendously sensible.
CreateTrigger explicitly restricts to plain tables, and what would make
sense if we are trying to make the behavior comparable is to restrict
rules to tables or views.
regards, tom lane