Skip site navigation (1) Skip section navigation (2)

"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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group