Thread: 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!