"Permission denied" on public view

From: "Martin Kresse" <mkresse(at)slyde(dot)in-berlin(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: "Permission denied" on public view
Date: 2000-02-14 18:36:07
Message-ID: 200002141832.e1EIWWR16053@einhorn.in-berlin.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jan Wieck 2000-02-14 18:50:46 Re: [SQL] "Permission denied" on public view
Previous Message Wim Kerkhoff 2000-02-14 18:23:58 bug in translate(text,from,to) ?