Hi,

I have about 200 functions in a schema and I have to grant execute 
privilege on all these functions to a group.

I have found on the web an interesting function for grant access on table.
  http://www.lerctr.org/pgnotes/pgnotes.html 
(http://www.lerctr.org/pgnotes/grant-all.html)

But, the problem is more complex for functions because you have to give 
the arguments. You can find the list in the pg_proc table but they are 
store in a array by oid. So you can resolve the type with the table 
pg_type. After, you have to reproduce the correct syntax (with correct 
number of orgument, on one execute line...)

Is there a more simple approach to resolve this problem ?

Also if someone has a link to an advanced pl/pgsql documentation, I 
would really appreciate it because writing this function bypass my 
current knowledge of sql and the postgresql documentation on this topic 
don't give very complex examples.

Thank you for your attention,


--
Jean-Denis Giguï¿½re
ï¿½tudiant en gï¿½omatique appliquï¿½e ï¿½ l'environnement
Universitï¿½ de Sherbrooke






Tue, 29 Jun 2004 17:39:53 -0400
Jean-Denis Giguere <jdenisgiguere@fastmail.fm>
grant execute on many functions
Jean-Denis Giguere wrote:
> But, the problem is more complex for functions because you have to give 
> the arguments. You can find the list in the pg_proc table but they are 
> store in a array by oid. So you can resolve the type with the table 
> pg_type. After, you have to reproduce the correct syntax (with correct 
> number of orgument, on one execute line...)
> 

Does this help?

SELECT n.nspname || '.' || p.proname ||
        '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')'
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
AND p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype
AND NOT p.proisagg
AND pg_catalog.pg_function_is_visible(p.oid)
AND p.proname ~ '^format_type$';
                ?column?
--------------------------------------
  pg_catalog.format_type(oid, integer)
(1 row)

Joe

Tue, 29 Jun 2004 15:27:59 -0700
Joe Conway <mail@joeconway.com>
Re: grant execute on many functions
Joe Conway <mail@joeconway.com> writes:
> Jean-Denis Giguere wrote:
>> But, the problem is more complex for functions because you have to give 
>> the arguments.

> Does this help?

> SELECT n.nspname || '.' || p.proname ||
>         '(' || pg_catalog.oidvectortypes(p.proargtypes) || ')'

Also, casting the function's OID to regprocedure may be useful.
Random example:

regression=# select 1142::regprocedure;
      regprocedure
------------------------
 date_mii(date,integer)
(1 row)

			regards, tom lane

Tue, 29 Jun 2004 23:45:14 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: grant execute on many functions 
Tom Lane wrote:
> Also, casting the function's OID to regprocedure may be useful.
> Random example:
> 
> regression=# select 1142::regprocedure;
>       regprocedure
> ------------------------
>  date_mii(date,integer)
> (1 row)

That's even better -- I tried regproc, but forgot about regprocedure. I 
think the problem will be, though, that the output of the reg* datatypes 
is not castable to text, and therefore cannot be used to build a dynamic 
sql statement.

   select 'GRANT EXECUTE ON ' || 1142::regprocedure;
   ERROR:  array value must start with "{" or dimension information

But with the help of plpgsql:

   create or replace function regprocedure2text(regprocedure)
   returns text as '
     begin
       return $1;
     end;
   ' language plpgsql;
   CREATE FUNCTION
   select 'GRANT EXECUTE ON ' || regprocedure2text(1142::regprocedure);
                   ?column?
   -----------------------------------------
    GRANT EXECUTE ON date_mii(date,integer)
   (1 row)

Joe

Tue, 29 Jun 2004 21:20:02 -0700
Joe Conway <mail@joeconway.com>
Re: grant execute on many functions
Joe Conway <mail@joeconway.com> writes:
> That's even better -- I tried regproc, but forgot about regprocedure. I 
> think the problem will be, though, that the output of the reg* datatypes 
> is not castable to text, and therefore cannot be used to build a dynamic 
> sql statement.

