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 EB731D1B56E for ; Mon, 26 Jul 2004 17:05:09 -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 86472-10 for ; Mon, 26 Jul 2004 20:05:01 +0000 (GMT) Received: from ptb-relay03.plus.net (ptb-relay03.plus.net [212.159.14.214]) by svr1.postgresql.org (Postfix) with ESMTP id A6E3BD1B209 for ; Mon, 26 Jul 2004 17:04:38 -0300 (ADT) Received: from [212.159.87.229] (helo=thelabyrinth.force9.co.uk) by ptb-relay03.plus.net with esmtp (Exim) id 1BpBi2-000Nyt-8l; Mon, 26 Jul 2004 20:04:30 +0000 Received: from ruth.localdomain (ruth.localdomain [192.168.0.1]) by thelabyrinth.force9.co.uk (Postfix) with ESMTP id 8DCD130060; Mon, 26 Jul 2004 20:05:33 +0100 (BST) Date: Mon, 26 Jul 2004 21:05:08 +0100 (BST) From: Sam Barnett-Cormack X-X-Sender: sdb@localhost.localdomain To: Jean-Luc Lachance Cc: Randall Perry , olly@lfix.co.uk, pgsql-admin@postgresql.org Subject: Re: How do I grant access to entire database at In-Reply-To: <41055B7D.7000106@sympatico.ca> Message-ID: References: <41055B7D.7000106@sympatico.ca> 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/298 X-Sequence-Number: 14366 On Mon, 26 Jul 2004, Jean-Luc Lachance wrote: > I have been thinking about this problem for quite a while. > > Proper administration require creation of groups. > Adding a new user to a database is as simple as adding the user to the > group that has the required privileges to the database. > > But, I think one new command would be very usefull. > > CREATE GROUP FROM USER > > where the privileges would be derived from the user's. I ain't no guru, but I would say: 1) I'd've thought that'd be simple enough to implement, and it is nice and would be very handy 2) It's not as good or as flexible as what I suggested - it'd be nice to have both, but group from user is much more likely to be forthcoming, I expect 3) You're right about groups, and I don't think enough people use them enough or appropriately. Sam > 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. > > > > BTW, I prefer postgresql for all my own development. > > > > > > 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.) > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University