Thread: 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>