SQL Connector setup guide
This guide explains how to use the SQL Connector. It covers how to:
- Make sure your Propel account has the SQL Connector enabled.
- Provision credentials for the SQL Connector.
- 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
- API
To create a Propel Application for the SQL Connector via the Console, follow these steps:
- Go to the โApplicationsโ section of the Console
- Click on โCreate Applicationโ.
- Enter the Application details as instructed below:
- Unique name: Enter the customer's name or identifier. For example: "Customer 001".
- Description: Enter a description. For example: "Customer 001 SQL credentials".
- Scopes: Include theย
DATA_POOL_QUERY
ย scope. - 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.
- Click on "Create".
You can create Applications via the API with the following GraphQL request. The Application you use to create Applications needs the APPLICATION_ADMIN
scope.
Be very careful with the APPLICATION_ADMIN
scope, as it can create Applications with any set of scopes.
- GraphQL
- cURL
mutation {
createApplication(
input: {
uniqueName: "Customer 001"
description: "Customer 001 SQL credentials"
scopes: [DATA_POOL_QUERY]
propeller: P1_SMALL
}
) {
... on ApplicationResponse {
application {
id
uniqueName
description
scopes
propeller
clientId
secret
}
}
... on FailureResponse {
error {
message
}
}
}
}
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_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.
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
- API
To create a Propel Application for the SQL Connector via the Console, follow these steps:
- Go to the "Data Pools" section of the Console.
- Click on the Data Pool you want to grant access to and then click "Access Control".
- Click on "Add new policy".
- Enter the following information:
- Column-based access control: The columns you want to grant access to.
- 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 wherecustomerId = 001
. This way, "Customer 001" can only see their data. - Applications: Add the Application you created in step 2.1.
- Name: Give your policy a name. For example: "DataPoolA-Customer001".
- Description. Give your policy a description. For example: "Policy for 'Customer 001' to access Data Pool A".
- Click "Create".
- Repeat this for each Data Pool to which you need to grant the Application access.
- Make sure "Access Control" is enabled for all Data Pools.
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โ
Business Intelligence (BI) Toolsโ
ELT / ETL Platformsโ
Limitationsโ
- Queries have a 10-second timeout.