Skip to main content
Question

How set ssl on for postgis reader/writer?

How can I, in workbench, connect to postgis with ssl on? According to this thread it should be possible but I can't find the setting anywhere.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

10 replies

fmelizard
Safer
Forum|alt.badge.img+22

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?

Forum|alt.badge.img
  • uggla
  • June 14, 2017

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?

Forum|alt.badge.img
  • uggla
  • June 15, 2017

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
todd_davis
Influencer
Forum|alt.badge.img+23

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 127.0.0.1/32 md5

host all all ::1/128 md5

to:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

Forum|alt.badge.img
  • uggla
  • June 19, 2017

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 127.0.0.1/32 md5

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.

fmelizard
Safer
Forum|alt.badge.img+22

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
Hi @uggla. Thank you for your reply. I have spoken with development, and have requested that they look into the issue you are experiencing (issue tracked as PR 78189). I'll follow up here when I receive a reply from development. 

 

Forum|alt.badge.img
  • uggla
  • June 29, 2017

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

fmelizard
Safer
Forum|alt.badge.img+22

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

Thank you for the update @uggla. I have informed Development who will look into this issue.

 

fmelizard
Safer
Forum|alt.badge.img+22

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.

salvaleonrp
Enthusiast
Forum|alt.badge.img+23

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 127.0.0.1/32 md5

host all all ::1/128 md5

to:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

This was the solution for me @todd_davis

Community Stats

32,982
Posts
124,993
Replies
41,894
Members
Terms & ConditionsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.