Re: BUG #1142: Problem with update permissions for view - Mailing list pgsql-bugs

Dear Tom,

On Fri, 2004-04-30 at 19:48, Tom Lane wrote:
> Arturs Zoldners <az@rpiva.lv> writes:
> > -- The following rule prevents user x to update public_data:
> > CREATE RULE log_public AS ON UPDATE TO private_data WHERE (new.a <>
> > old.a) DO INSERT INTO private_log (old_val, new_val) VALUES (old.a,
> > new.a);
>
> This is a known problem.  It's fixed for 7.5 but there seems no way to
> back-port the fix into existing release series (without forcing initdb).
>
> The error is essentially that the use of an INSERT command as the rule
> body causes the original view to be checked for INSERT rather than
> UPDATE permissions ...
>
>             regards, tom lane

I tried the same test with 7.5devel. The problem is solved, but...
I found another bug, which sounds very like the first one:

ERROR: permission denied for relation...

Here are sql statements (postgres is superuser, x is ordinal user):

--********************************************************************

SET SESSION AUTHORIZATION 'postgres';
SELECT version();
--PostgreSQL 7.5devel on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
--3.2.2 20030222 (Red Hat Linux 3.2.2-5)

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;
SET search_path = public, pg_catalog;
CREATE TABLE private_data (
    id serial NOT NULL,
    a integer
);
REVOKE ALL ON TABLE private_data FROM PUBLIC;
CREATE VIEW public_data AS
    SELECT private_data.id, private_data.a FROM private_data;
REVOKE ALL ON public_data FROM PUBLIC;

GRANT SELECT,RULE,UPDATE ON public_data TO x;

CREATE TABLE private_log (
    old_val integer,
    new_val integer
);
REVOKE ALL ON TABLE private_log FROM PUBLIC;
CREATE RULE on_update  AS ON UPDATE TO public_data DO INSTEAD UPDATE
private_data SET a = new.a WHERE (private_data.id = old.id);
SELECT pg_catalog.setval('private_data_id_seq', 3, true);
COMMENT ON SCHEMA public IS 'Standard public schema';

--

INSERT INTO private_data(id, a) VALUES (1, 1);
--INSERT 17832 1

UPDATE public_data SET a=2 WHERE id = 1;
--UPDATE 1

SET SESSION AUTHORIZATION 'x';
UPDATE public_data SET a=3 WHERE id = 1;
--UPDATE 1

SET SESSION AUTHORIZATION 'postgres';
CREATE RULE log_public AS ON UPDATE TO private_data WHERE (new.a <>
old.a) DO INSERT INTO private_log (old_val, new_val) VALUES (old.a,
new.a);

UPDATE public_data SET a=4 WHERE id = 1;
--UPDATE 1

SET SESSION AUTHORIZATION 'x';
UPDATE public_data SET a=5 WHERE id = 1;
--UPDATE 1

-- ^ This was the place, where 7.4.2 failed, now 7.5devel works ok,
--   and user x CAN update a view!
--   But, again, simple rule added to table private_log breaks things...
SET SESSION AUTHORIZATION 'postgres';
CREATE RULE silly_restriction AS ON INSERT TO private_log WHERE EXISTS
(SELECT 1 FROM private_log WHERE (old_val = 1) AND (new_val = 2)) DO
INSTEAD NOTHING;
--CREATE RULE
UPDATE public_data SET a=6 WHERE id = 1;
--UPDATE 1

SET SESSION AUTHORIZATION 'x';
UPDATE public_data SET a=7 WHERE id = 1;

--psql:bug.sql:61: ERROR:  permission denied for relation private_log

--***********************************************

I think, there is no reason for error again, because user x has
SELECT,RULE,UPDATE permissions for view public_data.


Best regards,
AZ

PS.

this bug was in "level 2":
update public_data (0) ->
update private_data (1) ->
insert private_log (2)

I found the same problem in "level 1" complex rules
involving deletion and inserts.

I wouldn't like to make a spam, but if you are interested in, I can
reduce them to dummy examples and send out, too.