Re: Bugs with rules on views/tables: permission denied - Mailing list pgsql-bugs

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


> "Donald Fraser" <demolish@cwgsy.net> writes:
> > In addition to this observation I note that the same bugs apply to view=
s.
>=20
> 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.)
>=20
> 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 interpret=
ed=20
rules on views as follows:

1) Permissions on views grant the said USER the ability to perform the gran=
ted=20
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 in=
sert.

CREATE USER chkrule WITH  PASSWORD '' NOCREATEDB NOCREATEUSER;

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

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

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

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

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

CREATE RULE rul_vu_tbl_test_01 AS ON INSERT TO vu_tbl_test DO (UPDATE tbl_t=
est2 SET s_text =3D NEW.s_text WHERE id =3D NEW.id);
CREATE RULE rul_vu_tbl_test_02 AS ON INSERT TO vu_tbl_test DO INSTEAD (INSE=
RT INTO tbl_test1 (id, s_text) VALUES(test_func(NEW.id), NEW.s_text));

Scenario 1)
As USER chkrule do:
Bugs=3D> 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=3D> 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 the=
n the error at Scenario 1 goes away.

Regards
Donald Fraser.