# Setup and administration for PostgreSQL gateway

Use the PostgreSQL gateway to ingest data into Cognite Data Fusion (CDF) from popular ETL solutions. This service provides a PostgreSQL interface to CDF and the CDF resource types appear as tables in the PostgreSQL database. You can ingest data directly into CDF resources, like assets, events, and datapoints and to Cognite RAW.

NOTE

  • Azure Data factory is the officially supported ETL tool.
  • PostgreSQL gateway is only intended for ingestion. Querying data from CDF for analysis and visualization is not supported.

This article explains how an Azure Active Directory (AD) administrator can control access to ingesting data into CDF from your existing ETL tools, such as Microsoft Azure Data Factory, that support writing to PostgreSQL, using the Cognite PostgreSQL gateway.

When you have registered the PostgreSQL gateway, users can sign in with their organizational ID to integrate data in a CDF project.

In this article:

NOTE

To perform the steps below, you need to be an administrator of Azure AD.

# Before you start

Make sure you have registered the Cognite API and the CDF portal application in Azure AD and set up Azure AD and CDF groups to control access to CDF data.

# Step 1: Register an app in Azure AD to use with PostgreSQL gateway

  1. Sign in to the Azure portal (opens new window) as an admin.

  2. If you have access to multiple tenants, use the Directory + subscription filter Directory + subscription filter in the top menu to select the tenant in which you want to register an application.

  3. Search for and select Azure Active Directory.

  4. Under Manage, select App registrations > New registrations.

  5. In the Register an application window, enter the app name, and then select Register.

  6. Copy and make a note of the Application (client) ID. This value is required to create user credentials for the PostgreSQL gateway.

  7. Under Manage, select Certificates & secrets > New client secret.

    ADD certificates and secrets

  8. Enter a client secret description and an expiry time, and then select Add.

  9. Copy and make a note of the client secret in the Value field.

NOTE

Make sure you copy this value now. This value will be hidden after you leave this page.

# Step 2: Create a group in Azure AD and add the registered app as its member

  1. Open the overview window in Azure AD and select Manage > Groups.

  2. Create a group, read more here.

  3. Open the group. Under Manage, select Members > Add members.

  4. Find the app you created above and click Select.

  5. Return to the overview, and then copy and make a note of the Object Id.

    ADD groups

  1. Sign in to Cognite Data Fusion (opens new window) as an admin.

  2. In the top menu, select Manage & Configure > Manage access.

  3. In the Access management window, select Groups > Create new group.

  4. In the Create a new group window, enter a Unique name for the group.

  5. Add relevant capabilities. The minimum requirement is to add the project:list and groups:list capabilities.

  6. Link the CDF group to an Azure AD group:

    1. In the Source ID field, enter the Object Id for the AAD group exactly as it exists in AAD.

    2. In the Source name field, enter the name of the group in Azure AD.

    Create new group with link to AAD group object ID

  7. Select Create.

# Step 4: Create user credentials for the PostgreSQL gateway

  1. Send a POST request to this server https://skywarp.{cluster}.cognite.ai/create.

    clusteris where your CDF instance is installed. If you don't know the cluster name, contact Cognite support.

  2. Get the Bearer token from Azure AD for authentication. To get a bearer token from Postman, see the illustration below.

  3. Specify these OIDC credentials in the request body.

{
"auth":
    {
    "token_url": "https://login.microsoftonline.com/<YOUR_AAD_TENANT_ID>/oauth2/v2.0/token",
    "client_id": "<YOUR_AAD_CLIENT_ID>",
    "client_secret": "<YOUR_AAD_CLIENT_SECRET>",
    "scope": "<BASE URL>/.default"
    },
    "project": "<YOUR_CDF_PROJECT_NAME>"
}
1
2
3
4
5
6
7
8
9
10

Where:

  • token_urlis the ID of your Azure AD tenant. To find your tenant ID, see this article (opens new window).
  • client_idis the object Id for the AAD group exactly as it exists in AAD.
  • client_secretfor the AAD group exactly as it exists in AAD.
  • scope is the base URL + .default of the cluster where your CDF instance is installed.
  • project is the CDF project name.

The service returns a username and password with the same CDF capabilities as the group you created in CDF. Make sure to keep these, as they are required when you set up the gateway connection in your ETL tool.

NOTE

Make sure you copy the username and password. If you lose the password, it can't be recovered.

Note

If you revoke the capabilities in CDF, you also retract access for the PostgreSQL gateway.

Using Postman

If you're using the Postman tool, you can import this collection (opens new window). Make sure the Authorization parameters are set correctly on the Authorization tab:

  • In the Type field, select OAuth 2.0.

  • In the Grant Type field, select Client Credentials.

  • In the Client Authentication field, select Send as Basic Auth header.

  • Make sure the Header prefix is set to Bearer.

Authenticator settings in Postman

Here's what a user credentials request would look like on the Body tab:

Create user credentials example

# Step 5: Set up and connect your ETL tool to the PostgreSQL gateway

  1. Configure your ETL tool as a pipeline between the source system and the PostgreSQL gateway.

You'll find detailed information in the documentation provided by your tool vendor. For example, you can find the Azure Data Factory documentation here (opens new window).

Last Updated: 9/20/2021, 11:17:05 AM