pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
grant execute on many functions
13+ messages / 4 participants
[nested] [flat]

* grant execute on many functions
@ 2004-06-29 21:39  Jean-Denis Giguere <jdenisgiguere@fastmail.fm>
  0 siblings, 1 reply; 13+ messages in thread

From: Jean-Denis Giguere @ 2004-06-29 21:39 UTC (permalink / raw)
  To: pgsql-admin

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








^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-29 22:27  Joe Conway <mail@joeconway.com>
  parent: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>
  0 siblings, 1 reply; 13+ messages in thread

From: Joe Conway @ 2004-06-29 22:27 UTC (permalink / raw)
  To: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 03:45  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joe Conway <mail@joeconway.com>
  0 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2004-06-30 03:45 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 04:20  Joe Conway <mail@joeconway.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 3 replies; 13+ messages in thread

From: Joe Conway @ 2004-06-30 04:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 04:26  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joe Conway <mail@joeconway.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2004-06-30 04:26 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 04:38  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joe Conway <mail@joeconway.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Tom Lane @ 2004-06-30 04:38 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 04:45  Joe Conway <mail@joeconway.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 13+ messages in thread

From: Joe Conway @ 2004-06-30 04:45 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 04:52  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Joe Conway <mail@joeconway.com>
  0 siblings, 0 replies; 13+ messages in thread

From: Tom Lane @ 2004-06-30 04:52 UTC (permalink / raw)
  To: Joe Conway <mail@joeconway.com>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 05:13  Joe Conway <mail@joeconway.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 13+ messages in thread

From: Joe Conway @ 2004-06-30 05:13 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jean-Denis Giguere <jdenisgiguere@fastmail.fm>; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 09:03  Doug Quale <quale1@charter.net>
  parent: Joe Conway <mail@joeconway.com>
  2 siblings, 1 reply; 13+ messages in thread

From: Doug Quale @ 2004-06-30 09:03 UTC (permalink / raw)
  To: pgsql-admin

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.




^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 15:07  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Doug Quale <quale1@charter.net>
  0 siblings, 2 replies; 13+ messages in thread

From: Tom Lane @ 2004-06-30 15:07 UTC (permalink / raw)
  To: Doug Quale <quale1@charter.net>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 15:32  Doug Quale <quale1@charter.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 13+ messages in thread

From: Doug Quale @ 2004-06-30 15:32 UTC (permalink / raw)
  To: pgsql-admin

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.



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: grant execute on many functions
@ 2004-06-30 16:32  Jean-Denis Giguere <jdenisgiguere@fastmail.fm>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 13+ messages in thread

From: Jean-Denis Giguere @ 2004-06-30 16:32 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

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





^ permalink  raw  reply  [nested|flat] 13+ messages in thread


end of thread, other threads:[~2004-06-30 16:32 UTC | newest]

Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-29 21:39 grant execute on many functions Jean-Denis Giguere <jdenisgiguere@fastmail.fm>
2004-06-29 22:27 ` Joe Conway <mail@joeconway.com>
2004-06-30 03:45   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-30 04:20     ` Joe Conway <mail@joeconway.com>
2004-06-30 04:26       ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-30 04:45         ` Joe Conway <mail@joeconway.com>
2004-06-30 04:52           ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-30 04:38       ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-30 05:13         ` Joe Conway <mail@joeconway.com>
2004-06-30 09:03       ` Doug Quale <quale1@charter.net>
2004-06-30 15:07         ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-30 15:32           ` Doug Quale <quale1@charter.net>
2004-06-30 16:32           ` Jean-Denis Giguere <jdenisgiguere@fastmail.fm>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox