Thread: BUG #2032: grant role bug
The following bug has been logged online: Bug reference: 2032 Logged by: Tom Email address: tom.zschockelt@flender.com PostgreSQL version: 8.1 Operating system: win2k Description: grant role bug Details: I've two login-roles ( usermgr, enduser ) each with a separate schema. I want to grant privileges on schema usermgr and some of it's objects to a group-role 'g_usermgr_use'. (user = usermgr ) 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; now I grant the group-role 'g_usermgr_use' to my login-user 'enduser'. GRANT g_usermgr_use to enduser; now I tried a select on one of the tables user = enduser select * from usermgr.a; Here I got the following error message : ERROR: permission denied for schema usermgr Then I tried a user = usermgr GRANT usage on SCHEMA usermgr to enduser; and again a user = enduser select * from usermgr.a; now I got the following error ERROR: permission denied for relation a It seems that the group-role permissions do not work properly.
"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
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
Tom.Zschockelt@flender.com writes:
> here is an 'real' output of psql in our test scenario.
I tried to duplicate this example, but it still works fine for me.
> 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)
This makes me suspicious, because you are evidently using an 8.0 (or
older) psql; 8.1's \du output does not look like that. While using an
old psql with a new backend shouldn't affect the behavior of GRANT,
I wonder whether this is a symptom of pilot error at another level.
Is it possible that you are talking to an 8.0 postmaster as one user
and an 8.1 postmaster as the other user? Then the two "testdb1"
databases wouldn't be the same database at all. You can use
"select version()" and "show data_directory" to positively confirm
the identity of the postmaster you are connected to.
> testdb1=> \dp+
> Access privileges for database "testdb1"
> Schema | Name | Type | Access privileges
> --------+------+------+-------------------
> (0 rows)
I believe this only shows stuff that is in your search path, which the
usermgr schema wouldn't be by default for enduser. Try
\dp usermgr.*
regards, tom lane