Обсуждение: Permissions problem

Hi,

I am having problems with permissions in postgres. I am using version 7.1.3 of
Postgres running on RedHat 7.2. 

I create the table "accounts" and revoke all permissions for the PUBLIC user:  
accounts | {"=","dcl=arwR"}

However, any user can make a select or update in the table "accounts".

Can anybody help me?!

Thanks a lot.

noy <noyda@isoco.com> writes:
> However, any user can make a select or update in the table "accounts".

Surely not.

test71=# select version();                            version
------------------------------------------------------------------PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by
GCC2.95.3
 
(1 row)

test71=# create user foo;
CREATE USER
test71=# create user bar;
CREATE USER
test71=# \c - foo
You are now connected as new user foo.
test71=> create table accounts (f1 int);
CREATE
test71=> insert into accounts values(1);
INSERT 1587112 1
test71=> revoke all on accounts from public;
CHANGE
test71=> \z accounts
Access privileges for database "test71" Table   | Access privileges
----------+-------------------accounts | {"=","foo=arwR"}
(1 row)

test71=> select * from accounts;f1
---- 1
(1 row)

test71=> \c - bar
You are now connected as new user bar.
test71=> select * from accounts;
ERROR:  accounts: Permission denied.
test71=> update accounts set f1 = 2;
ERROR:  accounts: Permission denied.
test71=> 


Perhaps your "any user" is actually a superuser?
        regards, tom lane

Hi,

Thanks for your help... I had problem with the user's permissions because I
created the users using the shell scripts:

createuser -a login -P

and users created in that way have all the privileges. The man page no makes
references to this. -a, --adduser  Allows the new user to create other users.


Thanks.



Tom Lane wrote:
> 
> noy <noyda@isoco.com> writes:
> > However, any user can make a select or update in the table "accounts".
> 
> Surely not.
> 
> test71=# select version();
>                              version
> ------------------------------------------------------------------
>  PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
> (1 row)
> 
> test71=# create user foo;
> CREATE USER
> test71=# create user bar;
> CREATE USER
> test71=# \c - foo
> You are now connected as new user foo.
> test71=> create table accounts (f1 int);
> CREATE
> test71=> insert into accounts values(1);
> INSERT 1587112 1
> test71=> revoke all on accounts from public;
> CHANGE
> test71=> \z accounts
> Access privileges for database "test71"
>   Table   | Access privileges
> ----------+-------------------
>  accounts | {"=","foo=arwR"}
> (1 row)
> 
> test71=> select * from accounts;
>  f1
> ----
>   1
> (1 row)
> 
> test71=> \c - bar
> You are now connected as new user bar.
> test71=> select * from accounts;
> ERROR:  accounts: Permission denied.
> test71=> update accounts set f1 = 2;
> ERROR:  accounts: Permission denied.
> test71=>
> 
> Perhaps your "any user" is actually a superuser?
> 
>                         regards, tom lane

noy <noyda@isoco.com> writes:
> Thanks for your help... I had problem with the user's permissions because I
> created the users using the shell scripts:
> createuser -a login -P
> and users created in that way have all the privileges. The man page no makes
> references to this. -a, --adduser  Allows the new user to create other users.

Good point.  It's explained on the man page for the underlying CREATE
USER command, but the page for the createuser script needs to say it
too.  I've committed a fix for 7.2.1.
        regards, tom lane