X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 89E6B32A2E9 for ; Fri, 1 Oct 2004 14:56:02 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01198-06 for ; Fri, 1 Oct 2004 13:55:54 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 70DBF32A2E7 for ; Fri, 1 Oct 2004 14:55:57 +0100 (BST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id E88003535D; Fri, 1 Oct 2004 06:55:38 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id E315935331; Fri, 1 Oct 2004 06:55:38 -0700 (PDT) Date: Fri, 1 Oct 2004 06:55:38 -0700 (PDT) From: Stephan Szabo To: Duane Winner Cc: pgsql-admin@postgresql.org, "Arend P. van der Veen" , "David J. Neu" Subject: Re: grant command: wildcard on tables? In-Reply-To: <415C27DC.80407@att.net> Message-ID: <20041001065334.J51510@megazone.bigpanda.com> References: <415C27DC.80407@att.net> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/13 X-Sequence-Number: 15007 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"