Postgres Pro
Downloads
Home   >   mailing lists

Thread: postgres not use table access permissions ?

postgres not use table access permissions ?

From
Partyka Robert
Date:
Hi,

I start yesterday CVS PostgreSQL server, and saw strange thing:
from user postgres:
# create database test;
CREATE
# \c test;
#create user bobson with password '1' nocreatedb nocreateuser;
CREATE
#create table a (a int4);
CREATE
#revoke all on a from public;
CHANGE

and now from user bobson after conecting to test database:
#insert into a values ('1');
INSERT 19104 1

hmmm... looks like bug. Or I miss something?

so next from user postgres:
#revoke all on a from bobson;
CHANGE

and from user bobson after connect:
#delete from a;
DELETE 1

Postgres ignore access permissions ?
BTW... in my pg_hba.conf
local            password
...

regards
Robert 'BoBsoN' Partyka

Re: postgres not use table access permissions ?

From
Tom Lane
Date:
Partyka Robert <bobson@saturn.alpha.pl> writes:
> #create user bobson with password '1' nocreatedb nocreateuser;
> CREATE
> #create table a (a int4);
> CREATE
> #revoke all on a from public;
> CHANGE
> and now from user bobson after conecting to test database:
> #insert into a values ('1');
> INSERT 19104 1

> hmmm... looks like bug. Or I miss something?

Oops.  Strange though, this looks like it must be a very long-standing
bug: aclinsert3 thinks it can delete any zero-permissions item from an
ACL array, whereas aclcheck has a hard-wired assumption that the world
item is always there.  Could we have missed this for this long?
        regards, tom lane

Re[2]: postgres not use table access permissions ?

From
Partyka Robert
Date:
> Partyka Robert <bobson@saturn.alpha.pl> writes:
> > #create user bobson with password '1' nocreatedb nocreateuser;
> > CREATE
> > #create table a (a int4);
> > CREATE
> > #revoke all on a from public;
> > CHANGE
> > and now from user bobson after conecting to test database:
> > #insert into a values ('1');
> > INSERT 19104 1
> 
> > hmmm... looks like bug. Or I miss something?
> 
> Oops.  Strange though, this looks like it must be a very long-standing
> bug: aclinsert3 thinks it can delete any zero-permissions item from an
> ACL array, whereas aclcheck has a hard-wired assumption that the world
> item is always there.  Could we have missed this for this long?

In 6.5.3 I've found other strange thing. When I give user INSERT, UPDATE
permissions such user can do DELETE without DELETE permissions so in fact
if I do 
# grant UPDATE, INSERT, SELECT on a to user1;
it was treat as:
# grant UPDATE, INSERT, DELETE, SELECT on a to user1;

Today I want to test it on lastest CVS, but ... you know ;)

regards
Robert 'BoBsoN' Partyka

Re: postgres not use table access permissions ?

From
Tom Lane
Date:
I wrote:
> Oops.  Strange though, this looks like it must be a very long-standing
> bug: aclinsert3 thinks it can delete any zero-permissions item from an
> ACL array, whereas aclcheck has a hard-wired assumption that the world
> item is always there.  Could we have missed this for this long?

Yup, we could've.  It looks like "revoke all from public" has been
broken clear back to Postgres95, if not longer.  Amazing that no one
noticed.  Anyway, fixed now.
        regards, tom lane

Re: Re[2]: postgres not use table access permissions ?

From
Tom Lane
Date:
Partyka Robert <bobson@saturn.alpha.pl> writes:
> if I do 
> # grant UPDATE, INSERT, SELECT on a to user1;
> it was treat as:
> # grant UPDATE, INSERT, DELETE, SELECT on a to user1;

Yeah.  The underlying permission set is actually "read, write, append"
(where write access also allows append).  So UPDATE and DELETE are
treated the same, and allowing them also allows INSERT.  This is
something that probably oughta be changed some day.  That'll doubtless
break some user applications, though, since the true permission set is
user-visible (try psql's \z command for example).
        regards, tom lane