Обсуждение: ERROR: function crosstab(unknown, unknown) does not exist
Hi there, I am using 9.1.3. I inserted the tablefunc extension, into a SCHEMA called tablefunc, in order to separate it from my tables.I had to create it as postgres user, but changed than the Owner of both schema and functions to my user XXX. Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launch itas user XXX, it complaints: ERROR: function crosstab(unknown, unknown) does not exist LINE 1: ...ROM countries_view AS c LEFT JOIN ( SELECT * FROM crosstab( ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ********** Error ********** ERROR: function crosstab(unknown, unknown) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 84 I looked for the other tables which are included in the query, if they belong to postgres, but they belong all to user XXX. So, what could that be? Thanks for any hints!
On 07/03/2012 06:48 AM, Stefan Schwarzer wrote: > Hi there, > > I am using 9.1.3. I inserted the tablefunc extension, into a SCHEMA called tablefunc, in order to separate it from my tables.I had to create it as postgres user, but changed than the Owner of both schema and functions to my user XXX. > > Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launchit as user XXX, it complaints: > > ERROR: function crosstab(unknown, unknown) does not exist > LINE 1: ...ROM countries_view AS c LEFT JOIN ( SELECT * FROM crosstab( ... ^ > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > > ********** Error ********** > ERROR: function crosstab(unknown, unknown) does not exist > SQL state: 42883 > Hint: No function matches the given name and argument types. You might need to add explicit type casts. > Character: 84 > > > I looked for the other tables which are included in the query, if they belong to postgres, but they belong all to userXXX. > > So, what could that be? > > Thanks for any hints! Are you schema qualifying the function name when you use it? If not, does user XXX have schema tablefunc in their search_path? > -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> writes: > On 07/03/2012 06:48 AM, Stefan Schwarzer wrote: >> Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launchit as user XXX, it complaints: > Are you schema qualifying the function name when you use it? > If not, does user XXX have schema tablefunc in their search_path? Also, does user XXX have USAGE permission on the schema containing the extension? Usually, if you're going to stick an extension into a schema other than public, you're going to want to grant usage on that schema to PUBLIC, or at least more than nobody. regards, tom lane
>>> Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launchit as user XXX, it complaints: > >> Are you schema qualifying the function name when you use it? >> If not, does user XXX have schema tablefunc in their search_path? > > Also, does user XXX have USAGE permission on the schema containing the > extension? Usually, if you're going to stick an extension into a schema > other than public, you're going to want to grant usage on that schema to > PUBLIC, or at least more than nobody. Strange thing is that if I specify the schema for the crosstab function: SELECT * FROM tablefunc.crosstab( 'SEL…. than it works. The search path is indicated as: "$user", public, metadata, admin, gis, tablefunc, postgis, topology I added a GRANT USAGE ON SCHEMA tablefunc TO XXX. Just in case, but this didn't solve the problem. Still wondering why itworks for user Postgres, but not for user XXX...
On 07/04/2012 11:20 PM, Stefan Schwarzer wrote: >>>> Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launchit as user XXX, it complaints: >> >>> Are you schema qualifying the function name when you use it? >>> If not, does user XXX have schema tablefunc in their search_path? >> >> Also, does user XXX have USAGE permission on the schema containing the >> extension? Usually, if you're going to stick an extension into a schema >> other than public, you're going to want to grant usage on that schema to >> PUBLIC, or at least more than nobody. > > Strange thing is that if I specify the schema for the crosstab function: > > SELECT * FROM tablefunc.crosstab( 'SEL…. > > than it works. > > The search path is indicated as: > > "$user", public, metadata, admin, gis, tablefunc, postgis, topology > > I added a GRANT USAGE ON SCHEMA tablefunc TO XXX. Just in case, but this didn't solve the problem. Still wondering whyit works for user Postgres, but not for user XXX... > Using psql log in as user XXX and run SELECT current_schemas('f'); Do same as the postgres user. Let us know the results. -- Adrian Klaver adrian.klaver@gmail.com
>>>>> Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launchit as user XXX, it complaints: >>> >>>> Are you schema qualifying the function name when you use it? >>>> If not, does user XXX have schema tablefunc in their search_path? >>> >>> Also, does user XXX have USAGE permission on the schema containing the >>> extension? Usually, if you're going to stick an extension into a schema >>> other than public, you're going to want to grant usage on that schema to >>> PUBLIC, or at least more than nobody. >> >> Strange thing is that if I specify the schema for the crosstab function: >> >> SELECT * FROM tablefunc.crosstab( 'SEL…. >> >> than it works. >> >> The search path is indicated as: >> >> "$user", public, metadata, admin, gis, tablefunc, postgis, topology >> >> I added a GRANT USAGE ON SCHEMA tablefunc TO XXX. Just in case, but this didn't solve the problem. Still wondering whyit works for user Postgres, but not for user XXX... >> > > Using psql log in as user XXX and run SELECT current_schemas('f'); > Do same as the postgres user. > Let us know the results. Ahh…. Indeed, no "tablefunc" schema in there. So, what do I need to do? And what does the command mean?
Stefan Schwarzer <stefan.schwarzer@unep.org> writes: > Ahh�. Indeed, no "tablefunc" schema in there. If the textual value of search_path (as per "show search_path") lists the schema but current_schemas() doesn't, I have to think that you've got a permissions problem --- the system will silently ignore any search_path entries for which you don't have USAGE permission. You said you'd done a GRANT but I think you must've fat-fingered it somehow ... regards, tom lane
On 07/05/2012 07:46 AM, Stefan Schwarzer wrote: >>>>>> Now, when I launch a query which includes "crosstab()" as a postgres user, everything works fine. However, if I launchit as user XXX, it complaints: >>> >>> The search path is indicated as: >>> >>> "$user", public, metadata, admin, gis, tablefunc, postgis, topology >>> >>> I added a GRANT USAGE ON SCHEMA tablefunc TO XXX. Just in case, but this didn't solve the problem. Still wondering whyit works for user Postgres, but not for user XXX... >>> >> >> Using psql log in as user XXX and run SELECT current_schemas('f'); >> Do same as the postgres user. >> Let us know the results. > > Ahh…. Indeed, no "tablefunc" schema in there. So, what do I need to do? And what does the command mean? See here for info: http://www.postgresql.org/docs/9.1/interactive/functions-info.html Per Toms suggestion you need to check the permissions on the schema. One way to do that is, from psql type the following and enter: \dn+ > -- Adrian Klaver adrian.klaver@gmail.com
> Per Toms suggestion you need to check the permissions on the schema. > One way to do that is, from psql type the following and enter: > \dn+ One other thing you might want to verify: Does user XXX have the PUBLIC schema in their search_path? It is by default, but some people take it out for security reasons. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
> If the textual value of search_path (as per "show search_path") lists > the schema but current_schemas() doesn't, I have to think that you've > got a permissions problem --- the system will silently ignore any > search_path entries for which you don't have USAGE permission. > You said you'd done a GRANT but I think you must've fat-fingered it > somehow … Thanks so much Tom, Adrian & Alban. After various tries, I finally dropped again the schema, recreated it, and "GRANT ALLPRIVILEGES ON SCHEMA tablefunc TO xxx;"…. and now it works. Great! Merci & Danke!