CData connectors
Postgres

Postgres

Overview

This page contains the setup guide and reference information for the Postgres source connector. We utilise the official CData Python (opens in a new tab) connector to ingest your data.

Features

FeatureSupportedNotes
Full Refresh SyncYes
Incremental SyncYes

Getting started

Requirements and prerequisites

  • Use Postgres v9.3.x or above
  • Create a dedicated read-only user
  • Whitelist the Y42 production IP address, which is 35.198.72.34.

Setup guide

Step 1: (Optional) Create a dedicated read-only user

We recommend creating a dedicated read-only user for better permission control and auditing. Alternatively, you can use an existing Postgres user in your database.

  1. To create a dedicated user, run the following command:


    _10
    CREATE USER <user_name> PASSWORD 'your_password_here';

  2. Grant access to the relevant schema:


    _10
    GRANT USAGE ON SCHEMA <schema_name> TO <user_name>

  3. Grant the user read-only access to the relevant tables:


    _10
    GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <user_name>;

  4. Allow user to see tables created in the future:


    _10
    ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <user_name>;

Step 2: Set up the Postgres source

  1. Create a new source, select CData - Postgres from the Source type dropdown and enter a name for it.
  2. On the source setup page, create a new Postgres secret, enter a unique name for it.
  3. For the Host, Port, and DB Name, enter the hostname, port number, and name for your Postgres database.
  4. For User and Password, enter the username and password you created in Step 1.
  5. (Optional) If you want to connect via SSH tunnel, select "SSH Tunnel Method": SSH Key Authentication or Password Authentication.

Step 3: (Optional) Connect via SSH tunnel

In case of an SSH connection, select "SSH Tunnel Method" other than "No Tunnel" and configure SSH tunneling:

  1. For SSH Tunnel Jump Server Host, enter the hostname or IP address for the intermediate (bastion) server that Y42 will connect to.
  2. For SSH Connection Port, enter the port on the bastion server. The default port for SSH connections is 22.
  3. For SSH Login Username, enter the username to use when connecting to the bastion server. Note: This is the operating system username and not the Postgres username.
  4. For authentication:
    • If you selected SSH Key Authentication, set the SSH Private Key to the private key that you are using to create the SSH connection. To generate a private key for SSH Tunneling, execute the following command:

      _10
      ssh-keygen -t rsa -m PEM -f myuser_rsa

      The command produces the private key in PEM format and the public key remains in the standard format used by the authorized_keys file on your bastion server. Add the public key to your bastion host to the user you want to use for data sync. The private key is provided via copy-and-paste to the configuration screen.
    • If you selected Password Authentication, enter the password for the operating system user to connect to the bastion server. Note: This is the operating system password and not the Postgres password.
PinterestSalesforce