Re: BUG #2032: grant role bug

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(at)sss(dot)pgh(dot)pa(dot)us>
09.11.2005 16:38

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

"Tom" <tom(dot)zschockelt(at)flender(dot)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