Thread: Problem granting access to a PlPython function

Hi,
I have this plpython function that I need to execute with a non
superuser. I logged in the postgres account, create it and grant execute
rights to the target user.
However I cannot execute it with this user: I'm getting a "function ...
does not exist" error and after poking it for a few hours without luck,
it seems this problem is too much for a n00b like me.
Can anyone offer a hint how to make it work?
Following is a full example of what I'm doing:


# su - postgres
-bash-3.2$ psql -f hours_pgsql_function.sql
CREATE FUNCTION
-bash-3.2$ psql
Welcome to psql 8.3.8, the PostgreSQL interactive terminal.

postgres=# select hours_between(localtimestamp, localtimestamp,
cast('Argentina' as character varying));
 hours_between
---------------
             0
(1 row)

postgres=# grant execute on function hours_between (timestamp without
time zone, timestamp without time zone, character varying) to otrs;
GRANT
postgres=# \q
-bash-3.2$ psql -d otrs -U otrs
Welcome to psql 8.3.8, the PostgreSQL interactive terminal.

otrs=> select hours_between(localtimestamp, localtimestamp,
cast('Argentina' as character varying));
ERROR:  function hours_between(timestamp without time zone, timestamp
without time zone, character varying) does not exist
LINE 1: select hours_between(localtimestamp, localtimestamp, cast('A...
               ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
otrs=>

TIA,
Mariano.

On Sun, Nov 08, 2009 at 05:07:16PM -0300, Mariano Mara wrote:
> I have this plpython function that I need to execute with a non
> superuser. I logged in the postgres account, create it and grant execute
> rights to the target user.
> However I cannot execute it with this user: I'm getting a "function ...
> does not exist" error and after poking it for a few hours without luck,

It looks like you're creating it in a different database (and/or schema)
from the one you're trying to access it from.  Functions are associated
with exactly one schema and this schema will exist in exactly one
database. Your search_path specifies the schema(s) to look in for
tables/functions, you can change it by doing:

  SET search_path = myschema;

You need to be connected to the right database for this to work; "psql
-l" gives you a list.  The command "\df" lists all (user defined)
functions that you can currently see.

Hope that helps point you in the right direction!

--
  Sam  http://samason.me.uk/

Excerpts from Sam Mason's message of Sun Nov 08 17:18:52 -0300 2009:
> On Sun, Nov 08, 2009 at 05:07:16PM -0300, Mariano Mara wrote:
> > I have this plpython function that I need to execute with a non
> > superuser. I logged in the postgres account, create it and grant execute
> > rights to the target user.
> > However I cannot execute it with this user: I'm getting a "function ...
> > does not exist" error and after poking it for a few hours without luck,
>
> It looks like you're creating it in a different database (and/or schema)
> from the one you're trying to access it from.  Functions are associated
> with exactly one schema and this schema will exist in exactly one
> database. Your search_path specifies the schema(s) to look in for
> tables/functions, you can change it by doing:
>
>   SET search_path = myschema;
>
> You need to be connected to the right database for this to work; "psql
> -l" gives you a list.  The command "\df" lists all (user defined)
> functions that you can currently see.
>
> Hope that helps point you in the right direction!
>

That was fast and it was right too. Seems I still need to master the
whole database/schema concept.

Thanks a lot Sam for your time and advice, I really appreciate it.

Mariano.