SAS Access to Postgres | Error

[MariaD]

Hi folks, 

We have a SAS installed on Linux (9.4M7). Until last Friday we connected to postgres without any problem. After that, we received the following error: 

CLI error trying to establish connection: FATAL: no pf_hba.conf entry for host x.x.x.x, user yyyy, database zzzz, no encryption. 

We used other tools to connect to Postgres too, and on it the solution was add the following options on the connection string:

SSL=true

SSLFACTORY= org.postgrsql.ssl.NonValidatingFactory

I tried to add it to the current connection but SSLFACTORY option was not recognized.

Our current connection string is:

proc sql;

  connect to postgres (server=xxxx port=35432 database=xxx user=xxx password=xxx)

Any idea how to pass the SSLFACTORY option?

Regards, 

[Patrick]

I believe you can pass these two database parameters via SAS libname/connection option CONOPTS='connection_options'

When copy/pasting the error message into chatGPT or Copilot the instructions are to update on the Postgres side. Below the first step as received as answer:

Here are some steps to resolve this issue:

1. Edit the pg_hba.conf File:

   • Log into your PostgreSQL server.
   • Locate the pg_hba.conf file, typically found in the data directory (e.g., /var/lib/pgsql/data/).
   • Add an entry for your host. For example:

     host    all             all             x.x.x.x/32            md5
     

   • Replace x.x.x.x with the actual IP address you’re connecting from.

......