Re: BUG #1142: Problem with update permissions for view

Hi!

I submited this bug from web interface 27 Apr 2004, but the details were
lost.
The original message was:

>From PostgreSQL 7.4.2 Documentation (34.4. Rules and Privileges):
"...user only needs the required privileges for the tables/views that he
names explicitly in his queries..."

However, in this example this is not so:

SET SESSION AUTHORIZATION 'postgres';

SELECT version();
--(PostgreSQL 7.4.2 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;

COPY private_data (id, a) FROM stdin;
1    1
\.

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', 1, true);

COMMENT ON SCHEMA public IS 'Standard public schema';

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

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)

-- The following rule prevents user x to update public_data:
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;

The error message from last input line is:

ERROR:  permission denied for relation public_data,

However, user x _has_ SELECT, RULE, UPDATE permissions on public_data.

Best regards,
AZ