Configuring Redshift / PostgreSQL Access

This article will describe how to configure a Redshift Data Warehouse credentials for use by Census, and why those permissions are needed.

Required Permissions

Census reads data from one or more tables (possibly across different schemata) in your database and publishes it to the corresponding objects in external systems such as Salesforce. To limit the load on your database as well as to other apps' APIs, Census computes a “diff” to determine changes between each update. In order to compute these diffs, Census creates and writes to a set of tables in the census schema (2 or 3 tables for each sync job configured).
We recommend you create a dedicated census user account with a strong, unique password. Census uses this account to connect to your Redshift or PostgreSQL database. In order for the Census connection to work correctly, the census account must have these permissions:
  • The ability to create the census schema and full admin access to all tables within that schema (including creating tables, deleting tables, and reading and writing to all tables).
  • Read-only access to the information_schema schema, which Census uses to list the available schemata, tables, and views, and identity the data types for columns within tables to be synced.
  • Read-only access to any tables and views in any schemata that you would like Census to publish to Salesforce.
  • (Redshift only) The ability to UNLOAD tables from the census schema to an AWS S3 bucket controlled by Census.

Encryption

All connections from the Census Data Warehouse Service to your database, as well as connections from your Redshift database to S3, are protected by TLS encryption. All Census data stored in S3 is encrypted with AWS Server-Side Encryption(SSE). We recommend configuring your PostgreSQL instance to use TLS v1.2 or later for all connections.

IP Whitelisting

This account will always connect from one of these static IP addresses located within AWS:
  • 34.216.163.241
  • 54.212.243.205
You may optionally limit Census’s access by whitelisting only these IPs in your firewall or security group, and/or add rules to your pg_hba.conf file to only allow the census user to connect to your database when using these IP addresses.