Обсуждение: problem with GRANT postgres 8.0.4
There are two tables article_reviewers Column | Type | Modifiers -------------+---------+----------- article_id | integer | not null reviewer_id | integer | not null Foreign-key constraints: "$1" FOREIGN KEY (reviewer_id) REFERENCES reviewers(person_id) reviewers Column | Type | Modifiers -----------+---------+----------- person_id | integer | not null status | text | keywords | text | Indexes: "reviewers_pkey" PRIMARY KEY, btree (person_id) Foreign-key constraints: "$1" FOREIGN KEY (person_id) REFERENCES persons(id) When I try to execute sql statement: INSERT INTO article_reviewers (article_id, reviewer_id) VALUES (876,569); ERROR: permission denied for relation reviewers KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x WHERE "person_id" = $1 FOR UPDATE OF x" In person table there is ofcourse person with id=569. User is super user and I did GRANT ALL on ALL TABLES : public | article_reviewers | table | {control=r/control,balcer=arwdRxt/control} public | articles | table | {control=r/control,balcer=arwdRxt/control} public | persons | table | {control=r/control,balcer=arwdRxt/control} What is wrong, sorry if this is stupid question. Jacek
Jacek Balcerski wrote: > ERROR: permission denied for relation reviewers > KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x > WHERE "person_id" = $1 FOR UPDATE OF x" > > In person table there is ofcourse person with id=569. > User is super user and I did GRANT ALL on ALL TABLES : > public | article_reviewers | table | > {control=r/control,balcer=arwdRxt/control} > public | articles | table | > {control=r/control,balcer=arwdRxt/control} > public | persons | table | > {control=r/control,balcer=arwdRxt/control} I don't see table "reviewers" in this list, which is the table the error statement mentions. -- Richard Huxton Archonet Ltd
Don't forget to cc: the list when replying Jacek Balcerski wrote: > Richard Huxton napisał(a): > >> Jacek Balcerski wrote: >> >>> ERROR: permission denied for relation reviewers >>> KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x >>> WHERE "person_id" = $1 FOR UPDATE OF x" >>> >>> In person table there is ofcourse person with id=569. >>> User is super user and I did GRANT ALL on ALL TABLES : >>> public | article_reviewers | table | >>> {control=r/control,balcer=arwdRxt/control} >>> public | articles | table | >>> {control=r/control,balcer=arwdRxt/control} >>> public | persons | table | >>> {control=r/control,balcer=arwdRxt/control} >> >> I don't see table "reviewers" in this list, which is the table the >> error statement mentions. > > > public | reviewers | table | > {control=r/control,balcer=arwdRxt/control} > I'am serching archives for an clear answer right now, but if any of you > would have time to explain > what I did wrong it would be helpful :) I'm guessing you are running as user "control" with only read permissions for table "reviewers". If you look at the error message... ERROR: permission denied for relation reviewers KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x WHERE "person_id" = $1 FOR UPDATE OF x" This is the foreign-key check. In versions before 8.1 this was handled by taking a SELECT ... FOR UPDATE lock which means you need the "UPDATE" permission too. To quote the manuals: "UPDATE Allows UPDATE of any column of the specified table. SELECT ... FOR UPDATE also requires this privilege (besides the SELECT privilege). For sequences, this privilege allows the use of the nextval and setval functions." HTH -- Richard Huxton Archonet Ltd
Richard Huxton napisał(a): > > Don't forget to cc: the list when replying > > Jacek Balcerski wrote: > >> Richard Huxton napisał(a): >> >>> Jacek Balcerski wrote: >>> >>>> ERROR: permission denied for relation reviewers >>>> KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x >>>> WHERE "person_id" = $1 FOR UPDATE OF x" >>>> >>>> In person table there is ofcourse person with id=569. >>>> User is super user and I did GRANT ALL on ALL TABLES : >>>> public | article_reviewers | table | >>>> {control=r/control,balcer=arwdRxt/control} >>>> public | articles | table | >>>> {control=r/control,balcer=arwdRxt/control} >>>> public | persons | table | >>>> {control=r/control,balcer=arwdRxt/control} >>> >>> >>> I don't see table "reviewers" in this list, which is the table the >>> error statement mentions. >> >> >> >> public | reviewers | table | >> {control=r/control,balcer=arwdRxt/control} >> I'am serching archives for an clear answer right now, but if any of >> you would have time to explain >> what I did wrong it would be helpful :) > > > I'm guessing you are running as user "control" with only read > permissions for table "reviewers". If you look at the error message... > > ERROR: permission denied for relation reviewers > KONTEKST: SQL statement "SELECT 1 FROM ONLY "public"."reviewers" x > WHERE "person_id" = $1 FOR UPDATE OF x" > > This is the foreign-key check. In versions before 8.1 this was handled > by taking a SELECT ... FOR UPDATE lock which means you need the > "UPDATE" permission too. To quote the manuals: > > "UPDATE > Allows UPDATE of any column of the specified table. SELECT ... FOR > UPDATE also requires this privilege (besides the SELECT privilege). > For sequences, this privilege allows the use of the nextval and setval > functions." > > HTH > -- > Richard Huxton > Archonet Ltd > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > Well the proble is I'am running as user balcer... Jacek