Connectors and Connections > Data Ingestion and Replication connection properties > PostgreSQL connection properties
  

PostgreSQL connection properties

Create a PostgreSQL connection to securely read data from or write data to PostgreSQL.

Prepare for authentication

You can configure database or Kerberos authentication method to connect to a PostgreSQL database.
Before you configure the connection properties, you need to keep the authentication details handy based on the authentication type that you want to use.
To configure database authentication, you need the user name, password, host name, port, and database name from your PostgreSQL account. To configure Kerberos authentication, you need the service principal name, host name, port, and database name from your PostgreSQL account.
To configure Kerberos authentication, you need to perform certain prerequisite tasks.

Prepare for Kerberos authentication

To connect to PostgreSQL databases with Kerberos authentication, place the required Kerberos configuration files on the Secure Agent machine.
When you configure Kerberos authentication to connect to PostgreSQL, consider the following guidelines:

Configuring Kerberos authentication

Before you use Kerberos authentication to connect to PostgreSQL on Linux or Windows, the organization administrator needs to perform the prerequisite tasks.
  1. 1To configure the Java Authentication and Authorization Service configuration file (JAAS), perform the following tasks:
    1. aCreate a JAAS configuration file on the Secure Agent machine.
    2. bAdd the following entries to the JAAS configuration file:
      3. JDBC_DRIVER_01 {
      com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;
      };
  2. 2To configure the krb5.conf file, perform the following tasks:
    1. aCreate a krb5.conf file on the Secure Agent machine.
    2. bAdd the details of the Key Distribution Center (KDC) and admin server to the krb5.conf file in the following format:
      3. [libdefaults]
      default_realm = <Realm name>
      forwardable = true
      ticket_lifetime = 24h

      [realms]
      <REALM NAME> = {
      kdc = <Location where KDC is installed>
      admin_server = <Location where KDC is installed>
      }
      [domain_realm]
      <domain name or host name> = <Domain name or host name of Kerberos>
      <domain name or host name> = <Domain name or host name of Kerberos>
  3. 3Set the following environment variables on the Secure Agent machine.
    4. For more information about the required environment variables, see Setting environment variables.
  4. 4Restart the Secure Agent.
  5. 5To generate the credential cache file on the Secure Agent machine and use Kerberos authentication to connect to PostgreSQL, perform the following tasks:
    1. aFrom the command line on the Secure Agent machine, run the following command and specify the PostgreSQL user name and realm name:
      2. Kinit <user name>@<realm_name>
    2. bWhen prompted, enter the password for the Kerberos principal user.

Setting environment variables

To use Kerberos authentication to connect to PostgreSQL, you need to set the required environment variables on the Secure Agent machine.
Run the following commands to set the environment variables:
After you set the environmental variables, you need to restart the Secure Agent.
Alternatively, you can add the KRB5_CONFIG and JAASCONFIG environment variables when you create a PostgreSQL connection.
To add the environment variables when you configure a connection with Kerberos authentication, you need to add the KRB5_CONFIG and JAASCONFIG properties in the Additional Kerberos Properties field in a PostgreSQL connection.
For example, add the properties in the following format:
KRB5_CONFIG=<Absolute path of the Kerberos configuration file>\krb5.conf;JAASCONFIG=<Absolute path of the JAAS config file>\<File name>.conf
Note: Ensure that you separate each key-value pair with a semicolon.

Connect to PostgreSQL

Let's configure the PostgreSQL connection properties to connect to PostgreSQL.

Before you begin

Before you get started, get the required information from your PostgreSQL account based on the authentication method that you want to use.
Check out Prepare for authentication to learn more about the authentication prerequisites.

Connection details

The following table describes the PostgreSQL connection properties:
Property
Description
Connection Name
Name of the connection.
Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -,
Maximum length is 255 characters.
Description
Description of the connection. Maximum length is 4000 characters.
Type
PostgreSQL
Runtime Environment
The name of the runtime environment where you want to run tasks.
You cannot run a database ingestion and replication task on a Hosted Agent or in a serverless runtime environment.

Authentication types

You can configure database or Kerberos authentication methods to connect to PostgreSQL databases.
Select the required authentication type and then configure the authentication-specific parameters.

Advanced settings

The following table describes the advanced connection properties:
Property
Description
Schema Name
The schema name.
If you don't specify the schema name, all the schemas available in the database are listed when you import the source object in Data Integration.
Connection Environment SQL
The SQL statement to set up the database environment when you connect to the database. The database environment applies for the entire session that uses this connection. For example, you can enter this statement to set the time zone: SET timezone to 'America/New_York';
Additional Connection Properties
Additional connection parameters that you want to use.
Provide the connection parameters as semicolon-separated key-value pairs.

Encryption types

The encryption method determines if the Secure Agent and the PostgreSQL database server exchange encrypted data. If you do not want to establish a connection using SSL, select noEncryption. PostgreSQL establishes a connection without using SSL. Data is not encrypted. Default is noEncryption.
To use SSL, select the required encryption method and then configure the encryption-specific parameters.