pgRouting
pgr_getTableName fails when current user is not owner of a table's schema
When the current role accessing the database is not the owner of the schema where a table lives, pgr_getTableName returns null, null, even though the table exists, and is readable to the role.
I believe this is because pgr_getTableName reads from information_schema.schemata, which only lists schemas actually owned by the current role, not all schemas accessible.
example: user1 is the owner of a database, and thus is owner of the public schema. user2 has read access to the public schema.
as user1:
db_name=# select * from pgr_getTableName('edge_table');
sname | tname
--------+------------
public | edge_table
(1 row)
as user2:
db_name=> select * from pgr_getTableName('edge_table');
sname | tname
-------+-------
|
(1 row)
We would love to get a pull request against the develop branch if you have a suggestion for a fix for this. This is not realy a bug in my mind as it fails because the access privileges are set to tight to allow us to access the information needed. If there is a better way to do this I would love to hear about it.
Pleas read http://www.postgresql.org/docs/9.3/static/ddl-schemas.html 5.7.4 pgr_getTableName is designed to work for the pgrouting functions, and this functions not only read tables, also create and alter tables, indices, etc, so the user must have complete access to the schema. I think the schema owner must grant that access. read http://www.postgresql.org/docs/9.0/static/sql-grant.html for granting access
I have faced a problem on CentOS where the owner cannot use pgr_getTableName. It works as user postgres of course. I do not have the same problem on my Ubuntu.
The CentOS installation is CentOS-7.0-1406 minimal. Database is installed from PostgreSQL repo:
yum install epel-release
yum install http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-centos93-9.3-1.noarch.rpm
yum install postgresql93-server postgresql93-contrib postgis2_93 gdal unzip ntp wget
yum install --nogpgcheck pgrouting_93
\l shows the database owner is the current user, but still pgr_getTableName returns NULL. The output of \l is identical on my Ubuntu where pgr_getTableName works.
Does the user have access to information_schema.schemata and to information_schema.tables ?
At least can select from both. schemata is empty though when selecting as the user.
@vesse I need to ask around about CentOS postgres access privileges. Because the function uses both.
@cvvergara Thanks. For reference, I created the user and DB as user postgres using the provided commands:
createuser -P gisuser
createdb -O gisuser gis
Then, still as user postgres, created the extensions:
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;
CREATE EXTENSION IF NOT EXISTS pgrouting;
@vesse You say that it works on Ubuntu. Os the version of PostgreSQL the same on Ubuntu and CentOS?
So does this mean, the problem exists with 9.3 on CentOS and it works with 9.4 on Ubuntu? If the issue depends on the version of PostgreSQL, then it's better to know the versions. With Ubuntu/Debian there is usually a default version of PostgreSQL, so testing is easier, but with CentOS someone has to get the RPM's from the PostgreSQL channel, right? So it can be almost any version.
If you create a user and the user does not have access permissions to access the information schema than the command will fail. We will look into this at some point to see if there is a way around this problem, but I suspect that will not be the case. I think the best we can do is document the required grants need for the command. It might also be possible to generate those grants on the function as part of the create extension. I think we understand the issue. We just need time to research the solution. As I said above this is a postgresql grant problem and not a pgrouting specific problem. That said we should keep this open so it gets looked into.
This function was deprecated and is used internaly ith an undersocore prefix. This does not solve the problem stated in the issue.