grant command: wildcard on tables?

[Duane Winner]

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Stephan Szabo]

Post by Duane Winner
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.
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?
mydatabase=# grant select,update,insert,delete on schemaA.* to myuser;
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.

Post by Duane Winner
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.
mydatabase=# grant select,update,insert,delete on schemaB.table-two
to myuser;

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster