pgr_getTableName fails when current user is not owner of a table's schema

[talin-trulia]

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)

[woodbri]

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.

[cvvergara]

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

[vesse]

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.

[cvvergara]

Does the user have access to information_schema.schemata and to information_schema.tables ?

[vesse]

At least can select from both. schemata is empty though when selecting as the user.

[cvvergara]

@vesse
I need to ask around about CentOS postgres access privileges. Because the function uses both.

[vesse]

@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;

[dkastl]

@vesse You say that it works on Ubuntu. Os the version of PostgreSQL the same on Ubuntu and CentOS?

[vesse]

@dkastl No, not anymore. Ubuntu was upgraded to 9.4.1.