1

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?

  • 2
    This doesn't look like a permissions issue, but a schema issue. When you get an error like such and such a function does not exist it is either because the function signature is wrong or because you have the Postgis functions in the wrong schema. – John Powell May 30 at 15:56
  • 1
    is the PostGIS schema (likely public) in the user search path? (do show search_path;) – JGH May 30 at 15:59
  • 1
    Check where schema Postgis is installed and check that your search_path includes it. It is also possible that you will need to provide the USAGE privilege for a particular schema. – John Powell May 30 at 15:59
0

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).

0

It is also possible you do not have postGIS properly installed on your database instance. Try running CREATE EXTENSION postgis; and then try running your original query again. I hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.