Setting up Redshift for use with One Model

1. We need to ensure that One Model can access your redshift cluster. This would either mean that your redshift cluster has open access, or that you have whitelisted the One Model servers to use it. To get the IP to whitelist for your region, please contact support. 

2. Next we need to setup the user that One Model will connect to your redshift cluster with. To do that we can use the following SQL statement. In all of the following scripts you will need to replace %YourUsernme% with the name of the user that you are creating, %YourPassword% with the password that you are generating and %YourDatabaseName% with the name of the database you are creating.

CREATE USER %YourUsernme% with PASSWORD '%YourPassword%' CREATEUSER;

3. Next we need to setup the permissions to allow the user to read the system tables. This is required for some of the sites functions to work properly.

GRANT USAGE ON SCHEMA information_schema TO %YourUsernme%;
GRANT USAGE ON SCHEMA pg_catalog TO %YourUsernme%;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO %YourUsernme%;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO %YourUsernme%;

4. The last step is to create a new database on redshift to destinate all of the data to. We recommend making the user that you have created the owner of the database so that we can perform all of the required functions to objects within it.

CREATE DATABASE %YourDatabaseName% WITH OWNER = %YourUsernme%;

We will provide you with a set of credentials for authenticating with the Redshift cluster, however we recommend to also use IP allowlisting for additional protection given the sensitivity of the data we store.  An IP allowlist helps protect against cases where the password may have been accidentally exposed by requiring someone to know the password and have a way of making requests from the same IP.