Thread: Generating GRANT/REVOKE on functions from catalog

I am attempting to script the generation of grant/revoke statements for
a database, and I'm having some trouble when it comes to functions.

consider the following function:

create function add(a integer, b integer)
returns integer
as $$
select $1 + $2;
$$ language SQL;

The statement I need to generate is:

revoke all on function public.add(integer, integer) from someuser;

I'm attempting to use the pg_proc table in the system catalogs, and I'm
good up to the point where I need the parameter types.  Can anyone give
me a hand with this?

Thanks,

--
------------------------------------------------------------------------
*Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com>

Doug Gorley <doug.gorley@gmail.com> writes:
> The statement I need to generate is:

> revoke all on function public.add(integer, integer) from someuser;

> I'm attempting to use the pg_proc table in the system catalogs, and I'm
> good up to the point where I need the parameter types.  Can anyone give
> me a hand with this?

Personally, I'd cast the function OID to regprocedure, instead of
doing it the hard way ...

            regards, tom lane

That looks like exactly what I want.  Is there an easy way to cast that
to a string so that I can concatenate it into a GRANT statement?

------------------------------------------------------------------------
*Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com>


Tom Lane wrote:
> Doug Gorley <doug.gorley@gmail.com> writes:
>
>> The statement I need to generate is:
>>
>
>
>> revoke all on function public.add(integer, integer) from someuser;
>>
>
>
>> I'm attempting to use the pg_proc table in the system catalogs, and I'm
>> good up to the point where I need the parameter types.  Can anyone give
>> me a hand with this?
>>
>
> Personally, I'd cast the function OID to regprocedure, instead of
> doing it the hard way ...
>
>             regards, tom lane
>

Doug Gorley <doug.gorley@gmail.com> writes:
> That looks like exactly what I want.  Is there an easy way to cast that
> to a string so that I can concatenate it into a GRANT statement?

Well, since 8.3 you just cast it to a string ;-)

In older versions I'd suggest a plpgsql wrapper function.  plpgsql has
always been very lax about letting you assign anything to anything,
so you can cast by assignment.

            regards, tom lane

Perfect, I'm using the following function:

create or replace function fn_sig(p_oid oid) returns text
as $$
begin
return p_oid::regprocedure;
end;
$$ language plpgsql;

In the following query:

select
    pg_namespace.nspname ||
    '.' ||
    fn_sig(pg_proc.oid)
from
    pg_proc
    inner join pg_namespace
        on pg_proc.pronamespace = pg_namespace.oid

Thanks very much!

------------------------------------------------------------------------
*Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com>




Tom Lane wrote:
> Doug Gorley <doug.gorley@gmail.com> writes:
>
>> That looks like exactly what I want.  Is there an easy way to cast that
>> to a string so that I can concatenate it into a GRANT statement?
>>
>
> Well, since 8.3 you just cast it to a string ;-)
>
> In older versions I'd suggest a plpgsql wrapper function.  plpgsql has
> always been very lax about letting you assign anything to anything,
> so you can cast by assignment.
>
>             regards, tom lane
>

Doug Gorley <doug.gorley@gmail.com> writes:
> Perfect, I'm using the following function:
> create or replace function fn_sig(p_oid oid) returns text
> as $$
> begin
> return p_oid::regprocedure;
> end;
> $$ language plpgsql;

> In the following query:

> select
>     pg_namespace.nspname ||
>     '.' ||
>     fn_sig(pg_proc.oid)
> from


I wouldn't do that if I were you: regprocedure will already
schema-qualify the function name if it's needed.  The additional
qualification you're trying to force will just result in syntax errors.

            regards, tom lane