ERROR: function crosstab(unknown, unknown) does not exist

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 launch it 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 user XXX.

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 launch it 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 launch it 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 it works 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 launch it 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 it 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 launch it 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 it 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 launch it 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 why it 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.

Adrian Klaver <adrian.klaver@gmail.com> wrote:

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 ALL PRIVILEGES ON SCHEMA tablefunc TO xxx;"…. and now it works. Great! Merci & Danke!