Re: grant execute on many functions

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

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

Joe Conway <mail(at)joeconway(dot)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

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

Joe Conway <mail(at)joeconway(dot)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

Joe Conway <mail(at)joeconway(dot)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

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

Joe Conway <mail(at)joeconway(dot)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

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)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

Joe Conway <mail(at)joeconway(dot)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.

[ moving thread to a more appropriate list ]

Joe Conway <mail(at)joeconway(dot)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

Doug Quale <quale1(at)charter(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Doug Quale <quale1(at)charter(dot)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.

Tom Lane wrote:
> Doug Quale <quale1(at)charter(dot)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