Modify pg_hba.conf on Relational Datastore?

[JoëlHempenius3]

I recently configured GeoAnalitics Server on my Arcgis Enterprise and I run into a strange error

I want to copy data from a FeatureService (Extract Capability enabled) with data from my Enterprise Geodatabase to a Hosted FeatureService with the Relational Datastore where the data should be copied into.

Here is a screenshot of what I'm trying to do

Portal gives an error after a while:

In the Arcgis Server log from GeoAnalitics Server:

SEVERE

Nov 7, 2018, 3:32:57 PM

Job 'jc8cba0c08c65424786c2870a0ecc6b69' for tool 'CopyToDataStore' failed: FATAL: no pg_hba.conf entry for host "xxx.125.50.165", user "hsu_00jx3", database "db_1sc75", SSL off.

System/GeoAnalyticsTools.GPServer

The ipadress is from my GeoAnalitics Server Host, so the conclusion i draw from this message is that the geoAnalytics tools try to access the Postgres database which comes with the Esri Datastore directly.

I've worked with Postgres and Postgis before, so I know what this error means, and when it is my Enterprise geodabase, I would have modified this file and would not have come here.

But in this case, it is the Data Store which comes with my Base Deployment. And I was always told this database is managed by the Esri Software, so you should not touch these low level settings. Am I right here?

I used this page to configure GeoAnalitics server and made sure all firewall ports are open:

http://enterprise.arcgis.com/en/portal/latest/administer/windows/configure-the-portal-with-arcgis-ge...

Arcgis Enterprise 10.6.1:

1 server with Arcgis Server Hosting role + Portal

1 server with  Arcgis Datastore Relational + tile

1 server with Arcgis Datastore SpatioTemporal (Copying data to my  Spatiotemporal Big datastore with this same tool runs fine )

2 clustered Arcgis Servers for  mapservices from my Enterprise Geodatabase en Geoanalytics

So my questions:

Did I miss something while configuring the GeoAnalytics Server?

Should I manually fix the pg_hba,conf file?

If I update to a newer version of Arcgis Enterprise in the future, will my modified pg_hba.conf be preserved?

[LaurenceClinton]

Hi Joel,

From data store command line utilities located in the install directory/tools, please run the below command to see if this helps.

allowconnection.bat xxx.125.50.165   hsu_00jx3

[EduardoFernandez1]

Hello Laurence

I am experiencing the same FATAL error but after Federation and i can ressolve it. However i have some "under the bonnet" questions i am hoping someone at Esri could asisst with?

I have ArcGIS Server ( one site joined to two virtual machines joined, active\active high availability) and the site has one registered relational (managed) data store, residing on a third virtual machine. In addition, the ArcGIS Server is federated to Portal. Data Store, ArcGIS Server and Portal have the same CA root certificate and intermediate certificate assigned as trusted. One domain service account with full control permissions has been setup across all virtual machines and Portal and GIS Server rest end points are reachable from within each of these machines.

I unfederated ArcGIS Server from Portal and validated the data stores in AGS Manager and the data stores validated fine (green tick box, no fatal log errors). When i federate ArcGIS Server to Portal again and then re-validate the data stores in AGS Manager, i get a similar fatal error as Joel 

Type=warning, code =”110787” source “Data Store” process = “21232” thread = “1” methodName = “” machine “<machine name>.<domain>” user= “” elapse = “” requestID = “” > Failed to create index on ‘{0}’ org.postgres.util.PSQLException: FATAL : no pg_hba.config entry for host “XXX.X.X.1”, user “hsu_XaXaX”, database “db_AbAbA”, SSL off </msge>

I can run the allowconnection.bat and the fatal error no longer is logged and the data stores are validated ( green tick box) in AGS Manager and the data store log files are all ok. 

I repeate the above again. The second time, the fatal error had a different host, user and database parameters

I would like to understand

a) when does the connection to the managed\relational database get set? I thougth it was at the initial data store configuration? Do we have to manually set data connections to an existing data store after Federation?

b) By what script\mechanism\process is the different IPs, users and database parameters being created?

c) can these IPs, users and database parameters be static\never change?

d) is this accepted\designed behaviour by Enterprise?

Any help and insight is greatly appreciated

Thanks Ed

[LaurenceClinton]

Hi Ed,

Can you tell me what version of ArcGIS Data store you are using?

Thanks,

Laurence

[EduardoFernandez1]

Hey Laurence 

We are using ArcGIS DataStore 10.7.1.11595 ( used installer ArcGIS_DataStore_Windows_1071_169689) 

Thanks Ed

[pfoppe]

We had a similar issue with one of our implementations.  In our case, the machine was built out in our central, corporate data center.  We then boxed it up and shipped it to one of our offices and (at some point), trying to access hosted feature services failed with the error: 

FATAL: no pg_hba.conf entry for host "<new_ip_address>", user "<redacted>", database "<redacted">, SSL off

I used the allowconnection datastore command to forcibly add the (new) IP, User, DB connection - ArcGIS Data Store command utility reference—Portal for ArcGIS (10.8) | Documentation for ArcGIS Ente... 

Wierdly enough, we have a few similar implementations where we built out the system in a different location than where it is running and they are not experiencing the same issues...  

And concerning that this is tied to an IP address as we do NOT set static/reserved IP addresses on our machines unless there is a known need (like firewall ACL rule or DNS alias resolution).  

This is a pretty straight forward 10.7.1 setup with all ArcGIS Enterprise components running on the same host (IIS, web-adaptor for portal & server, portal, server, datastore).  No image server.  No Geoanalytics. 

Jonathan Quinn‌ - thoughts on this?  Thanks!!

[EduardoFernandez1]

Hello Patrick

Using the allowconnection tool does work. I did find though that each time the Data Store service was restarted, I had to re-add the new host IP and user. It became a bit cumbersome ( plus this is not usual behavior).

For me, the problem no longer exists. The ArcGIS Server machine names were set up to use dns names rather than the internal machine name in ArcGIS Server Administration. This rename setting won't retain because  when the ArcGIS Server service is stop\started or the server machine itself is restarted, the name will always be set to the internal machine name set as the OS level of the server.

We changed the names back to their internal machine names in ArcGIS Administrator Directory, unfederated Portal\ArcGIS Server, re-federated Portal\ArcGIS Server and then re-registered the managed database Data Store with ArcGIS Server ( using the unregisterdatastore and registerdatastore utility tools in command line on the data store machines). Validated the data stores in ArcGIS Server Manager and everything resulted as expected. No error message in the log files either.

Ive been monitoring this for 2 weeks now and I no longer receive the error and publishing of hosted services is resulting in data stored in ArcGIS DataStore.

Cheers Ed

[Rudy_Stricklan]

You can add IPv4 connecting addresses at the end of the pg_hba.conf file. Although certainly not recommended for production environments, an entry of

host       all       all        0.0.0.0/0       md5

will allow all addresses and enable you to see if IP address restrictions are your problem. Connection parameters are documented at https://www.postgresql.org/docs/12/auth-pg-hba-conf.html.

[AndresCastillo]

documentation regarding Rudy's comment:

Error Connecting to database FATAL : no pg_hba.conf entry for host"x.x.x.x", user"jiradbuser", datab...