|
----- Original Message ----- From: "Tom Lane" <[EMAIL PROTECTED]> To: "Donald Fraser" <[EMAIL PROTECTED]> Cc: "[ADMIN]" <[EMAIL PROTECTED]> Sent: Monday, February 24, 2003 2:42 PM Subject: Re: [ADMIN] Bugs with rules on views/tables: permission denied > "Donald Fraser" <[EMAIL PROTECTED]> 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.
|
