grant execute on many functions

[Jean-Denis Giguere]

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






---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Joe Conway]

Post by Jean-Denis Giguere
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Post by Joe Conway

Post by Jean-Denis Giguere
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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Joe Conway]

Post by Tom Lane
Also, casting the function's OID to regprocedure may be useful.
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Tom Lane]

Post by Joe Conway
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.
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joe Conway]

Post by Tom Lane
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)

Post by Tom Lane
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



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Tom Lane]

Post by Joe Conway

Post by Tom Lane
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Tom Lane]

Post by Joe Conway
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Joe Conway]

Post by Tom Lane

Post by Joe Conway
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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Doug Quale]

Post by Joe Conway
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.


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Doug Quale
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Doug Quale]

Post by Tom Lane

Post by Doug Quale
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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Jean-Denis Giguere]

Post by Tom Lane

Post by Doug Quale
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".

Post by Tom Lane
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly