Thread: BUG #1142: Problem with update permissions for view
The following bug has been logged online:
Bug reference: 1142
Logged by: Arturs Zoldners
Email address: az@rpiva.lv
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
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
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
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.
Arturs Zoldners <az@rpiva.lv> writes:
> I found another bug, which sounds very like the first one:
Hm, looks like no one ever experimented with permissions checking for
subqueries in a rule WHERE clause before. Fixed - thanks for the report!
regards, tom lane