Thread: BUG #1571: Cannot grant execute functions to non-superusers

The following bug has been logged online:

Bug reference:      1571
Logged by:          CaT
Email address:      cat@zip.com.au
PostgreSQL version: 7.4.7
Operating system:   Linux (Debian woody with backports.org pgsql)
Description:        Cannot grant execute functions to non-superusers
Details:

Basically, I just cannot get my nonsuperuser access to the functions I
import. Aparently this works for 8.0 but I'm using 7.4.7 and its gone all
wibbly.


CREATE OR REPLACE FUNCTION gen_salt(text) RETURNS text AS
'$libdir/pgcrypto', 'pg_gen_salt' LANGUAGE 'C';
...
postgres=# \df+ gen_salt
                                                  List of functions
 Result data type | Schema |   Name   | Argument data types |  Owner   |
Language |    Source code     | Description
------------------+--------+----------+---------------------+----------+----
------+--------------------+-------------
 text             | public | gen_salt | text                | postgres | c
     | pg_gen_salt        |
 text             | public | gen_salt | text, integer       | postgres | c
     | pg_gen_salt_rounds |
(2 rows)

postgres=# grant execute on function gen_salt(text) to holly;
GRANT
postgres=# select gen_salt('md5'::text);
  gen_salt
-------------
 $1$CIvz7yzz
(1 row)




= holly@hollydb:/holly> \set VERBOSITY verbose
= holly@hollydb:/holly> select gen_salt('md5'::text);
ERROR:  42883: function gen_salt(text) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.
LOCATION:  ParseFuncOrColumn, parse_func.c:323
= holly@hollydb:/holly> \df+ gen_salt
                                           List of functions
 Result data type | Schema | Name | Argument data types | Owner | Language |
Source code | Description
------------------+--------+------+---------------------+-------+----------+
-------------+-------------
(0 rows)

On Thu, Mar 31, 2005 at 01:07:56AM +0100, CaT wrote:
>
> The following bug has been logged online:
>
> Bug reference:      1571
> Logged by:          CaT
> Email address:      cat@zip.com.au
> PostgreSQL version: 7.4.7
> Operating system:   Linux (Debian woody with backports.org pgsql)
> Description:        Cannot grant execute functions to non-superusers
> Details:

Ok. I apparently misunderstood something. It seems you can't add global
functions and just give users access to them. You need to dupe functions
on a per db basis. So... feel free to move along and ignore this bug
report. :)

Nothing to see here... :)

--
    "To the extent that we overreact, we proffer the terrorists the
    greatest tribute."
        - High Court Judge Michael Kirby

"CaT" <cat@zip.com.au> writes:
> = holly@hollydb:/holly> select gen_salt('md5'::text);
> ERROR:  42883: function gen_salt(text) does not exist

This is not a permissions problem, it's a "you're not looking in the
right place" problem.  I speculate that either you loaded the functions
in the wrong database, or holly for some reason doesn't have the public
schema in her search path.

            regards, tom lane

On Wed, Mar 30, 2005 at 07:46:37PM -0500, Tom Lane wrote:
> "CaT" <cat@zip.com.au> writes:
> > = holly@hollydb:/holly> select gen_salt('md5'::text);
> > ERROR:  42883: function gen_salt(text) does not exist
>
> This is not a permissions problem, it's a "you're not looking in the
> right place" problem.  I speculate that either you loaded the functions
> in the wrong database, or holly for some reason doesn't have the public
> schema in her search path.

Yes. Definately a PEBCAK issue. :)

--
    "To the extent that we overreact, we proffer the terrorists the
    greatest tribute."
        - High Court Judge Michael Kirby