Thread: 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