Обсуждение: GRANT question
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.
=?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
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.
=?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
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
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.
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
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
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.
=?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