How set ssl on for postgis reader/writer?

Hi @henrikuggla. FME 2015+ supports SSL with Postgres / PostGIS however there aren't parameters for configuring it. If you require the ability to configure, there is an enhancement request PR 52876, which I can add you to?

Hi @NatalieAtSafe. I don't understand what you mean with "FME 2015+ supports SSL with Postgres / PostGIS". Both reader and writer fails if I set Postgresql to require ssl. The FME log clearly says "ssl off". How should I do to make it work?

Here is my log:

Creating reader for format: PostGIS
Trying to find a DYNAMIC plugin for reader named `POSTGIS'
FME API version of module 'POSTGIS' matches current internal version (3.8 20170315)
Creating reader for format: PostGIS
Trying to find a DYNAMIC plugin for reader named `POSTGIS'
FME API version of module 'POSTGIS' matches current internal version (3.8 20170315)
Opening POSTGIS reader for dataset 'geodata'
Error connecting to PostgreSQL database(host='10.3.1.147', port='15432', dbname='geodata', user='titta', password='***'): 'FATAL:  no pg_hba.conf entry for host "10.42.32.75", user "titta", database "geodata", SSL off
'
Successfully closed POSTGIS database reader
Reader failed in open() call
Failed to obtain any schemas from reader 'POSTGIS' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information
UniversalReader -- readSchema resulted in 0 schema features being returned
Reader Parameter(0) = >geodata<
Reader Parameter(1) = >READ_SCHEMA_MODE<
Reader Parameter(2) = >SCHEMALIST<
Reader Parameter(3) = >HOST<
Reader Parameter(4) = >10.3.1.147<
Reader Parameter(5) = >PORT<
Reader Parameter(6) = >15432<
Reader Parameter(7) = >USER_NAME<
Reader Parameter(8) = >titta<
Reader Parameter(9) = >PASSWORD<
Reader Parameter(10) = >********<
Reader Parameter(11) = >_MERGE_SCHEMAS<
Reader Parameter(12) = >YES<
Reader Parameter(13) = >RETRIEVE_ALL_TABLE_NAMES<
Reader Parameter(14) = >YES<
Reader Directive(0) = >RUNTIME_MACROS<
Reader Directive(1) = >READ_SCHEMA_MODE,SCHEMALIST,HOST,10.3.1.147,PORT,15432,USER_NAME,titta,PASSWORD,********,_MERGE_SCHEMAS,YES,RETRIEVE_ALL_TABLE_NAMES,YES<
Configuration object contents:
FME Temporary Folder: /tmp
mapping file Line 0: `APPLICATION' `FME_WORKBENCH'
mapping file Line 1: `AUTOREG' `YES'
mapping file Line 2: `LOG_ENCODING' `UTF-8'
mapping file Line 3: `LOG_MAX_FEATURES' `-1'
mapping file Line 4: `LOG_APPEND' `NO'
mapping file Line 5: `FME_HOME' `/opt/fme-desktop-2017/'
mapping file Line 6: `FME_SESSION_USED' `TRUE'
mapping file Line 7: `R_4_METAFILE' `POSTGIS'
mapping file Line 8: `R_4_DATASET' `geodata'
mapping file Line 9: `R_4_READ_SCHEMA_MODE' `SCHEMALIST'
mapping file Line 10: `R_4_HOST' `10.3.1.147'
mapping file Line 11: `R_4_PORT' `15432'
mapping file Line 12: `R_4_USER_NAME' `titta'
mapping file Line 13: `R_4__MERGE_SCHEMAS' `YES'
mapping file Line 14: `R_4_RETRIEVE_ALL_TABLE_NAMES' `YES'
mapping file Line 15: `R_4_READ_SCHEMA_MODE' `SCHEMALIST'
mapping file Line 16: `R_4_HOST' `10.3.1.147'
mapping file Line 17: `R_4_PORT' `15432'
mapping file Line 18: `R_4_USER_NAME' `titta'
mapping file Line 19: `R_4__MERGE_SCHEMAS' `YES'
mapping file Line 20: `R_4_MERGE_SCHEMAS' `YES'
mapping file Line 21: `R_4_RETRIEVE_ALL_TABLE_NAMES' `YES'
mapping file Line 22: `SCHEMA_TIME' `YES'
mapping file Keyword: `SCHEMA_TIME'                            occurs 1 time(s)
mapping file Keyword: `R_4_HOST'                               occurs 2 time(s)
mapping file Keyword: `R_4_PORT'                               occurs 2 time(s)
mapping file Keyword: `R_4_PASSWORD'                           occurs 2 time(s)
mapping file Keyword: `R_4_USER_NAME'                          occurs 2 time(s)
mapping file Keyword: `FME_SESSION_USED'                       occurs 1 time(s)
mapping file Keyword: `AUTOREG'                                occurs 1 time(s)
mapping file Keyword: `LOG_MAX_FEATURES'                       occurs 1 time(s)
mapping file Keyword: `R_4_METAFILE'                           occurs 1 time(s)
mapping file Keyword: `LOG_APPEND'                             occurs 1 time(s)
mapping file Keyword: `APPLICATION'                            occurs 1 time(s)
mapping file Keyword: `FME_HOME'                               occurs 1 time(s)
mapping file Keyword: `R_4_READ_SCHEMA_MODE'                   occurs 2 time(s)
mapping file Keyword: `R_4_RETRIEVE_ALL_TABLE_NAMES'           occurs 2 time(s)
mapping file Keyword: `R_4__MERGE_SCHEMAS'                     occurs 2 time(s)
mapping file Keyword: `R_4_MERGE_SCHEMAS'                      occurs 1 time(s)
mapping file Keyword: `R_4_DATASET'                            occurs 1 time(s)
mapping file Keyword: `LOG_ENCODING'                           occurs 1 time(s)
UniversalReader -- readSchema resulted in 0 schema features being returned
Failed to obtain any schemas from reader 'POSTGIS' from 1 datasets. This may be due to invalid datasets or format accessibility issues due to licensing, dependencies, or module loading. See logfile for more information
Successfully closed POSTGIS database reader

Are you sure this isn't an issue with permissions for your FME host machine not having access to the Postgres database?

From your log:

'FATAL: no pg_hba.conf entry for host "10.42.32.75", user "titta", database "geodata", SSL off

This part of the log points to your FME coming from 10.42.32.75 not being listed in the pg_hba.conf and therefore being rejected.

So you may want to change this as a way of testing

<PostgreSQLDir>\\data\\pg_hba.conf.

• Change the following lines:

host all all ::1/128 md5

to:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

@todd_davis thanks for your reply but I'm trying to get FME to connect to Postgresql using ssl encryption. Your pg_hba.conf examples does NOT require ssl. To have Postgresql require ssl you substitute host with hostssl (and you would obviously also need to configure ssl in postgresql.conf), and then FME fails.

Hi @NatalieAtSafe. I've found that ssl is not working in the Linux version of FME desktop. It works in the Windows version.

Hi @henrikuggla and @uggla,

I am pleased to inform you that Development has fixed this issue. PostgreSQL should now have SSL support on Linux, in FME 2018.0 Builds 18195+. 2018 Beta build are available from https://www.safe.com/downloads/beta/, and 2018.0 will be released this March.

Please let me know whether you have any questions and apologies for any inconvenience.

This was the solution for me @todd_davis