Received: from einhorn.in-berlin.de (einhorn.in-berlin.de [192.109.42.8]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA03536 for ; Mon, 14 Feb 2000 13:36:50 -0500 (EST) (envelope-from mkresse@slyde.in-berlin.de) Received: from slyde (DialIN064.vr.in-berlin.de [193.175.22.192]) by einhorn.in-berlin.de (8.10.0.Beta10/8.10.0.Beta10/Debian 8.10.0.Beta10-1) with ESMTP id e1EIWWR16053 for ; Mon, 14 Feb 2000 19:32:33 +0100 Message-Id: <200002141832.e1EIWWR16053@einhorn.in-berlin.de> X-Authentication-Warning: einhorn.in-berlin.de: Host DialIN064.vr.in-berlin.de [193.175.22.192] claimed to be slyde From: "Martin Kresse" To: pgsql-sql@postgresql.org Date: Mon, 14 Feb 2000 19:36:07 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: "Permission denied" on public view Priority: normal X-mailer: Pegasus Mail for Win32 (v3.12a) Hi there, I am working on a db application that allows students to choose their courses, so I try to design everything securely. However, it seems to be that secure, that users don't have access on a certain view, which I don't understand. To track down and understand the problem, I devised a little model, which is supposed to store information about different cars of different postgres users: /* Main Table */ CREATE TABLE data(owner NAME, car TEXT); CREATE SEQUENCE datalog; CREATE RULE ins_data AS ON INSERT TO data DO SELECT NEXTVAL('datalog'); GRANT ALL ON datalog TO PUBLIC; /* Public View */ CREATE VIEW publicdata AS SELECT * FROM data WHERE owner = USER; CREATE RULE ins_publicdata AS ON INSERT TO publicdata DO INSTEAD INSERT INTO data(owner, car) VALUES(USER, new.car); GRANT SELECT, INSERT ON publicdata TO PUBLIC; The datalog sequence is used to detect and track changes in the data table. Everything works fine when I use a Postgres superuser, for example an insert like: INSERT INTO publicdata(car) VALUES('Ford'); However, when I try the exact same statement with a different user, I get the error message: ERROR: data: Permission denied. When I remove the ins_data rule, the INSERT works for the other users as well. Does the rule need access to the data table? Is this a bug or am I doing something wrong? I appreciate your help, Martin Kresse