Re: grant command: wildcard on tables? - Mailing list pgsql-admin

From Stephan Szabo
Subject Re: grant command: wildcard on tables?
Date
Msg-id 20041001065334.J51510@megazone.bigpanda.com
Whole thread Raw
In response to grant command: wildcard on tables?  (Duane Winner <duanewinner@att.net>)
List pgsql-admin
On Thu, 30 Sep 2004, Duane Winner wrote:

> Hello,
>
> We have a database with data loaded and now I need to GRANT
> SELECT,UPDATE,INSERT,DELETE to all tables in 4 different schemas for a
> specific postgresql user account.
>
> I have two problems:
>
> 1) One of these schemas has 75 tables. Is there a way to do the GRANT
> command with a wildcard to give the privileges to the user in one fell
> swoop?
>
> I try:
>
>     mydatabase=# grant select,update,insert,delete on schemaA.* to myuser;
>
> and I get:
>     ERROR:  relation "schemaA.*" does not exist

There isn't a wildcard syntax for that currently, although you can fake it
with a function that gets all the tables in schemaA and grants to each of
them. There should be examples in the mailing list archives since this
gets asked fairly frequently.

> 2) The other three schemas only have several tables each, so I can just
> run the GRANT command on each schema.table individually, however one
> table has a name with a hyphen it, and this causes an error.
>
> I try:
>
>     mydatabase=# grant select,update,insert,delete on schemaB.table-two
> to myuser;

I think that should be: schemaB."table-two"

pgsql-admin by date:

Previous
From: JEDIDIAH
Date:
Subject: Re: [HACKERS] Point in Time Recovery
Next
From: Tom Lane
Date:
Subject: Re: securing template1