[BUGS] BUG #1142: Problem with update permissions for view

[PostgreSQL Bugs List]

The following bug has been logged online:

Bug reference:      1142
Logged by:          Arturs Zoldners

Email address:      [EMAIL PROTECTED]

PostgreSQL version: 7.4

Operating system:   Linux (RedHat 9 distrib.)

Description:        Problem with update permissions for view

Details: 

>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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])