Error during topology creation about osm2pgrouting HOT 11 CLOSED

Thanks for your detailed report.
Just for clarification: do you think this is related to the new version of Ubuntu, or did you post the version information only for completeness?
Or in other words; did it work with previous version(s) of Ubuntu?

Just in case you find out, how to fix this, you're very welcome to create a fork, make a change and submit a pull request.

For Ubuntu 14.04 I didn't do much testing. I actually just built the new package, and osm2pgrouting was even copied only, because there was no changed dependency.

from osm2pgrouting.

Thanks for your quick answer!
(And by the way a big thanks for all the great work done on pgrouting v2!)

Unfortunately I can't tell you if this bug is related to the new version because we previously run our server on Windows server with Pgrouting v1/Postgres 9.1

So for now my main suspect are theses ones with a high suspicion on an "escaping quote" mistake:
-It don't work because i'm not logged as postgres, and i dont use postgres user either
-It never worked, but no one reported as it can easily be done manually afterwards
-It's relative to this ubuntu version

I already had a quick glance at your code but, I must confess I had a hard time figuring out what do what... Wich is quite logical as I'm more a db-admin and not a developer :/

from osm2pgrouting.

Got the line to focus on in your code. The escape syntax seems sliglhy different from the preceding lines, but that should as well be normal as the SQL command is not of the same nature.
https://github.com/pgRouting/osm2pgrouting/blob/master/src/Export2DB.cpp#L474

But I haven't time yet to seek further, I need to work on my data right now :P Sorry :/

from osm2pgrouting.

Or maybie this is something totally different... This is the error returned on server log:
2014-04-30 11:04:57 CEST ERROR table "ways" does not exist

But obviously this table exist at the end of the process...
Could it be that the create topology function is called to early, maybie in the same transaction, so it cannot see the "just created" table "ways" ?

from osm2pgrouting.

I think the SQL command you linked to looks OK. The table name there is passed as an argument. Therefore it also has single quotation.

If a table "ways" doesn't exist, it maybe doesn't exist in the correct schema. I don't think this is a transaction problem. I guess that an errors occurs and the import tool just goes on and reports the error too late.

Unfortunately I have no time right now to look at this either. It seems to work with standard user "postgres" and/or default schema "public", right?
It could be the function pgr_createTopology itself, which either has difficulties to run as a different user, or a user with restricted permissions, or using a different schema.

from osm2pgrouting.

Hello, sorry I didn't have time earlier to further investigate.

I confirm everything works fine on any database if the default superuser role 'postgres' is used.

In fact the pgr_createTopology() function does not work with an other user.
More precisely it is the sub function pgr_gettablename() which could be enhanced.

When called by another role than 'postgres' or a superuser this function return a blank result.
You can try to reproduce by doing the following.

Connect with user postgres to a database where a 'ways' table exist and test the function:

SET ROLE postgres;
SELECT public.pgr_gettablename('ways');

This should return the right result:

 pgr_gettablename
------------------
 (public,ways)
(1 row)

Then create a new user, set role to it, and run the function with the same parameters.

CREATE ROLE debug LOGIN VALID UNTIL 'infinity';

SET ROLE debug;
SELECT public.pgr_gettablename('ways');

This should return this:

 pgr_gettablename
------------------
 (,)
(1 row)

Can you reproduce this behavior?

from osm2pgrouting.

Ok, clearly I'm a blind man as my problem was clearly linked to this other issues.
#28
pgRouting/pgrouting#252

If you change the ownership of the schema to an other user it enable the new owner to run pgr_createTopology().

But is this logical? Postgresql is meant to support multiple users, given that one user have a 'GRANT ALL' on a schema why should he be limited and couldn't run pgr_createTopology()?

from osm2pgrouting.

I think pgr_createTopology() is the only function, which requires the permissions to create a table.
But I need to look at the details of the function. I think we didn't look much at roles and permissions.
So thank you for pointing at it.

from osm2pgrouting.

In general, if the permissions do not allow us to access the the system tables or schemas that are needed to run the function then the function can not be expected to work. If you know of a better way to write pgr_gettablename() we would be happy to consider changing the function. Otherwise the best we can do is maybe add to the documents that you have certain permissions to run some commands.

from osm2pgrouting.

I'll have a look, but not on my working hours. What look inconsistent to me is that you cannot run command until you are superuser or the owner of the database (as specified by @yellow-sky in #28 ).
IMHO having rightful permissions should be sufficient, executing should not be restricted to solely owner & superusers.

from osm2pgrouting.

The pgr_CreateTopology is no longer used for osm2pgrouting 2.1+. The newer version uses the implicit osm topology. Also it is now possible to load piecemeal multiple osm files, so using CreateTopology when loading multiple files doesn't work well.

from osm2pgrouting.