Connecting an Amazon RedShift Database

CONTENTS

Home

Connect to RedShift

Here is what you need to connect:

  • Read-only user (Username field)
  • Add the bipp IP address to the Cluster Security Group.
  • Database connection information
  • Credentials
bipp supports PostgreSQL version 9.x onwards. Earlier versions of PostgreSQL may be compatible but are not tested.

Create a RedShift read-only User

bipp recommends using a read-only user for connecting to your RedShift databases.

In the Redshift query editor:

Create a new user:

CREATE USER bipp_db_user WITH PASSWORD 'Secret@123';
Assign your own user name and strong password for bipp_db_user and Secret@123.

To grant SELECT permissions on a table-by-table basis, execute the query for each table:

GRANT SELECT ON your_schema.your_table TO bipp_db_user;

To grant SELECT permissions on all tables in a schema:

GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO bipp_db_user;

Add bipp IP Address to Cluster Security Group

If your Redshift database is not publicly accessible, you must add bipp’s IP address **34.121.142.220/**32 to your Cluster Security Group settings:

  1. Log in to the AWS Console.

  2. Navigate to Redshift Cluster Management.

  3. Select Clusters from the navigation pane.

  4. Select the Cluster to connect to bipp.

  5. Open the Properties tab on the Cluster Details page.

  6. Scroll to the Network and Security > VPC Security Groups and select the Security Group for bipp communication.

  7. Open the Inbound Rules tab and click Edit.

  8. Click Add Rule to add a new Inbound Rule:

    a. Set Type to Redshift

    b. Edit the Port, if needed

    c. Set Source field to **34.121.142.220/**32

    d. Click Save

Use Direct Connection as the Connection type when you create the datasource.

Create a RedShift Datasource in bipp

  1. Select Datasources from the Home Navigation pane to open the Datasources page.

    Datasource Page

  2. Click New Datasource on the Datasources page.

    New Datasource

  3. Enter a Name. For example, TestRedShift.

  4. Select RedShift for SQL Dialect for your database. bipp creates and enforces the rules for the selected dialect.

    Datasource RedShift

  5. Enter the information for your RedShift database instance:

    • Host: name or IP address of the database host.
    • Port: port of the RedShift Cluster for the connection.
    • Username: user name for the connection. bipp recommends using a read-only user account.
    • Password: password for the user.
    • DbName: name of the database for the connection.
    • Connection: connection type
      • Direct Connection: database directly available over internet or your bipp platform is deployed on-premise in a private subnet. If your database is behind a firewall. Add the bipp IP address 34.121.142.220 to your Cluster Security Group.
        • SslMode: select the mode for SSL connection.
          • disable: Do not use SSL. The SslCert, SslKey and SslRootCert fields are not used.
          • require: Always use SSL, skip certificate verification.
          • verify-ca: Always use SSL, verify the certificate is signed by a trusted CA.
          • verify-full: Always use SSL, verify the certificate is signed by a trusted CA, and the server host name matches the certificate.
        • SslCert: browse to the location of the PEM format file containing your X509 certificate. This certification identifies the RedShift database to clients during SSL connections.
        • SslKey: browse to the location of your SSL key.
        • SslRootCert: browse to the location of your SSL Root certificate.
      • Direct Secure Connection: secured encrypted TCP connection if your database is behind a firewall. Add the bipp IP address 34.123.9.91 to your Cluster Security Group. TLS is not supported in this mode. The encrypted data is transmitted to prevent in-transit eavesdropping. Direct Secure Connection will be deprecated soon.
      • SSH Tunnel: encrypted connection using Datasource RedShift SSH Port Forwarding. bipp supports both forward and reverse SSH tunnels. You can use the SshTunnelAuth to specify Key or Password for the authorization type. Here are the additional SSH fields:
        • SshTunnelHost: name or IP address of the SSH tunnel authentication host.
        • SshTunnelPort: port on the SSH tunnel authentication host for the connection.
        • SshTunnelUser: user name for SSH tunnel authentication.
        • Ssh TunnelAuth: select Key to use an SSH Private Key and optional SSH Passphrase, or select Password to use your SSH Password.

  6. Click Test to verify the datasource connectivity. If you have an error, fix the connection or credentials problem and click Test again.

  7. Click Create to add the datasource.

Congratulations! You have successfully created a RedShift datasource in bipp.

IN THIS PAGE

Sign up for a forever free account!