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 7AE20D1B16A for ; Mon, 26 Jul 2004 16:26:04 -0300 (ADT) 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 66324-06 for ; Mon, 26 Jul 2004 19:25:56 +0000 (GMT) Received: from ptb-relay02.plus.net (ptb-relay02.plus.net [212.159.14.213]) by svr1.postgresql.org (Postfix) with ESMTP id 53197D1B1D0 for ; Mon, 26 Jul 2004 16:25:52 -0300 (ADT) Received: from [212.159.87.229] (helo=thelabyrinth.force9.co.uk) by ptb-relay02.plus.net with esmtp (Exim) id 1BpB6e-00084l-6c; Mon, 26 Jul 2004 19:25:52 +0000 Received: from ruth.localdomain (ruth.localdomain [192.168.0.1]) by thelabyrinth.force9.co.uk (Postfix) with ESMTP id 65B0E30060; Mon, 26 Jul 2004 19:26:55 +0100 (BST) Date: Mon, 26 Jul 2004 20:26:30 +0100 (BST) From: Sam Barnett-Cormack X-X-Sender: sdb@localhost.localdomain To: Randall Perry Cc: olly@lfix.co.uk, pgsql-admin@postgresql.org Subject: Re: How do I grant access to entire database at In-Reply-To: Message-ID: References: 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: 200407/296 X-Sequence-Number: 14364 On Mon, 26 Jul 2004, Randall Perry wrote: > Thanks, I'll use it. > > But, if the developer's are listening -- this is really obtuse. MySQL > administration is much easier. Please consider simplifying the GRANT process > for future revs. I do agree with this, actually - in fact, let me expand. GRANT out to have a varying degree of granularity, IMO. So you can specify individual sequences or tables, a glob (handy for table+sequence), possibly an intelligent way of including anything requisite for a given table, a whole DB, a whole namespace, a whole cluster (although then you could just make the user a superuser), all of the members of a DB/namespace that are of a certain type (say, tables+sequences, or functions-only, or whatever). This isn't just useful for easing the way one grants access to prevent unauthorised access, but also for creating users that prevent an individual from doing things accidentally - so a given developer might have several accounts with different privs - a bit like the way we only su to root, not log in as root. It's one of these little niggles that, for me, prevents Postgres being unassailably the best FOSS database. > on 7/18/04 4:41 PM, Oliver Elphick at olly@lfix.co.uk wrote: > > > On Sun, 2004-07-18 at 20:52, Randall Perry wrote: > >> This is a pain. Couldn't we gave something simple like > >> GRANT ALL ON database.* TO JOE; > >> > >> Which would grant full access to all objects in the database to JOE for all > >> time? > > > > You can do it like this in psql: > > > > \a > > \t > > \o /tmp/grant.sql > > SELECT 'GRANT ALL ON ' || n.nspname || '.' || c.relname || > > ' TO joe;' > > FROM pg_catalog.pg_class AS c > > LEFT JOIN pg_catalog.pg_namespace AS n > > ON n.oid = c.relnamespace > > WHERE c.relkind IN ('r','v','S') AND > > n.nspname NOT IN ('pg_catalog', 'pg_toast') AND > > pg_catalog.pg_table_is_visible(c.oid) > > ORDER BY n.nspname, c.relname; > > \o > > \i /tmp/grant.sql > > > > > > The above could be put in a script and run from a Unix command prompt. > > > > (The SQL used above is adaated from that used by psql's \d command.) > > -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University