Connect Postgres to Row Zero

Row Zero makes it easy to connect to Postgres with a built-in Postgres connector. Follow the instructions below to connect to Postgres, share your connection with others, and import data to a spreadsheet.

Create your Postgres connection

Row Zero supports two ways to authenticate to PostgreSQL. Use whichever fits your security posture:

  • Password-based Authentication
  • AWS IAM Role-based Authentication (via AWS IAM / RDS IAM DB auth)

Below is a detailed flow for each option.

  1. In a Row Zero workbook, click on the 'Data' icon in the upper right-hand corner and then click the button to 'Add connection.' connect spreadsheet to postgres

  2. The Postgres connector requires a few pieces of information defined below. postgres connector

    • Connection name - This field can be anything that helps identify the data source being connected (e.g. "Production Data" or "Product Metrics"). It will be visible to all users who have access to this connection.
    • Host - This is the PostgreSQL address that typically takes the form of a string like 'database.mydomain.com'.
    • Port - This is often 5432.
    • Database - The name of the PostgreSQL database being connected to.
    • User - This is the username for your PostgreSQL database.

Password-based Authentication

For password-based authentication, populate the following:

  • Authentication method — Password
  • Password — The password used to log into your PostgreSQL Database.

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test connection icon turns green and says 'Connected', proceed by clicking on 'Create connection'. Add PostgreSQL as source screen in Row Zero

Note - If your database is behind a VPN or firewall, you'll need to allow the Row Zero IP addresses: 18.217.97.112 and 18.224.119.220.

AWS IAM Role-based Authentication (AWS IAM / RDS)

For AWS IAM-based authentication, populate the following:

  • Authentication method — AWS IAM
  • Role ARN — the ARN of the role to connect to RDS PostgreSQL that can connect to RDS and assume a role (see below for instructions to setup).
  • AWS Region — The AWS region your RDS Postgres instance is located in.
  • External ID - This is a value will be populated by the Row Zero application and must be included in your AWS IAM role trust policy (see below for instructions to setup).

Once all the information is entered, hit 'Test connection' to ensure the information is correct. If the test connection icon turns green and says 'Connected', proceed by clicking on 'Create connection'

Add PostgreSQL as source screen in Row Zero

Note - If your database is behind a VPN or firewall, you'll need to allow the Row Zero IP addresses: 18.217.97.112 and 18.224.119.220.

AWS IAM Role-based Authentication Setup Instructions

Create an AWS IAM Role for Postgres Access that will be assumed by Row Zero via STS in order to connect to your database.

  • Create a new IAM Role in the same account as your PostgreSQL / RDS instance.
  • Attach a trust policy that allows Row Zero's IAM principal (or account) to assume this role, with an External ID condition.
  • Attach permission policies allowing it to call rds-db:connect to your target DB user.
  • Configure the RDS / PostgreSQL instance to have IAM authentication enabled.
  • Create a PostgreSQL database user that your IAM role will connect as and grant the rds_iam role to that user (GRANT rds_iam TO <username>;). This enables the user to authenticate using AWS IAM tokens.

Sample trust policy (you must substitute in the External ID that Row Zero provides during setup):

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Effect": "Allow",
           "Principal": {
               "AWS": [
                   "arn:aws:iam::732940336628:role/DataSourceProxyRole"
               ]
           },
           "Action": "sts:AssumeRole",
           "Condition": {
               "StringEquals": {
                   "sts:ExternalId": "<EXTERNALID_FROM_ROW_ZERO>"
               }
           }
       }
   ]
}

Permissions policy example (scope to your DB instance / user):

{
   "Version": "2012-10-17",
   "Statement": [
       {
           "Effect": "Allow",
           "Action": "rds-db:connect",
           "Resource": "arn:aws:rds-db:<region>:<account-id>:dbuser:<db-resource-id>/<database-username>"
       }
   ]
}

Write a query to import Postgres data

Now that Postgres is connected to your spreadsheet, you can import data by writing queries directly from Row Zero.

  1. From any workbook, click 'Data', and then click the "+" sign next to your Postgres connection to insert a connected table. add postgres connected table

  2. This adds a data table to your spreadsheet that is connected to Postgres and opens a query editor. Select the 'Schema' and then write a SQL query. The easiest query to write is a 'select *' statement, which pulls in the entire table. For example:

    select * from table_name

  3. Click 'Run' to execute the query and import the queried Postgres data into your spreadsheet. import Postgres data to your spreadsheet

  4. The data imports directly into a connected table, a dynamic, condensed view that makes it easy to refresh and analyze your data. Double-click on the connected table to re-run your query for the latest data or schedule auto-refresh and everything built on connected data automatically updates and stays in sync.

  5. Once you have imported your Postgres data, you can build out your connected spreadsheet, adding computed columns, pivot tables, charts, and more.