Skip to content

Configuring PostgreSQL schemas visible to Scan

When scanning a database entry point, the configured user determines which tables and views will be scanned in the specified instance (for Oracle) or database (for PostgreSQL and MS SQL Server).

By default in PostgreSQL and therefore in PostGIS, the public schema is created in each new database, and any user who can connect to it sees this schema. Best practice suggests not to store data in this schema, but to create several schemas on which the rights of users or groups of users are refined, guaranteeing greater granularity of use and isolation of security.

FME relies on rights andsearch_path to establish its connection to a PostGIS database. Sometimes, the database is not properly configured.

For Scan FME to analyze the tables in a schema, :

  • the configured user has read rights on tables ;
  • the user path contains the schemas containing the tables.

Demonstration and ordering

Let's take the example of a PostgreSQL 9.3 database with a PostGIS 2.1.3 database called geofla, which stores the administrative boundaries of the lIGN France product and is structured in 9 schemas, 6 of which correspond to metropolitan France and each DOM, one(global) being the merged whole and the last 2 being the schemas inherent to the DBMS and its extensions(public and topology).

The aim is to scan the tables of the 6 data schemas with the isogeo_demo user belonging to the isogeo_editor group.

PGAdmin 3 - GeoFLA structure

1. Configure entry point

Follow the indications given here, to obtain something similar to this:

APP - PostGIS entry point

2. Notice that nothing is displayed

If you run the scan, you'll see that the only table returned is public.raster_columns, a system table with the PostGIS extension. It is in error, as it contains no geographic entities.

APP - 1st scan of entry point

3. Check that the user is accessing the tables

First of all, you need to check what the user "sees", via your psql console (or via a graphical interface such as pgAdmin):

GRANT USAGE ON SCHEMA guadeloupe, guyane, lareunion TO isogeo_editor;
GRANT USAGE ON SCHEMA guadeloupe, guyane, lareunion TO isogeo_demo;
GRANT SELECT ON ALL TABLES IN SCHEMA guadeloupe, guyane, lareunion to isogeo_demo;

Using QGIS, we can see that the isogeo_demo user has access to the tables of the guadeloupe, guyane and lareunion schemas. Note that not all software connects to a PostgreSQL database in the same way. For example, QGIS does not require the search_path to be configured. FME does.

QGIS - PostGIS entry point

4. Change user path

To change the search_path, run :

ALTER ROLE isogeo_demo IN DATABASE geofla SET search_path TO $user, public, guadeloupe;

If you run the scan again, you will see that the tables in the Guadeloupe schema, and only the Guadeloupe schema, are scanned:

APP - 2nd scan of entry point

5. Consequences and illustrations

Taking into account this database configuration finesse actually enables us to refine the cataloguing process by choosing the right granularity for entry points at the time of scanning.<br > In addition, we can see that variations in the search_path on the same entry point do not change the "memory" of the FME scan:

Guyana scheme only:

ALTER ROLE isogeo_demo IN DATABASE geofla SET search_path TO $user, public, guyane;

APP - 3rd scan of entry point

Only the lareunion scheme:

ALTER ROLE isogeo_demo IN DATABASE geofla SET search_path TO $user, public, lareunion;

APP - 4th scan of entry point

The 3 diagrams :

ALTER ROLE isogeo_demo IN DATABASE geofla SET search_path TO $user, public, guadeloupe, guyane, lareunion;

APP - 5th scan of entry point

Order summary

If we had wanted to scan all 6 schemas in which data is stored, we would have done :

/* Granting access to role and rolegroup to wanted schemas*/
GRANT USAGE ON SCHEMA guadeloupe, guyane, lareunion, martinique, mayotte, metropole TO isogeo_editor;
GRANT USAGE ON SCHEMA guadeloupe, guyane, lareunion, martinique, mayotte, metropole TO isogeo_demo;
GRANT SELECT ON ALL TABLES IN SCHEMA guadeloupe, guyane, lareunion, martinique, mayotte, metropole to isogeo_demo;

/* Setting the search_path with the wanted schemas */
ALTER ROLE isogeo_demo IN DATABASE geofla SET search_path TO $user, public, guadeloupe, guyane, lareunion, martinique, mayotte, metropole;

Further details

  • the user path must contain public, otherwise FME will not be able to access it;
  • the $user corresponds to the user's table. Optional ;

  • it is possible to reset a user's search_path to the DBMS default configuration:

    ALTER USER isogeo_demo RESET search_path;

  • to view a user's search_path, connect to a database with that user and run :

    show search_path;

Resources