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 DOSELECT 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
INSTEADINSERT 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