Re: Bugs with rules on views/tables: permission denied

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Donald Fraser" <demolish(at)cwgsy(dot)net>
Cc: "[ADMIN]" <pgsql-admin(at)postgresql(dot)org>
Sent: Monday, February 24, 2003 2:42 PM
Subject: Re: [ADMIN] Bugs with rules on views/tables: permission denied

> "Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> > In addition to this observation I note that the same bugs apply to views.
>
> What bugs? The original complaint was shown to be user error. (If you
> try to duplicate the problem using the example quoted in your mail, it
> works fine.)
>
> regards, tom lane

Sorry I didn't see any follow up emails on that one...
May be I haven't understood the documentation correctly either. I interpreted
rules on views as follows:

1) Permissions on views grant the said USER the ability to perform the granted
action on the view, for example SELECT, INSERT or UPDATE.
2) The rules of the view always run at the rule creator's permission access level.

Here is a simple example that fails with views, both in an update and an insert.

CREATE USER chkrule WITH PASSWORD '' NOCREATEDB NOCREATEUSER;

CREATE OR REPLACE FUNCTION test_func(int4) RETURNS int4 AS '
DECLARE
id ALIAS FOR $1;
ndosomething int4;
BEGIN
ndosomething := id;
RETURN ndosomething;
END; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
REVOKE ALL ON FUNCTION test_func(int4) FROM PUBLIC;

CREATE TABLE public.tbl_test1 (id int4 NOT NULL, s_text text NOT NULL, PRIMARY KEY (id)) WITHOUT OIDS;
REVOKE ALL ON TABLE public.tbl_test1 FROM PUBLIC;

CREATE TABLE public.tbl_test2 (id int4 NOT NULL, s_text text NOT NULL, PRIMARY KEY (id)) WITHOUT OIDS;
REVOKE ALL ON TABLE public.tbl_test1 FROM PUBLIC;

INSERT INTO tbl_test2(id, s_text) VALUES('1', 'testtext');

CREATE VIEW vu_tbl_test AS SELECT id, s_text FROM tbl_test1;
REVOKE ALL ON TABLE vu_tbl_test FROM PUBLIC;
GRANT SELECT, INSERT ON TABLE vu_tbl_test TO chkrule;

CREATE RULE rul_vu_tbl_test_01 AS ON INSERT TO vu_tbl_test DO (UPDATE tbl_test2 SET s_text = NEW.s_text WHERE id = NEW.id);
CREATE RULE rul_vu_tbl_test_02 AS ON INSERT TO vu_tbl_test DO INSTEAD (INSERT INTO tbl_test1 (id, s_text) VALUES(test_func(NEW.id), NEW.s_text));

Scenario 1)
As USER chkrule do:
Bugs=> INSERT INTO vu_tbl_test (id, s_text) VALUES('1','sometext');
ERROR: vu_tbl_test: permission denied

Scenario 2)
Now drop rule 1 as USER postgres
DROP RULE rul_vu_tbl_test_01 ON vu_tbl_test;

As USER chkrule do:
Bugs=> INSERT INTO vu_tbl_test (id, s_text) VALUES('1','sometext');
ERROR: test_func: permission denied

If you give the USER chkrule UPDATE permissions on the view vu_tbl_test then the error at Scenario 1 goes away.

Regards
Donald Fraser.