I have been trying to connect a local PostgreSQL server to a Power Automate Flow through the On-premise Data Gateway, but I have been unable to. After a few days of troubleshooting, it appears that the issue is that encryption is not enforced upon the connection. My PostgreSQL server requires encryption and it does not appear the Power Automate PostgreSQL connector gives the option.
When attempting to make a connection I get the error Credentials are missing or not valid. inner exception: The credentials provided for the PostgreSQL source are invalid. (Source at server.com:5432;db.), but when the logs on the On-premise Data Gateway are reviewed the following logs are found:
- [DataMovement.PipeLine.GatewayDataAccess] DataSourceReference:<pi>{"kind":"PostgreSQL","path":"server.com:5432;db"}</pi>, CredentialDetails.EncryptedConnection:NotEncrypted, isEncryptedConnection:False
- [{"kind":"PostgreSQL","path":"server.com:5432;db","AuthenticationKind":"UsernamePassword","Username":"un","Password":"HIDDEN","PrivacySetting":"None"}]';fastcombine=True;allownativequeries=True;cachepath=$MemoryCache$;maxcachesize=16777216:[Basic] Encrypted Credential information omitted</pi>]
- Message: 28000: no pg_hba.conf entry for host "IP.IP.IP.IP", user "un", database "db", SSL off; StackTrace: at Npgsql.NpgsqlConnector.
Oddly the PowerBI online PostgreSQL connector connects just fine (with the encryption option turned on), but unlike Power Automate/PowerApps connectors, PowerBI connectors can not be shared with the former. I can see the PowerBI online PostgreSQL connector make the TLS connection in Wireshark, but I the Power Automate/PowerApps connector never even attempts a TLS connection.
It is my theory that the Npgsql connection string the Power Automate/PowerApps connector is attempting to use is missing the SSL Mode setting, hence Encrypted Credential information omitted. I have attempted to review/troubleshoot all the different configuration settings/files for On-premise Data Gateway, .NET, Npgsql, and the PostgreSQL connector, but I have not made any headway in resolving this issue. Any assistance is greatly appreciated. Thanks in advance!!
