MuleSoft Connector for PostgreSQL

Build 21.0.7930

Establishing a Connection

This section describes how to connect to PostgreSQL from your AnyPoint Studio project. You can use wizards or the XML editor in AnyPoint Studio to define connection properties in a global element. Global elements can be reused across other building blocks in your project.

Create a Connection in the Visual Editor

Complete the following steps to visually add the connector to your project and create a connection:

  1. Create a new Mule Project in Anypoint Studio.
  2. From the palette, drag the connector into your Mule flow.
  3. On the Message Flow canvas, double-click the connector icon to open the connector properties in the Properties pane.
  4. In the General section, click Add and define the PostgreSQL connection properties. Note that the user name and Password properties always appear here. The user name must be set as it is used as the key for the connection if connection pooling is enabled.

Create a Connection in the XML Editor

Set connection properties in the <cdata-postgresql:config> element in the root of the project's XML.

Note: The user name attribute must always be included here and serves as the key for the connection if connection pooling is enabled.

  <cdata-postgresql:config name="CData_PostgreSQL_Configuration" username="MyName" connectionProperty1="MyProperty" ... doc:name="CData PostgreSQL: Configuration"/> 

Connecting to PostgreSQL

The following connection properties are usually required in order to connect to PostgreSQL.

  • Server: The host name or IP of the server hosting the PostgreSQL database.
  • User: The user which will be used to authenticate with the PostgreSQL server.
  • Password: The password which will be used to authenticate with the PostgreSQL server.

You can also optionally set the following:

  • Database: The database to connect to when connecting to the PostgreSQL Server. If this is not set, the user's default database will be used.
  • Port: The port of the server hosting the PostgreSQL database. 5432 by default.

The connector may provide secure communication with PostgreSQL server using SSL encryption. You can optionally turn on SSL encryption by setting UseSSL to true.

You can also leverage SSL authentication to connect to PostgreSQL data. For that configure the following connection properties:

  • SSLClientCert: Set this to the name of the certificate store for the client certificate. Used in the case of 2-way SSL, where truststore and keystore are kept on both the client and server machines.
  • SSLClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
  • SSLClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
  • SSLClientCertType:; The certificate type of the client store.
  • SSLServerCert: The certificate to be accepted from the server.

The following is the example connection string to connect PostgreSQL using standard user/password credentials and active SSL encryption:

User=username;Password=password;Server=host.mypostgres.com;Database=usernamedb;UseSSL=true;"

Authenticating to PostgreSQL

There are several authentication methods available for connecting to PostgreSQL including standard authentication, Azure Active Directory OAuth, Azure Active Directory Password and Azure Active Directory MSI authentication.

Connect Using Standard Authentication

Set the AuthScheme to Password in order to connect to PostgreSQL with login credentials. The PostgreSQL Server may be setup to authenticate with md5, password, Kerberos and SASL (particulary, SCRAM-SHA-256). The respective authentication method is setup in the pg_hba.conf file on the PostgreSQL Server. You may find instructions about authentication setup on the PostgreSQL Server here.

The following is an example connection string:

AuthScheme=Password;Password=mypassword;Server=mypostgres.com;Database=mydatabase;

Password is the default authentication mechanism the driver uses to connect PostgreSQL Server. You may omit AuthScheme in the above example.

Authenticating with Kerberos

The authentication with Kerberos is initiated by PostgreSQL Server when the CData MuleSoft Connector for PostgreSQL is trying to connect to it. You should setup Kerberos on the PostgreSQL Server to activate this authentication method. Once you have Kerberos authentication setup on the PostgreSQL Server, see Using Kerberos for details on how to authenticate with Kerberos by the connector.

Authenticating using Azure AD

Azure AD is a connection type that goes through OAuth. Set your AuthScheme to AzureAD and see Using OAuth Authentication for an authentication guide.

The following is an example connection string:

AuthScheme=AzureAD;InitiateOAuth=GETANDREFRESH;User=user@tenant.onmicrosoft.com;Server=mydb.postgres.database.azure.com;Database=postgres;Azuretenant=b6f917e8-a8e2-416b-8283-cc6ebfe1ca12;UseSSL=true;

Authenticating using Azure Password

To connect using your Azure credentials directly, specify the following connection properties:

  • AuthScheme: Set this to AzurePassword.
  • User: Set this to your user account you use to connect to Azure.
  • Password: Set this to the password you use to connect to Azure.

AuthScheme=AzurePassword;InitiateOAuth=GETANDREFRESH;User=user@tenant.onmicrosoft.com;Password=mypassword;Server=mydb.postgres.database.azure.com;Database=postgres;Azuretenant=b6f917e8-a8e2-416b-8283-cc6ebfe1ca12;UseSSL=true;

Authenticating using MSI Authentication

If you are running PostgreSQL on an Azure VM, you can leverage Managed Service Identity (MSI) credentials to connect:

  • AuthScheme: Set this to AzureMSI.

The MSI credentials will then be automatically obtained for authentication.

AuthScheme=AzureMSI;User=msiroleatpostgres;Server=mydb.postgres.database.azure.com;Database=postgres;Azuretenant=b6f917e8-a8e2-416b-8283-cc6ebfe1ca12;UseSSL=true;

Configure Connector Operations

The connector is an operation-based connector; its operations correspond to SQL statements and expose other functionality such as Schema Discovery. See Using the Connector (Mule 3.8+) for more information on executing SQL to PostgreSQL in Mule applications.

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930