Postgres Pro
Downloads
Home   >   mailing lists

Thread: GRANT question

GRANT question

From
Együd Csaba
Date:
Hi,
As a superuser I created a normal user:

    CREATE USER probauser NOCREATEDB NOCREATEUSER;
    ALTER GROUP eden_users ADD USER probauser;

a function:

    CREATE OR REPLACE FUNCTION edenproc_usesysid(text)
      RETURNS int4 AS .........
    -- Note that the owner of this function is the superuser.

I added the EXECUTE grant for a function to the probauser:

    GRANT EXECUTE ON FUNCTION edenproc_usesysid(text) TO probauser;


And then tried to run the function:

# select edenproc_usesysid('probauser');
ERROR:  function edenproc_usesysid(text) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.

Certainly as the superuser I can run the function... What's missing more???


Thank you,
  -- Csaba Együd

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.

Re: GRANT question

From
Tom Lane
Date:
=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> # select edenproc_usesysid('probauser');
> ERROR:  function edenproc_usesysid(text) does not exist
                                           ^^^^^^^^^^^^^^

Read the error message: this is not a lack-of-permissions problem.

I'd wonder whether the two users have the same schema search path...

            regards, tom lane

Re: GRANT question

From
Együd Csaba
Date:
Hi Tom,
thank yor answer.

>Read the error message: this is not a lack-of-permissions problem.
You are right, I just meant that perhaps it needs some other settings, e.g.
set search_path or similar ... Sorry for that...

Regarding the search_path it is the same in both cases:

# show search_path;
 search_path
--------------
 $user,public
(1 row)

What else could cause this problem?

-- csaba


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, November 26, 2005 5:29 PM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> # select edenproc_usesysid('probauser');
> ERROR:  function edenproc_usesysid(text) does not exist
                                           ^^^^^^^^^^^^^^


I'd wonder whether the two users have the same schema search path...

            regards, tom lane

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.

Re: GRANT question

From
Tom Lane
Date:
=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> Regarding the search_path it is the same in both cases:

> # show search_path;
>  search_path
> --------------
>  $user,public
> (1 row)

That's not "the same" if the superuser has a self-named schema ...

            regards, tom lane

Re: GRANT question

From
Michael Fuhr
Date:
On Sat, Nov 26, 2005 at 06:13:21PM +0100, Egyd Csaba wrote:
> Regarding the search_path it is the same in both cases:
>
> # show search_path;
>  search_path
> --------------
>  $user,public
> (1 row)

For different users, the above represents *different* search paths
because $user is expanded to the user name if a schema exists with
that name.

What does "\df edenproc_usesysid" show in psql?  If it shows nothing
then try wildcarding the name, like "\df *eden*".  If it still
shows nothing, are you sure you're connecting to the same database?
What does "SELECT current_database();" show for each user?

--
Michael Fuhr

Re: GRANT question

From
Együd Csaba
Date:
Tom, I see your point and you are right.

Michael, it seems if no functions were in my database...

When I created the moson.gdb database I didn't create any additional
schemas. I simply reloaded the dump - I think into the public. Thats all.
In PgAdmin the (all my) functions can be seen in the public schema (logging
in as any user). I don't really understand. What do I do wrong?

postgres@saurus:~> psql -U eden_admin moson.gdb
Password:
moson.gdb=# select edenproc_usesysid('probauser'::text) as usesyid;
 usesyid
---------
     118
(1 row)

moson.gdb=# show search_path;
 search_path
--------------
 $user,public
(1 row)

moson.gdb=# \df edenproc_usesysid
                          List of functions
 Schema |       Name        | Result data type | Argument data types
--------+-------------------+------------------+---------------------
 public | edenproc_usesysid | integer          | text
(1 row)

moson.gdb=# SELECT current_database();
 current_database
------------------
 moson.gdb
(1 row)

moson.gdb=# \q

postgres@saurus:~> psql -U probauser moson.gdb

