Re: grant question
От | Andreas Wenk |
---|---|
Тема | Re: grant question |
Дата | |
Msg-id | 49A9D04A.90109@netzmeister-st-pauli.de обсуждение исходный текст |
Ответ на | Re: grant question ("Tena Sakai" <tsakai@gallo.ucsf.edu>) |
Список | pgsql-admin |
Tena Sakai schrieb: > Hi, > > Nah, I don't think that theory holds water... > > [tsakai@vixen ~]$ psql canon gjoslyn > Password for user gjoslyn: > Welcome to psql 8.3.6, the PostgreSQL interactive terminal. > > canon=> > canon=> \z gallo.gallo.unlinkcol1 > Access privileges for database "canon" > Schema | Name | Type | Access > privileges > > --------+------------+-------+---------------------------------------------------------- > gallo | unlinkcol1 | table | > {gbrush=arwdxt/gbrush,gjoslyn=r/gbrush,galloan=r/gbrush} > (1 row) > > -- as far as the table is concerned, it is readable > -- let me reproduce the error > canon=> select * from gallo.unlinkcol1 limit 4; > ERROR: permission denied for schema gallo > > I just feel that this is similar to a common unix file access > problem in that the file itself is readable, but one or more > directories in the path is not giving search permission. If > I read the error with such in mind, it makes more sense. > > \z command wouldn't let me look at the permission of the schema: > > canon=# \z gallo > Access privileges for database "canon" > Schema | Name | Type | Access privileges > --------+------+------+------------------- > (0 rows) > > What can I do? Hi, sure that does not work because the user gjoslyn from the group galloan is not allowed to use the schema gallo ... GRANT USAGE ON SCHEMA gallo to galloan; will help ... Cheers Andy > > Tena Sakai > tsakai@gallo.ucsf.edu > > > > -----Original Message----- > From: Tena Sakai > Sent: Sat 2/28/2009 3:04 PM > To: Tena Sakai; Andreas Wenk > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] grant question > > Hi, > > Maybe I found the underlying problem... > too psql, I typed: > > canon=# \dn gallo > > and it told me: > > List of schemas > Name | Owner > -------+------- > gallo | ysu > (1 row) > > Maybe the supersuer postgres is unable to grant select > on that table... But it told me it did. > > >> canon=# grant select on gallo.unlinkcol1 to galloan; > >> GRANT > > It doesn't make sense... I am confused. > > Tena Sakai > Tsakai@gallo.ucsf.edu > > > > -----Original Message----- > From: pgsql-admin-owner@postgresql.org on behalf of Tena Sakai > Sent: Sat 2/28/2009 2:55 PM > To: Andreas Wenk > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] grant question > > Hi Andy, > > Thank you for your walk through. > Here's what I did, emulating your guidance and spirit: > > -- about to create a new role > canon=# create role galloan; > CREATE ROLE > canon=# > canon=# \dg galloan > List of roles > Role name | Superuser | Create role | Create DB | Connections | Member of > -----------+-----------+-------------+-----------+-------------+----------- > galloan | no | no | no | no limit | {} > (1 row) > > -- grant a particular select on this role > canon=# grant select on gallo.unlinkcol1 to galloan; > GRANT > > -- put a user/role into galloan group/role > canon=# grant galloan to gjoslyn; > GRANT ROLE > canon=# > canon=# \dg gjoslyn > List of roles > Role name | Superuser | Create role | Create DB | Connections | > Member of > -----------+-----------+-------------+-----------+-------------+------------------ > gjoslyn | no | no | no | no limit | > {wetlab,galloan} > (1 row) > > --now test it as user gjoslyn > > [tsakai@vixen ~]$ psql canon gjoslyn > Password for user gjoslyn: > Welcome to psql 8.3.6, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > canon=> > canon=> select * from gallo.unlinkcol1 limit 5; > ERROR: permission denied for schema gallo > canon=> > -- it is having a problem with this schema called gallo > -- as you can see below, there is no problem with schema public > > canon=> select * from allele limit 5; > alleleid | markerid | value | datecreated | datereplaced > ----------+----------+-------+-------------------------+--------------------- > 3775273 | 1890310 | 138 | 2007-10-30 20:13:48.104 | 3000-01-01 12:00:00 > 3775276 | 1890310 | 136 | 2007-10-30 20:13:48.35 | 3000-01-01 12:00:00 > 3775278 | 1890310 | 122 | 2007-10-30 20:13:48.731 | 3000-01-01 12:00:00 > 3775280 | 1890310 | 142 | 2007-10-30 20:13:49.358 | 3000-01-01 12:00:00 > 3775282 | 1890310 | 124 | 2007-10-30 20:13:49.86 | 3000-01-01 12:00:00 > (5 rows) > > So, I don't know how to cure this problem. > Any hints, poiters are appreciated. > > Regards, > > Tena Sakai > tsakai@gallo.ucsf.edu > > > > -----Original Message----- > From: Andreas Wenk [mailto:a.wenk@netzmeister-st-pauli.de] > Sent: Sat 2/28/2009 1:01 PM > To: Tena Sakai > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] grant question > > > Tena Sakai schrieb: > > Thank you, Scott, for your reply. > > > > > Two problems. 1: you don't grant select on schemas, you grant it on > > > tables. 2: case folding. If you're gonna use a name "schema_Z" then > > > you have to quote it, because it's mixed case, not all lower. > > > > > You need to grant it for each table. > > > > In actual command issued, there is no case mixing. I wanted > > to emphasize the argument was a schema name, not a table name. > > But this means as new tables get created in the schema, a set > > of new commands must be issued? > > > > > Note that instead of granting it to a user, you should grant it > > > to a role, then give membership to that role to the user. > > > > That sounds like a good idea. Would you mind showing an exmple? > > Hi Tena, > > -- your user role > roletest=# CREATE ROLE tena LOGIN; > CREATE ROLE > -- a group role > roletest=# CREATE ROLE musicians; > CREATE ROLE > -- put tena 'in' the group role > roletest=# GRANT musicians to tena; > GRANT ROLE > > -- connect to roletest a user tena > roletest=# \c roletest tena > You are now connected to database "roletest" as user "tena". > roletest=> select * from test; > ERROR: permission denied for relation test > STATEMENT: select * from test; > ERROR: permission denied for relation test > > -- grant SELECT right as superuser in roletest > roletest=> \c roletest postgres > You are now connected to database "roletest" as user "postgres". > roletest=# GRANT SELECT on test to musicians; > GRANT > roletest=# \c roletest tena > You are now connected to database "roletest" as user "tena". > roletest=> SELECT * FROM test; > id | value > ----+------- > (0 rows) > > Cheers > > Andy > > -- > St.Pauli - Hamburg - Germany > > Andreas Wenk > > > > Regards, > > > > Tena Sakai > > tsakai@gallo.ucsf.edu > > > > > > -----Original Message----- > > From: Scott Marlowe [mailto:scott.marlowe@gmail.com] > > Sent: Sat 2/28/2009 12:04 PM > > To: Tena Sakai > > Cc: pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] grant question > > > > On Sat, Feb 28, 2009 at 12:56 PM, Tena Sakai <tsakai@gallo.ucsf.edu> > wrote: > > > Hi Everybody, > > > > > > I want to issue a command: > > > > > > grant select on schema_Z to user_a; > > > > > > so that the user_a can look at all tables in schema_Z. > > > Sadly, what I get is: > > > ERROR: relation "schema_Z" does not exist > > > > Two problems. 1: you don't grant select on schemas, you grant it on > > tables. 2: case folding. If you're gonna use a name "schema_Z" then > > you have to quote it, because it's mixed case, not all lower. > > > > > I tried: > > > > > > grant select on schema_Z.* to user_a; > > > > Sorry no wildcarding on grant (At least not yet). You need to grant > > it for each table. Note that instead of granting it to a user, you > > should grant it to a role, then give membership to that role to the > > user. > > > > > > > >
В списке pgsql-admin по дате отправления: