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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
