Search

In the Postgis/Postgres reader tables from a different schema don't show up or it doesn't appear to display the full list of tables.

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

Symptom

The user has access to Postgis/Postgresql tables in a different schema/catalog to their default one. FME doesn't display these tables in the tablelist picker.

The PostGIS and PostGreSQL readers do not return a full table list.

Cause

The table list requests the schema search path for the specified user. For user 'bob', this gives 'public' and 'bob'.

Resolution

The user's search path needs to be modified to include all the schema/catalogs that they have access to. See the commands below.

Here's an example of how to set the search path for a user:

 alter user postgisuser set search_path = '$user','public','cadastre';

The search path is then set to this value whenever the user logs in.

Here's how to change it back to the default:

 alter user postgisuser reset search_path;

Here's how to get the search path:

 show search_path;

You can see the search path in the pg_user table:

 select * from pg_user;

In addition the user 'postgisuser' may not currently have access to some
key PostGIS metadata tables. Therefore this user can't actually read PostGIS
data via FME (even though the tables show up in the tablelist).

As a sufficiently powerful user, you may wish to execute:

GRANT ALL ON TABLE public.geometry_columns TO PUBLIC; 

GRANT ALL ON TABLE public.spatial_ref_sys TO PUBLIC;

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.