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 7D565D1B289 for ; Wed, 30 Jun 2004 13:32:59 -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 61352-10 for ; Wed, 30 Jun 2004 16:32:54 +0000 (GMT) Received: from gaia.cits.rncan.gc.ca (gaiafast.cits.rncan.gc.ca [132.156.18.23]) by svr1.postgresql.org (Postfix) with ESMTP id 8085ED1B172 for ; Wed, 30 Jun 2004 13:32:50 -0300 (ADT) Received: from fastmail.fm (rerun.cits.rncan.gc.ca [132.156.18.10]) by gaia.cits.rncan.gc.ca (8.11.3/8.11.3) with ESMTP id i5UGWcT00706; Wed, 30 Jun 2004 12:32:42 -0400 (EDT) Message-ID: <40E2EB25.1070005@fastmail.fm> Date: Wed, 30 Jun 2004 12:32:37 -0400 From: Jean-Denis Giguere User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040301 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane , pgsql-admin@postgresql.org Subject: Re: grant execute on many functions References: <40E1E1A9.8080207@fastmail.fm> <40E1ECEF.5090007@joeconway.com> <3318.1088567114@sss.pgh.pa.us> <40E23F72.4000108@joeconway.com> <87oen1quxt.fsf@charter.net> <8987.1088608020@sss.pgh.pa.us> In-Reply-To: <8987.1088608020@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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: 200406/401 X-Sequence-Number: 14061 Tom Lane wrote: > Doug Quale writes: > >>test=# select oid::regprocedure from pg_proc order by oid::regprocedure; > > >>doesn't sort the way I would expect. > > > Nope, it'd just be ordering by the numeric OID. If you added a cast > procedure as we were just discussing, you could order by > oid::regprocedure::text and get what I suppose you're expecting. Is this cast procedure is correct oid::regprocedure::text ? When I try this, I get ERROR: cannot cast type regprocedure to text. Here is a small bash script to grant execute on many functions. (There are some strange error sometime, but I'm not able to reproduce the bug...) ######################################################## #!/bin/bash #Usage: grantexfct schema groupe schema=$1 groupe=$2 SQL="SELECT p.oid::regprocedure from pg_proc p LEFT JOIN pg_namespace n ON p.pronamespace=n.oid where n.nspname like '$schema';" LIST=`psql -c "$SQL" -A -t -U postgres servweb` echo $LIST for fct in $LIST do echo $fct SQL="GRANT EXECUTE ON FUNCTION $fct TO GROUP $groupe;" psql -c "$SQL" -U postgres servweb done exit 0 ######################################################## This is the pl/pgsql function. It is broken because I'm not able to cast ::regprocedure to ::text Maybye there are others bugs... ######################################################## --grant_exec(SCHEMA,GROUP) -- Grants execute on every functions of SCHEMA to group GROUP -- DECLARE schem ALIAS FOR $1; grp ALIAS FOR $2; obj record; num integer; BEGIN num:=0; FOR obj IN SELECT p.oid::regprocedure as funct FROM pg_proc p LEFT JOIN pg_namespace n ON p.pronamespace=n.oid where n.nspname like schem LOOP EXECUTE 'GRANT EXECUTE ON FUNCTION ' || schem || '.' || obj.funct ::text || ' TO GROUP ' || grp; num := num + 1; END LOOP; RETURN num; END; ######################################################### Any comment is welcome. Thank you to everyone who has contributed to this "solution". > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html