[GIS] psycopg2.ProgrammingError: function does not exist

errorpostgispostgresqlpsycopg2python

I am using Python code cur.callproc('function_a',[parameter1, parameter2]) for executing one function and this code is correct when I use a user having full DB permissions.

But when I use a private user, the psycopg2 returns an error in PostGIS functions st_distance(), st_transform(), st_setsrid().

Error message:
psycopg2.ProgrammingError: function st_setsrid(public.geometry, integer) does not exist
LINE 9: ...geocodigo = num1 ORDER BY st_distance(st_transform(st_setsrid...

                                                             ^ 
In SQL code: ... ORDER BY st_distance(st_transform(st_setsrid(a.geom,4674),4326),b.geom);

I tried many permissions for the private user but until now, just user with full permissions was accepted by Python code. What are the correct permissions for private user in PostgreSQL, or is there any other solution?

Best Answer

All calls to PostGIS functions must be schema qualified: schema_name.function (source).

To bypass writing the schema every time a PostGIS function is used, map the schema where PostGIS is (probably public) to the search_path (see here). As admin:

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

Moreover, make sure the private user has the necessary privileges in the database to proceed with the analysis (take a look here).

Related Question