Um.  Sooner or later we ought to do something about the whole
automatic-casting-to-and-from-text issue.  Datatypes shouldn't have to
supply both I/O procedures and text cast procedures.

> But with the help of plpgsql:

Right, in the short term you can make it work inside a plpgsql function,
since plpgsql is pretty darn lax about casting.  But it'd be nice if it
worked more generally.

			regards, tom lane

Wed, 30 Jun 2004 00:26:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: grant execute on many functions 
Joe Conway <mail@joeconway.com> writes:
>    select 'GRANT EXECUTE ON ' || 1142::regprocedure;
>    ERROR:  array value must start with "{" or dimension information

BTW, it seems like there's something pretty broken here.  How did
arrays get into it?  A quick probe suggests that it is resolving
the above input as array_append for type regprocedure[], which would
qualify as a surprising choice in my book.  7.3 gives a more reasonable
"unable to identify an operator ||" ...

			regards, tom lane

Wed, 30 Jun 2004 00:38:01 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: grant execute on many functions 
Tom Lane wrote:
> Um.  Sooner or later we ought to do something about the whole
> automatic-casting-to-and-from-text issue.  Datatypes shouldn't have to
> supply both I/O procedures and text cast procedures.

I thought automatic (i.e. implicit) casting-to-and-from-text was a no-no 
because it could cause strange and unintended conversion to take place, 
no? I do believe that explict casting to text should be allowed -- the 
result of any OUT function can be represented as text, can't it? A 
C-language version of the following should do it:

create or replace function any2text(anyelement) returns text as '
  begin
   return $1;
  end;
' language plpgsql;

And if we could cast text to cstring, it could be the input of any IN 
function. Hmmm, I guess you can cast any literal to cstring though:

select int4in('1'::cstring);
  int4in
--------
       1
(1 row)


> Right, in the short term you can make it work inside a plpgsql function,
> since plpgsql is pretty darn lax about casting.  But it'd be nice if it
> worked more generally.
> 

So what would that look like, and still be "safe".

Joe



Tue, 29 Jun 2004 21:45:05 -0700
Joe Conway <mail@joeconway.com>
Re: grant execute on many functions
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Um.  Sooner or later we ought to do something about the whole
>> automatic-casting-to-and-from-text issue.  Datatypes shouldn't have to
>> supply both I/O procedures and text cast procedures.

> I thought automatic (i.e. implicit) casting-to-and-from-text was a no-no 
> because it could cause strange and unintended conversion to take place, 
> no?

Sorry, I was imprecise.  I think that the system should automatically
provide a coercion to/from text implemented on top of a datatype's I/O
procedures.  I do not say that that coercion should be *applied*
implicitly --- I'd favor requiring explicit cast syntax to get it.

For datatypes that have a close enough affinity to text for implicit
coercion behavior to be reasonable, we should expect the datatype
designer to create a pg_cast entry to say so.

