Обсуждение: "Permission denied" on public view
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
> 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? Anything seems right for me. And I cannot reproduce this error in the current developers tree (it works as it should). Which version of PG are you using? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
> > 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? > > Anything seems right for me. And I cannot reproduce this > error in the current developers tree (it works as it should). > > Which version of PG are you using? Today, I installed PG 6.5.3, which behaves exactly the same. Prior I have been using PG 6.5.1. Martin