Configuring Your Amazon Redshift Data Warehouse Environment

Openbridge allows you to add your Redshift cluster as a storage location, enabling data pipelining to your cluster. These instructions will guide you through setting up your self-managed Amazon Redshift cluster to work with the Openbridge system.

To ensure a successful setup, you'll need to complete the following steps:

Verify Public Subnet Configuration
Configure Amazon Redshift Firewall
Create a Database and User in Redshift
Set Up Schema and Grant Permissions

Before you start, please ensure you have administrative access to your Amazon Redshift cluster and the necessary permissions to create databases, users, and schemas.

Make Sure Redshift Is Publically Available

Also, make sure you have selected Public Subnets. If not, this can cause connection failures, which would be the same as if you set "Publically accessible" to off.

See AWS doc: Public accessibility with default or custom security group configuration

Configure Amazon Redshift Firewall

To allow Openbridge to access your Redshift cluster, you need to configure your Amazon Redshift security group to grant inbound access from the Openbridge IP addresses:

52.54.227.22
52.2.68.68

Follow these steps to configure the security group:

Open the Amazon Redshift Management Console.
In the navigation pane, select Clusters.
Click on the name of the cluster you want to connect.
Click on the name of the associated security group. This will display the list of authorized inbound connections.
Click the Add Connection Type button.
In the Connection Type dropdown, select CIDR/IP.
In the CIDR/IP to Authorize field, enter 52.54.227.22/32.
Click Authorize to add the Openbridge IP address.
Repeat steps 5-8 for the second IP address (52.2.68.68/32).

Following these steps, you can give Openbridge access your Redshift cluster through the security group configuration.

Note: If you need more help, please refer to the official Amazon Redshift documentation for detailed instructions on configuring security groups.

Test Your Connection

Test the external connectivity to your Redshift cluster using one of the following methods:

Method 1: Using PSQL

You can leverage the PostgreSQL command-line utility (psql) to verify your connection details:

psql -h <cluster-endpoint> -p 5439 -U <username> -d <databasename> -c '\d'

Replace `<cluster-endpoint>` with your Redshift cluster's endpoint (e.g., `my-cluster.abc123.us-east-1.redshift.amazonaws.com`), `<username>` with your Redshift username, and `<databasename>` with the name of your database. This command should list the tables in the specified database if the connection is successful.

Method 2: Using an SQL Client

Alternatively, you can use an SQL client tool like DBeaver, SQL Workbench, or the Amazon Redshift Query Editor to connect to your Redshift cluster from an external location. Follow these steps:

Launch your preferred SQL client tool.
Create a new connection and provide the required details, such as the cluster endpoint, port (5439), username, and password.
Attempt to connect to the cluster and execute a simple SQL query (e.g., `SELECT 1;`) to verify the connection.

If the connection is successful, you should be able to execute queries and interact with your Redshift cluster.

Note: If you encounter connection timeouts or errors, double-check your security group configuration, ensuring that the Openbridge IP addresses are correctly authorized. Also, please ensure your network settings (e.g., firewalls, VPNs) do not block outbound connections to the Redshift cluster.

Setting Up Redshift Databases, Schemas, and Users

To create a Redshift account for Openbridge, you must be an admin Redshift user with the correct permissions.

Picking a Tool For Configuration: Redshift query editor, PSQL...

The Redshift query editor is a great place to work on configuring your server. Query editor provides an enhanced user experience and additional features for working with Redshift queries. The Amazon Redshift query editor can provide a more efficient and user-friendly experience for working with Redshift queries:

You can see a video on using the tool here: https://youtu.be/IwZNIroJUnc

However, you are free to use other tools like PSQL if you are more comfortable with them.

Create Database

Here is a set of commands that assumes you are connected via the Redshit query editor, PSQL, or some other tool to issue SQL commands to Redshift.

If you want to use a different database than the default one created when your cluster was configured, you can run the:

create database openbridge;

This will create a new database in your cluster for Openbridge to use.

To verify the database exists, you can run:

select datname, datdba, datconnlimit from pg_database_info where datdba > 1;

This should show something like this:

 datname     | datdba | datconnlimit
-------------+--------+-------------
 admin       |    100 | UNLIMITED   
 reports     |    100 | 100         
 openbridge  |    100 | 100

Create User

After connecting to your cluster and database, run this command to create the user:

create user {{username}} with password '{{userpassword}}';

Here is another example. How you create users will be up to your policies and preferences:

create user name_surname with password 'P455WORD' NOCREATEDB IN GROUP read_only_users;

To verify that the user was created, you can run;

select * from pg_user_info;

You should see your new user listed:

usename   | usesysid | usecreatedb | usesuper | usecatupd | passwd
-----------+----------+-------------+----------+-----------+----------
rdsdb     |        1 | true        | true     | true      | ********       
adminuser |      100 | true        | true     | false     | ********  
username  |      102 | true        | false    | false     | ********

Assign the CREATE permissions for {{username}} on openbridge. The database can be the one you created with the command above or the name of the database you created when you launched the cluster:

grant create on database openbridge to {{username}};

To alter default privileges, you can use the following query:

ALTER DEFAULT PRIVILEGES IN SCHEMA {{schema}} GRANT SELECT ON TABLES TO group {{groupname}};

To grant privileges on particular, you can get a few examples from Postgresql documentation: https://www.postgresql.org/docs/8.0/static/sql-grant.html

Create Schema

Next, let's make sure you have a schema for Openbridge to use in openbridge:

create schema if not exists openbridge authorization {{userpassword}};

To validate that the user has the correct permissions;

select nspname as schema, usename as owner from pg_namespace, pg_user where pg_namespace.nspowner = pg_user.usesysid and pg_user.usename ='{{username}}';

You should see something like this:

name   |  owner
----------+----------
openbridge | username
(1 row)

If the schema already exists, then we want to make sure the correct permissions are granted to {{username}} on the schema:

grant all on schema openbridge to {{username}};

Lastly, see the default search path:

ALTER USER {{username}} SET search_path="openbridge";

This allows us to route data to the schema you set up correctly.

IMPORTANT: If these steps are missed or misconfigured, our ability to connect or write data will be impacted.

Using Pre-Built Redshift Configuration File

To simplify the process of getting everything set up, we have included a SQL statement below:

/*
You will want to replace {{username}}, {{mydatabase}}, {{userpassword}} with your correct values
*/
create database {{mydatabase}};
\connect {{mydatabase}}
create user {{username}} with password '{{userpassword}}';
grant create on database {{mydatabase}} to {{username}};
create schema if not exists openbridge authorization {{username}};
grant all on schema openbridge to {{username}};
ALTER USER {{username}} SET search_path="openbridge";

Using Redshift query editor or PSQL you can connect to Redshift with your admin user. You would run something like this:

psql -h *****.us-east-1.redshift.amazonaws.com -p 5439 -U username -q -f create-openbridge.sql

Note: You will be prompted for your password. Also, you will need to change the {{username}}, {{username}} and {{mydatabase}} to the values that reflect your system/preferences.