moson.gdb=> select edenproc_usesysid('probauser'::text) as usesyid;
ERROR:  function edenproc_usesysid(text) does not exist
HINT:  No function matches the given name and argument types. You may need
to add explicit type casts.

moson.gdb=> \df edenproc_usesysid
                   List of functions
 Schema | Name | Result data type | Argument data types
--------+------+------------------+---------------------
(0 rows)

moson.gdb=> \df eden*
                   List of functions
 Schema | Name | Result data type | Argument data types
--------+------+------------------+---------------------
(0 rows)

moson.gdb=> SELECT current_database();
 current_database
------------------
 moson.gdb
(1 row)


-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Saturday, November 26, 2005 6:28 PM
To: Együd Csaba
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question

On Sat, Nov 26, 2005 at 06:13:21PM +0100, Egyd Csaba wrote:
> Regarding the search_path it is the same in both cases:
>
> # show search_path;
>  search_path
> --------------
>  $user,public
> (1 row)

For different users, the above represents *different* search paths because
$user is expanded to the user name if a schema exists with that name.

What does "\df edenproc_usesysid" show in psql?  If it shows nothing then
try wildcarding the name, like "\df *eden*".  If it still shows nothing, are
you sure you're connecting to the same database?
What does "SELECT current_database();" show for each user?

--
Michael Fuhr

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.

Re: GRANT question

From
Michael Fuhr
Date:
On Sat, Nov 26, 2005 at 09:10:40PM +0100, Egyd Csaba wrote:
> moson.gdb=> \df eden*
>                    List of functions
>  Schema | Name | Result data type | Argument data types
> --------+------+------------------+---------------------
> (0 rows)

Oops, I forgot that \df uses pg_function_is_visible(), so it won't
show functions that aren't in your search path.  Try this:

SELECT oid::regprocedure FROM pg_proc WHERE proname ILIKE '%eden%';

or

SELECT n.nspname, p.proname, oidvectortypes(p.proargtypes)
FROM pg_proc AS p
LEFT OUTER JOIN pg_namespace AS n ON n.oid = p.pronamespace
WHERE p.proname ILIKE '%eden%';

--
Michael Fuhr

Re: GRANT question

From
Tom Lane
Date:
Michael Fuhr <mike@fuhr.org> writes:
> Oops, I forgot that \df uses pg_function_is_visible(), so it won't
> show functions that aren't in your search path.  Try this:

Or try
    \df *.edenproc_usesysid

I note the lack of "show search_path" for the non-admin user in
your example ... it might also be interesting to try
    select current_schemas(true);

            regards, tom lane

Re: GRANT question

From
Együd Csaba
Date:
Hi Tom,
in case of the admin it results: {pg_catalog,public}. In case of the
non-admin user: {pg_catalog}!!!
Again, seeing it from the PgAdmin (as both users) I can see all the 5 well
known schemas.

-- csaba
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Sunday, November 27, 2005 12:07 AM
To: Michael Fuhr
Cc: Együd Csaba; pgsql-general@postgresql.org
Subject: Re: [GENERAL] GRANT question

Michael Fuhr <mike@fuhr.org> writes:
> Oops, I forgot that \df uses pg_function_is_visible(), so it won't
> show functions that aren't in your search path.  Try this:

Or try
    \df *.edenproc_usesysid

I note the lack of "show search_path" for the non-admin user in your example
... it might also be interesting to try
    select current_schemas(true);

            regards, tom lane

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.8/183 - Release Date: 2005.11.25.

Re: GRANT question

From
Tom Lane
Date:
=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
>> ... it might also be interesting to try
>>     select current_schemas(true);

> in case of the admin it results: {pg_catalog,public}. In case of the
> non-admin user: {pg_catalog}!!!

I think that confirms Michael's suggestion that you've removed public
USAGE access on the public schema.  Not a very good move if you want
to share objects across users.

            regards, tom lane