In my mind cleaning up this area needs to tie into rationalizing the
current mismash of some-datatypes-have-implicit-coercions-and-some-
do-not behaviors.  That doubtless involves breaking some existing
applications :-( but it would surely make the overall behavior much
more predictable.

			regards, tom lane

Wed, 30 Jun 2004 00:52:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: grant execute on many functions 
Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> 
>>   select 'GRANT EXECUTE ON ' || 1142::regprocedure;
>>   ERROR:  array value must start with "{" or dimension information
> 
> 
> BTW, it seems like there's something pretty broken here.  How did
> arrays get into it?  A quick probe suggests that it is resolving
> the above input as array_append for type regprocedure[], which would
> qualify as a surprising choice in my book.  7.3 gives a more reasonable
> "unable to identify an operator ||" ...

array_append is defined thus:

regression=# \df array_append
                           List of functions
  Result data type |   Schema   |     Name     | Argument data types
------------------+------------+--------------+----------------------
  anyarray         | pg_catalog | array_append | anyarray, anyelement
(1 row)

So the "||" operator sees (unknown, regprocedure), and make_op tries to 
coerce the unknown literal to an array of regprocedure, which of course 
fails. If instead the literal is explicitly cast:

select 'GRANT EXECUTE ON '::text || 1142::regprocedure;
ERROR:  operator does not exist: text || regprocedure
HINT:  No operator matches the given name and argument type(s). You may 
need to add explicit type casts.

I'm not clear on how we can do better :(. Any suggestions?

Joe

Tue, 29 Jun 2004 22:13:57 -0700
Joe Conway <mail@joeconway.com>
Re: grant execute on many functions
Joe Conway <mail@joeconway.com> writes:

> That's even better -- I tried regproc, but forgot about
> regprocedure. I think the problem will be, though, that the output of
> the reg* datatypes is not castable to text, and therefore cannot be
> used to build a dynamic sql statement.

This must be why

test=# select oid::regprocedure from pg_proc order by oid::regprocedure;

doesn't sort the way I would expect.


30 Jun 2004 04:03:26 -0500
Doug Quale <quale1@charter.net>
Re: grant execute on many functions
[ moving thread to a more appropriate list ]

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> select 'GRANT EXECUTE ON ' || 1142::regprocedure;
>> ERROR:  array value must start with "{" or dimension information
>> 
>> BTW, it seems like there's something pretty broken here.  How did
>> arrays get into it?

> regression=# \df array_append
>                            List of functions
>   Result data type |   Schema   |     Name     | Argument data types
> -------------------+------------+--------------+----------------------
>   anyarray         | pg_catalog | array_append | anyarray, anyelement
> (1 row)

> So the "||" operator sees (unknown, regprocedure), and make_op tries to 
> coerce the unknown literal to an array of regprocedure, which of course 
> fails. If instead the literal is explicitly cast:

> select 'GRANT EXECUTE ON '::text || 1142::regprocedure;
> ERROR:  operator does not exist: text || regprocedure
> HINT:  No operator matches the given name and argument type(s). You may 
> need to add explicit type casts.

> I'm not clear on how we can do better :(. Any suggestions?

I'm not sure either, but I can't say that I like this interpretation.
I did find that if there is an implicit coercion to text then the system
will prefer a saner interpretation:

regression=# select 'GRANT EXECUTE ON ' || 1142::regprocedure;
ERROR:  array value must start with "{" or dimension information

regression=# create function text(regprocedure) returns text as '
regression'# begin
regression'#   return $1;
regression'# end' language plpgsql stable strict;
CREATE FUNCTION
regression=# create cast (regprocedure as text) with function text(regprocedure) as implicit;
CREATE CAST

regression=# select 'GRANT EXECUTE ON ' || 1142::regprocedure;
                ?column?
-----------------------------------------
 GRANT EXECUTE ON date_mii(date,integer)
(1 row)

I'm worried though about how stable this choice is.  I'm almost tempted
to add a wart in the coercion routines to discourage matching "unknown"
to "anyarray" ...

			regards, tom lane

Wed, 30 Jun 2004 09:47:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Implicit casting for || (was Re: grant execute on many functions)
Doug Quale <quale1@charter.net> 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.

			regards, tom lane

Wed, 30 Jun 2004 11:07:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: grant execute on many functions 
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Doug Quale <quale1@charter.net> 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.

Thanks for the explanation.  Some months ago I had tried
oid::regprocedure::text and found it didn't work.  I didn't figure out
that this was because I need to create a cast procedure.

30 Jun 2004 10:32:06 -0500
Doug Quale <quale1@charter.net>
Re: grant execute on many functions
Tom Lane wrote:
> Doug Quale <quale1@charter.net> 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



Wed, 30 Jun 2004 12:32:37 -0400
Jean-Denis Giguere <jdenisgiguere@fastmail.fm>
Re: grant execute on many functions