Thread: Unusual permissions behaviour

I have this problem in 7.1.3 - I can't confirm at the moment if it exists in
7.2.

I have already granted the 'au-dietclub' user delete and insert permissions
on the users_flags table at this point:

australia=> delete from users_flags;
DELETE 0
australia=> delete from users_flags where user_id=1;
ERROR:  users_flags: Permission denied.
australia=> \connect - chriskl
You are now connected as new user chriskl.
australia=# grant select on users_flags to "au-dietclub";
CHANGE
australia=# \connect - au-dietclub
You are now connected as new user au-dietclub.
australia=> delete from users_flags where user_id=1;
DELETE 0

Why do I get a permission denied when I qualify the DELETE statement???

Chris

> I have this problem in 7.1.3 - I can't confirm at the moment if
> it exists in 7.2.
>
> I have already granted the 'au-dietclub' user delete and insert
> permissions on the users_flags table at this point:
>
> australia=> delete from users_flags;
> DELETE 0
> australia=> delete from users_flags where user_id=1;
> ERROR:  users_flags: Permission denied.
> australia=> \connect - chriskl
> You are now connected as new user chriskl.
> australia=# grant select on users_flags to "au-dietclub";
> CHANGE
> australia=# \connect - au-dietclub
> You are now connected as new user au-dietclub.
> australia=> delete from users_flags where user_id=1;
> DELETE 0
>
> Why do I get a permission denied when I qualify the DELETE statement???
>
> Chris
>


The schema:

CREATE TABLE "users_flags" ("user_id" integer NOT NULL REFERENCES users_users(user_id) ON DELETE
CASCADE,"flag_id" integer NOT NULL REFERENCES medidiets_flags(flag_id) ON DELETE
CASCADE,Primary Key ("user_id", "flag_id")
);
CREATE  INDEX "users_flags_flag_id_idx" on "users_flags" using btree (
"flag_id" "
int4_ops" );

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Why do I get a permission denied when I qualify the DELETE statement???

IIRC, you need SELECT permission to reference the values of any fields
of the table.  If you don't have SELECT permission, the table should
be write-only to you; you shouldn't be able to learn things about its
contents by doing stuff like
begin;delete from foo where col = 1;-- observe # rows deletedrollback;-- now I know whether there is a row with col =
1
        regards, tom lane