pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedGrant Question
12+ messages / 6 participants
[nested] [flat]
* Grant Question
@ 2003-05-12 21:11 Travis Whitton <whitton@atlantic.net>
0 siblings, 2 replies; 12+ messages in thread
From: Travis Whitton @ 2003-05-12 21:11 UTC (permalink / raw)
To: pgsql-admin
Hi - I'm new to postgreSQL, and I'm having some issues with using GRANT.
I'm looking for a way to grant privileges on all tables in a given database
to a user. I can accomplish this using individual grant statements for each
table; however, a global grant is not working for me. Here's an example.
template1=# CREATE DATABASE dummy;
CREATE DATABASE
edit=# \c dummy;
You are now connected to database dummy.
dummy=# CREATE TABLE test (id int);
CREATE TABLE
dummy=# CREATE TABLE test2 (id int);
CREATE TABLE
dummy=# GRANT ALL ON DATABASE dummy TO devel;
GRANT
dummy=# \dp
Access privileges for database "dummy"
Schema | Table | Access privileges
--------+-------+-------------------
public | test |
public | test2 |
(2 rows)
No privileges...
dummy=# GRANT ALL ON test TO devel;
GRANT
dummy=# \dp
Access privileges for database "dummy"
Schema | Table | Access privileges
--------+-------+------------------------------------
public | test | {=,postgres=arwdRxt,devel=arwdRxt}
public | test2 |
(2 rows)
This works but only by referencing a specific table. Any
help would be appreciated.
Thanks,
Travis Whitton <whitton@atlantic.net>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Grant Question
@ 2003-05-14 17:35 Bruno Wolff III <bruno@wolff.to>
parent: Travis Whitton <whitton@atlantic.net>
1 sibling, 0 replies; 12+ messages in thread
From: Bruno Wolff III @ 2003-05-14 17:35 UTC (permalink / raw)
To: Travis Whitton <whitton@atlantic.net>; +Cc: pgsql-admin
On Mon, May 12, 2003 at 21:11:47 +0000,
Travis Whitton <whitton@atlantic.net> wrote:
>
> dummy=# GRANT ALL ON DATABASE dummy TO devel;
> GRANT
Granting all on a database allows the grantee to create schemas and
temporary tables in that database. It doesn't grant access to any
tables in that database. To do that you want to write a script that
does a query to get a list of table names and then issue grant
statments for those tables.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Grant Question
@ 2003-05-20 20:57 kevin <kevin@mtel.co.uk>
parent: Travis Whitton <whitton@atlantic.net>
1 sibling, 0 replies; 12+ messages in thread
From: kevin@mtel.co.uk @ 2003-05-20 20:57 UTC (permalink / raw)
To: pgsql-admin
Travis Whitton <whitton@atlantic.net> wrote in message news:<grub.ath.cx/">slrnbc045f.42h.whitton@grub.ath.cx>...
> Hi - I'm new to postgreSQL, and I'm having some issues with using GRANT.
> I'm looking for a way to grant privileges on all tables in a given database
> to a user. I can accomplish this using individual grant statements for each
> table; however, a global grant is not working for me. Here's an example.
>
> template1=# CREATE DATABASE dummy;
> CREATE DATABASE
>
> edit=# \c dummy;
> You are now connected to database dummy.
>
> dummy=# CREATE TABLE test (id int);
> CREATE TABLE
>
> dummy=# CREATE TABLE test2 (id int);
> CREATE TABLE
>
> dummy=# GRANT ALL ON DATABASE dummy TO devel;
> GRANT
>
> dummy=# \dp
> Access privileges for database "dummy"
> Schema | Table | Access privileges
> --------+-------+-------------------
> public | test |
> public | test2 |
> (2 rows)
>
> No privileges...
>
> dummy=# GRANT ALL ON test TO devel;
> GRANT
>
> dummy=# \dp
> Access privileges for database "dummy"
> Schema | Table | Access privileges
> --------+-------+------------------------------------
> public | test | {=,postgres=arwdRxt,devel=arwdRxt}
> public | test2 |
> (2 rows)
>
> This works but only by referencing a specific table. Any
> help would be appreciated.
>
> Thanks,
> Travis Whitton <whitton@atlantic.net>
one solution is to create a user group with access privileges that you
desire to grant (say 'admin' is anything, 'users' can only read,
insert, update and delete).
You then grant privilege to that group only and maintain users in the
group.
I am not familiar with the schema that appears first(?) in 7.3, but
examine the admin help on http://www.postgresql.org/docs to see if
this offers a more direct approach.
clearly this (group) approach won't help you save time if the tables
already exist and you have to grant individually, but you can always
\dt into a file and edit it into sql to grant using search and
replace. This approach will save you having to do this again for
another user.
Good luck.
Kev.
^ permalink raw reply [nested|flat] 12+ messages in thread
* grant question
@ 2009-02-28 19:56 Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Tena Sakai @ 2009-02-28 19:56 UTC (permalink / raw)
To: pgsql-admin
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
I tried:
grant select on schema_Z.* to user_a;
and response was:
ERROR: syntax error at or near "to"
LINE 1: grant select on schema_Z.* to user_a;
Please tell me what I command I should issue instead
to get what I want done?
Thank you in advance.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-02-28 20:04 Scott Marlowe <scott.marlowe@gmail.com>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Scott Marlowe @ 2009-02-28 20:04 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-02-28 20:34 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tena Sakai @ 2009-02-28 20:34 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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?
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-02-28 21:01 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Wenk @ 2009-02-28 21:01 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; +Cc: pgsql-admin
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.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-02-28 22:55 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
0 siblings, 1 reply; 12+ messages in thread
From: Tena Sakai @ 2009-02-28 22:55 UTC (permalink / raw)
To: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>; +Cc: pgsql-admin
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.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-02-28 23:04 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Tena Sakai @ 2009-02-28 23:04 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; Andreas Wenk <a.wenk@netzmeister-st-pauli.de>; +Cc: pgsql-admin
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.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-02-28 23:41 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Tena Sakai @ 2009-02-28 23:41 UTC (permalink / raw)
To: Tena Sakai <tsakai@gallo.ucsf.edu>; Tena Sakai <tsakai@gallo.ucsf.edu>; Andreas Wenk <a.wenk@netzmeister-st-pauli.de>; +Cc: pgsql-admin
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?
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.
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-03-01 00:01 Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
parent: Tena Sakai <tsakai@gallo.ucsf.edu>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Wenk @ 2009-03-01 00:01 UTC (permalink / raw)
To: 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.
> >
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: grant question
@ 2009-03-01 00:04 Tena Sakai <tsakai@gallo.ucsf.edu>
parent: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
0 siblings, 0 replies; 12+ messages in thread
From: Tena Sakai @ 2009-03-01 00:04 UTC (permalink / raw)
To: Andreas Wenk <a.wenk@netzmeister-st-pauli.de>; pgsql-admin
Hi Andy,
Many thanks! That is the silver bullet I needed.
Hoorey!
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: Andreas Wenk [mailto:a.wenk@netzmeister-st-pauli.de]
Sent: Sat 2/28/2009 4:01 PM
To: Tena Sakai; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] grant question
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.
> >
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2009-03-01 00:04 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-12 21:11 Grant Question Travis Whitton <whitton@atlantic.net>
2003-05-14 17:35 ` Bruno Wolff III <bruno@wolff.to>
2003-05-20 20:57 ` kevin <kevin@mtel.co.uk>
2009-02-28 19:56 grant question Tena Sakai <tsakai@gallo.ucsf.edu>
2009-02-28 20:04 ` Re: grant question Scott Marlowe <scott.marlowe@gmail.com>
2009-02-28 20:34 ` Re: grant question Tena Sakai <tsakai@gallo.ucsf.edu>
2009-02-28 21:01 ` Re: grant question Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
2009-02-28 22:55 ` Re: grant question Tena Sakai <tsakai@gallo.ucsf.edu>
2009-02-28 23:04 ` Re: grant question Tena Sakai <tsakai@gallo.ucsf.edu>
2009-02-28 23:41 ` Re: grant question Tena Sakai <tsakai@gallo.ucsf.edu>
2009-03-01 00:01 ` Re: grant question Andreas Wenk <a.wenk@netzmeister-st-pauli.de>
2009-03-01 00:04 ` Re: grant question Tena Sakai <tsakai@gallo.ucsf.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox