SQL Connector setup guide

This guide explains how to use the SQL Connector. It covers how to:

1. Make sure your Propel account has the SQL Connector enabled.
2. Provision credentials for the SQL Connector.
3. Connect and query.

---

Requirements

• You have a Propel account.
• You have the PostgreSQL CLI installed.

---

Step 1: Make sure your Propel account has the SQL Connector enabled

You can verify if your account has the SQL Connector enabled by checking the Console menu.

The SQL Connector is available for accounts in the enterprise plan. Book a demo to learn more about enabling it for your account.

Step 2. Provision credentials for the SQL Connector.

To provision credentials for the SQL Connector, you first need to create a Propel Application and, second, define an access control policy.

The Propel Application will have a client ID and secret that serve as the username and password for the SQL connection. The Access Control Policy will determine the data these credentials can query.

We will walk through the process of creating Propel Applications and attaching an Access Control Policy via the Propel Console and API.

2.1 Create a Propel Application (credentials)

First, we need to create a Propel Application that will give us the client ID and secret that serve as the username and password for the SQL connection.

Console

To create a Propel Application for the SQL Connector via the Console, follow these steps:

1. Go to the “Applications” section of the Console
2. Click on “Create Application”.
3. Enter the Application details as instructed below:
   1. Unique name: Enter the customer's name or identifier. For example: "Customer 001".
   2. Description: Enter a description. For example: "Customer 001 SQL credentials".
   3. Scopes: Include the DATA_POOL_READ and DATAPOOL_QUERY scopes.
   4. Propeller: The Propeller determines how fast the credentials' queries are in records read per second. You can use the smallest Propeller P1_X_SMALL to start.
4. Click on "Create".

API

You can create Applications via the API with the following GraphQL request. The Application you use to create Applications needs the APPLICATION_ADMIN scope.

danger

Be very careful with the APPLICATION_ADMIN scope, as it can create Applications with any set of scopes.

GraphQL

mutation {
  createApplication(
    input: {
      uniqueName: "Customer 001"
      description: "Customer 001 SQL credentials"
      scopes: [DATA_POOL_READ, DATA_POOL_QUERY]
      propeller: P1_SMALL
    }
  ) {
    ... on ApplicationResponse {
      application {
        id
        uniqueName
        description
        scopes
        propeller
        clientId
        secret
      }
    }
    ... on FailureResponse {
      error {
        message
      }
    }
  }
}

cURL

curl 'https://api.us-east-2.propeldata.com/graphql' \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer YOUR_ACCESS_TOKEN' \
  --data-raw '{"query":"mutation {\n  createApplication(\n    input: {\n      uniqueName: \"Customer 001\"\n      description: \"Customer 001 SQL credentials\"\n      scopes: [DATA_POOL_READ, DATA_POOL_QUERY]\n      propeller: P1_SMALL\n    }\n  ) {\n    ... on ApplicationResponse {\n      application {\n        id\n        uniqueName\n        description\n        scopes\n        propeller\n        clientId\n        secret\n      }\n    }\n    ... on FailureResponse {\n      error {\n        message\n      }\n    }\n  }\n}","variables":{}}'

Note the client ID and secret, as we will need these to connect to the SQL interface in step 3.

2.2 Attach an Access Control Policy

To control what Data Pools the Application has access to, you need to create an Access Policy. You need to create an Access Policy for each Data Pool the Application needs access to.

danger

Make sure Access Control is enabled on ALL Data Pools. Data Pools with access control turned off will be accessible via the SQL Connector.

Console

To create a Propel Application for the SQL Connector via the Console, follow these steps:

1. Go to the "Data Pools" section of the Console.
2. Click on the Data Pool you want to grant access to and then click "Access Control".
3. Click on "Add new policy".
4. Enter the following information:
   1. Column-based access control: The columns you want to grant access to.
   2. Row-based access control: Create a row-based access control rule that filters data for a particular customer. For example, if "Customer 001" has a customerId of "001", then create a rule where customerId = 001. This way, "Customer 001" can only see their data.
   3. Applications: Add the Application you created in step 2.1.
   4. Name: Give your policy a name. For example: "DataPoolA-Customer001".
   5. Description. Give your policy a description. For example: "Policy for 'Customer 001' to access Data Pool A".
5. Click "Create".
6. Repeat this for each Data Pool to which you need to grant the Application access.
7. Make sure "Access Control" is enabled for all Data Pools.

API

To create Access Policies via the API, first make a request to create the Policy and enable access control for the Data Pool.

mutation {
  createDataPoolAccessPolicy(
    input: {
      uniqueName: "DataPoolA-Customer001"
      description: "Policy for Customer001 to access Data Pool A."
      dataPool: "DPOXXXXXXX"
      columns: ["*"]
      rows: [{ column: "customerId", operator: EQUALS, value: "001" }]
    }
  ) {
    dataPoolAccessPolicy {
      id
      uniqueName
      description
      dataPool {
        id
      }
      columns
      rows {
        column
        operator
        value
      }
    }
  }
  modifyDataPool(
    input: { idOrUniqueName: { id: "DPOXXXXXXX" }, accessControlEnabled: true }
  ) {
    ... on DataPoolResponse {
      dataPool {
        id
        accessControlEnabled
      }
    }
    ... on FailureResponse {
      error {
        message
      }
    }
  }
}

Second, assign the policy to the Application. Replace "DataPoolAccessPolicyID" with the Policy ID from the request above.

mutation AssignPolicyToApplication {
  assignDataPoolAccessPolicyToApplication(
    dataPoolAccessPolicy: "DataPoolAccessPolicyID"
    application: "Customer 001"
  ) {
    id
  }
}

3. Connect and query

Now that we have an Application that serves as the SQL credentials, we can connect to the SQL Interface.

Connection details

You can use the connection details below to connect your PostgreSQL client to Propel.

Detail	Value
Host	postgresql.us-east-2.propeldata.com
Port	5432
Database	propel
User	Your Propel Application ID
Password	Your Propel Application client secret

Example: Connecting with the PostgreSQL CLI

We are going to connect using the PostgreSQL CLI.

psql -h postgres.us-east-2.propeldata.com \
  -p 5432 \
  -d propel \
  -U $PROPEL_APPLICATION_ID \
  -W

When you are prompted for the password, enter the Application secret.

Example: Querying with the PostgreSQL CLI

Once connected, you can make queries to the Data Pools to which you have access.

propel=> SELECT * FROM DPOXXXXXXX LIMIT 3;

Connection guides

In this section, you will find connection guides for popular database clients, business intelligence tools, and ETL platforms to connect to PostgreSQL.

Database Clients

• PGAdmin
• DBeaver
• PopSQL
• VSCode DB Tools

Business Intelligence (BI) Tools

• Metabase
• Looker
• Tableau
• Superset
• Mode

ELT / ETL Platforms

• Fivetran - You must use Fivetran Teleport Sync.
• Airbyte - You must use cursor-based sync.

Limitations

• Queries have a 10-second timeout.