Re: BUG #2032: grant role bug - Mailing list pgsql-bugs

Hi Tom,

here is an 'real' output of psql in our test scenario.


psql-output as user : enduser

testdb1=> \du
                           List of users
 User name | User ID |         Attributes         |     Groups
-----------+---------+----------------------------+-----------------
 allgemein |   22584 |                            | {g_usermgr_use}
 enduser   |   24364 |                            | {g_usermgr_use}
 postgres  |      10 | superuser, create database |
 usermgr   |   22583 |                            |
(4 rows)

psql-output as user : usermgr

testdb1=> grant usage on schema usermgr to g_usermgr_use;
GRANT
testdb1=> grant select on table usermgr.a to g_usermgr_use;
GRANT
testdb1=> grant select on table usermgr.b to g_usermgr_use;
GRANT

testdb1=> \dn+
                                                 List of schemas
        Name        |  Owner   |              Access privileges    |
          Description
--------------------+----------+----------------------------------------------+-
---------------------------------
 enduser            | enduser  |    |
 information_schema | postgres | {postgres=UC/postgres,=U/postgres}    |
 pg_catalog         | postgres | {postgres=UC/postgres,=U/postgres}    |
System catalog schema
 pg_toast           | postgres |    |
Reserved schema for TOAST tables
 public             | postgres | {postgres=UC/postgres,=UC/postgres}    |
Standard public schema
 usermgr            | usermgr  |
{usermgr=UC/usermgr,g_usermgr_use=U/usermgr} |
(6 rows)

testdb1=> \dp
                  Access privileges for database "testdb1"
 Schema  | Name | Type  |                 Access privileges
---------+------+-------+---------------------------------------------------
 usermgr | a    | table |
{usermgr=arwdRxt/usermgr,g_usermgr_use=r/usermgr}
 usermgr | b    | table |
{usermgr=arwdRxt/usermgr,g_usermgr_use=r/usermgr}
(2 rows)

now I tried a select on table a as user enduser

testdb1=> select * from usermgr.a;
ERROR:  permission denied for schema usermgr
testdb1=>

testdb1=> \dp+
 Access privileges for database "testdb1"
 Schema | Name | Type | Access privileges
--------+------+------+-------------------
(0 rows)


Did I miss anything ? Is it neccessary to activate the role-membership or
is there any other precondition that must be fullfilled
before the right privileges can be handled ?

Thanks

Tom





Tom Lane <tgl@sss.pgh.pa.us>
09.11.2005 16:38


        An:     "Tom" <tom.zschockelt@flender.com>
        Kopie:  pgsql-bugs@postgresql.org
        Thema:  Re: [BUGS] BUG #2032: grant role bug


"Tom" <tom.zschockelt@flender.com> writes:
> GRANT usage on SCHEMA usermgr to g_usermgr_use;
> GRANT select on table a to g_usermgr;
> GRANT select on table b to g_usermgr;

Perhaps you meant to grant those select privileges to g_usermgr_use ?

Also, are you sure you were granting privileges on usermgr.a, and not
some other table named A in a different schema?

If you want us to believe this doesn't work, you'll need to send an
exact transcript of what you did (copy and paste from a terminal window
works well), not a rather handwavy description that might or might not
contain errors.

                                                 regards, tom lane