pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
grant command: wildcard on tables?
2+ messages / 2 participants
[nested] [flat]

* grant command: wildcard on tables?
@ 2004-09-30 15:35 Duane Winner <duanewinner@att.net>
  2004-10-01 13:55 ` Re: grant command: wildcard on tables? Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Duane Winner @ 2004-09-30 15:35 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Arend P. van der Veen <apvanderveen@att.net>; David J. Neu <djneu@att.net>

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


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;
   
and I get:
    ERROR:  syntax error at or near "-" at character 46


Hopefully there are solutions for both of these problems. And I figure 
if I get a solution for problem #1, it will take care of problem #2. Is 
there a wildcard syntax solution for #1? Is there a delimiter syntax 
solution for #2?

Thanks for any info,
Duane Winner




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: grant command: wildcard on tables?
  2004-09-30 15:35 grant command: wildcard on tables? Duane Winner <duanewinner@att.net>
@ 2004-10-01 13:55 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2004-10-01 13:55 UTC (permalink / raw)
  To: Duane Winner <duanewinner@att.net>; +Cc: pgsql-admin@postgresql.org, "Arend P. van der Veen" <apvanderveen@att.net>; David J. Neu <djneu@att.net>

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"



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-10-01 13:55 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-30 15:35 grant command: wildcard on tables? Duane Winner <duanewinner@att.net>
2004-10-01 13:55 ` Stephan Szabo <sszabo@megazone.bigpanda.com>

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