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 57F9A329D76 for ; Thu, 16 Sep 2004 17:18:25 +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 82912-08 for ; Thu, 16 Sep 2004 16:18:21 +0000 (GMT) Received: from web13806.mail.yahoo.com (web13806.mail.yahoo.com [216.136.175.16]) by svr1.postgresql.org (Postfix) with SMTP id 1C8A7329D3B for ; Thu, 16 Sep 2004 17:18:19 +0100 (BST) Message-ID: <20040916161818.75280.qmail@web13806.mail.yahoo.com> Received: from [216.173.173.66] by web13806.mail.yahoo.com via HTTP; Thu, 16 Sep 2004 09:18:18 PDT Date: Thu, 16 Sep 2004 09:18:18 -0700 (PDT) From: Chris Gamache Reply-To: cgg007@yahoo.com Subject: Clone a user's permissions to a group To: pgsql-admin@postgresql.org In-Reply-To: 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.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200409/150 X-Sequence-Number: 14853 Can I run something by y'all to see if I'm on the right track, or have fallen off my rocker? ... Postgresql 7.4 ... Essentially I want to convert a user to a group. I want this new group to have the same privileges to the same objects that the original user had. I can then remove privileges from the original user, add it to the new group, and create additional users within that group. My thought was to be able to select all the objects and permissions to the objects in the database that the original user has, and modify the ACL to replace the user with the group. I just don't want to destroy my configuration by experimentation, or a a malformed update query. GRANT and REVOKE are well documented but, like every command, they obscure the actions that take place at the system-table level. I've also looked at the information_schema, which is something new to me. Perhaps I could write a function that would EXECUTE a statement like this: SELECT 'GRANT ' || privilege_type || ' ON ' || table_schema || '.' || table_name || ' TO GROUP ' || $2 || CASE WHEN is_grantable = 'YES' THEN ' WITH GRANT OPTION;' else ';' END from table_privileges WHERE grantee = $1; Where $1 is the old user, and $2 is the new user. This doesn't cover sequences, though. I'd have to take the view definition for table_privileges and add "OR c.relkind = 'S'::char" to the SQL to include sequences. I could do the same thing to REVOKE the user's privileges. Is there an easier/better/safer way? CG __________________________________ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail