Setting Postgis Metadata Table Permissions

Symptom

Unexpected errors are received when connecting to PostGIS and PostGIS Raster tables that appear to show in the tablelist.

Cause

If the permissions on the metadata tables are not set correctly certain users may be unable to query these tables from FME, even though these tables may show up in the table list. These tables are usually in the "PUBLIC" schema, however certain users may not have proper access to them. This can lead to error messages when attempting to read from or write to spatial or raster tables.

A common example of this error is: Error was 'ERROR: Geometry SRID (900914) does not match column SRID (XXXX)...'. This error occurs when there is no match in the PostGIS spatial reference column for the coordinate system of the data being written.

If FME cannot find a matching coordinate system, it will try to insert a new one into the spatial_ref_sys table. However, if the user logged in doesn't have write permissions for the spatial_ref_sys table, FME cannot do this.

Resolution

The following commands can be run to correct this:

GRANT ALL ON TABLE public.geometry_columns TO PUBLIC
GRANT ALL ON TABLE public.spatial_ref_sys TO PUBLIC
GRANT ALL ON TABLE public.raster_columns TO PUBLIC
GRANT ALL ON TABLE public.raster_overviews TO PUBLIC

See this article about the search path for tables that are not showing up in the table list.