A function using pgr_dijkstra (pgRouting) is returning correct answer when run from "admin". The same function run from a simple user returns an error (ERROR: function pgr_dijkstra(unknown, integer, integer, boolean) does not exist).

Complementary information:

admin - mydb> select * from pgr_version();

 version │  tag   │   hash    │ branch │ boost

2.6.2   │ v2.6.2 │ b14f4d56b │ master │ 1.59.0
(1 row)

But from user with all privileges granted I get:

user - mydb> select * from pgr_version();

ERROR:  **function pgr_version() does not exist**

LINE 1: select * from pgr_version();

Using, \df though, the function appears (exists)...

Same search_path for both user and admin, 2 different roles though for admin and user. But user can access all tables on the schema used (select, insert, update, delete) and can execute all functions in that schema. I am perplexed.

Query in function:

SELECT seq, d.node, d.edge, cost,
       e.geom as edge_geom,
       n.geom as node_geom,
    pgr_dijkstra( '
        SELECT gid AS id, source, target, length AS cost
            FROM london_tube.lines',
        ( SELECT station_id
                        FROM london_tube.stations WHERE station = 'Finchley Road'),
        (SELECT station_id
                        FROM london_tube.stations WHERE station = 'Piccadilly Circus'),
        ) AS d
        LEFT JOIN london_tube.lines AS e ON d.edge = e.gid
        LEFT JOIN london_tube.stations AS n ON d.node = n.station_id
ORDER BY d.seq;
  • Th GRANT USAGE ON SCHEMA my_schema TO my_user; This allows the execution of functions but not the access to tables..
    – Mapperz
    Sep 20 '20 at 20:37
  • What about the schema where pgRouting is installed? The user must have it in its path and have usage rights + exectute privs on the functions (if not public)
    – JGH
    Sep 21 '20 at 11:33
  • Thanks to @Mapperz and JGH. It was actually an incomplete set of privileges to the pgRouting resource. I add to grant all on both schema (where pgRouting was installed), tables, sequences and functions. Sep 24 '20 at 7:15

Your Answer

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

Browse other questions tagged or ask your own question.