Обсуждение: create function (plgsql): permission denied

Greetings,

I have a user, owner a schema, but I just can't create any function
plgsql through that user. I got the error message: "plpgsql: permission
denied".

I don't know how can I grant permission to create functions for that
user (preferable way) or trust plpgsql language as a trusted language
(alternative way).

These are my definitions languages:

select * from pg_language ;
lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
----------+---------+--------------+---------------+--------------+--------
 plpgsql  | t       | f            |         20893 |            0 |
 sql      | f       | t            |             0 |         2248 | {=U}
 internal | f       | f            |             0 |         2246 | {=}
 c        | f       | f            |             0 |         2247 | {=}


In addition, could anyone explain me these table attributes?

thanks,
Pedro Salazar.
--
PS
pedro-b-salazar@ptinovacao.pt
PGP:0E129E31D803BC61

Greetings,

On Mon, 2003-04-07 at 12:39, Pedro Salazar wrote:
> select * from pg_language ;
> lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
> ----------+---------+--------------+---------------+--------------+--------
>  plpgsql  | t       | f            |         20893 |            0 |

I updated the 'lanpltrusted' attribute to true, and now I can create
functions.

But, is it possible that only a group or a specified list of users have
permission to create plpgsql functions instead trust the language for
all users?

thanks,
Pedro Salazar.
--
PS
pedro-b-salazar@ptinovacao.pt
PGP:0E129E31D803BC61

 From psql <databasename> -U<databaseowner>, issue:
    GRANT USAGE ON LANGUAGE plpgsql to <desireduser>;
You may also need (to allow creation of the function)
    GRANT ALL ON SCHEMA <schemaname> TO <desireduser>;

This will grant <desireduser> the usage of plpgsql even if it is untrusted.

For attribute explanations, see
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=catalog-pg-language.htm


John Gunther
Bucks vs Bytes Inc


Pedro Salazar wrote:

>Greetings,
>
>On Mon, 2003-04-07 at 12:39, Pedro Salazar wrote:
>
>
>>select * from pg_language ;
>>lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl
>>----------+---------+--------------+---------------+--------------+--------
>> plpgsql  | t       | f            |         20893 |            0 |
>>
>>
>
>I updated the 'lanpltrusted' attribute to true, and now I can create
>functions.
>
>But, is it possible that only a group or a specified list of users have
>permission to create plpgsql functions instead trust the language for
>all users?
>
>thanks,
>Pedro Salazar.
>
>

John Gunther <inbox@bucksvsbytes.com> writes:
> This will grant <desireduser> the usage of plpgsql even if it is untrusted.

No.  Untrusted languages can be used only by superusers, period.  USAGE
is for controlling non-superusers' access to trusted languages.

            regards, tom lane

Hi John,

when I make grant usage on language 'plpgsql' to my user, I get this
error:

ERROR:  language "plpgsql" is not trusted

So, if I must trust my language, I won't need the grant usage of
language to my user, will I?

BTW, if my user is already the owner of schema, is really need the grant
all on schema to my user?

thanks,
Pedro Salazar.

On Mon, 2003-04-07 at 13:53, John Gunther wrote:
>  From psql <databasename> -U<databaseowner>, issue:
>     GRANT USAGE ON LANGUAGE plpgsql to <desireduser>;
> You may also need (to allow creation of the function)
>     GRANT ALL ON SCHEMA <schemaname> TO <desireduser>;
>
> This will grant <desireduser> the usage of plpgsql even if it is untrusted.

--
PS
pedro-b-salazar@ptinovacao.pt
PGP:0E129E31D803BC61

<br /><br /><blockquote cite="mid1049726289.1132.38.camel@vitoria.intra.cet.pt" type="cite"><pre wrap="">when I make
grantusage on language 'plpgsql' to my user, I get this 
error:

ERROR:  language "plpgsql" is not trusted, if I must trust my language, I won't need the grant usage of
language to my user, will I?</pre></blockquote> Yes, I miswrote that sentence. The language must be trusted for any
non-superuserto ever use it. Just because the language is trusted, though, should not automatically allow users access
toit without a GRANT. The fact that they do not need GRANTing, though, is either a bug or a documentation error.<br
/><blockquotecite="mid1049726289.1132.38.camel@vitoria.intra.cet.pt" type="cite"><pre wrap=""> 

So

BTW, if my user is already the owner of schema, is really need the grant
all on schema to my user?</pre></blockquote> You're correct. I was allowing for <desireduser> to not be the
schemaowner<br /><blockquote cite="mid1049726289.1132.38.camel@vitoria.intra.cet.pt" type="cite"><pre wrap=""> 

thanks,
Pedro Salazar.

On Mon, 2003-04-07 at 13:53, John Gunther wrote: </pre></blockquote><blockquote
cite="mid1049726289.1132.38.camel@vitoria.intra.cet.pt"type="cite"><blockquote type="cite"><pre wrap=""> From psql
<databasename>-U<databaseowner>, issue:   GRANT USAGE ON LANGUAGE plpgsql to <desireduser>; 
You may also need (to allow creation of the function)   GRANT ALL ON SCHEMA <schemaname> TO <desireduser>;

This will grant <desireduser> the usage of plpgsql even if it is untrusted.   </pre></blockquote><pre wrap="">
</pre></blockquote>