AWS Redshift

Connecting DinMo to Redshift: Step-by-Step Guide

Redshift is a popular datawarehouse on the Amazon Web Services platform. This guide provides step-by-step instructions to help you seamlessly integrate Amazon Redshift with DinMo.

Prerequisites

• Access to your Amazon Redshift cluster with administrative privileges.

• The ability to create users, schemas, and grant privileges.

• A DinMo workspace with access to workspace settings.

Step 1: Add Amazon Redshift as a Source

In your DinMo workspace:

1. Navigate to Workspace Settings.

2. Go to the Sources tab.

3. Click on Add New Source.

4. Select Amazon Redshift from the list of available sources.

Step 2: Create a DinMo User and Schemas in Redshift

To ensure secure and organized access, we'll create a dedicated user for DinMo and specific schemas for DinMo's use.

SQL Script to Execute

Run the following SQL commands in your Redshift cluster. Make sure to replace placeholders with your actual values.

-- Create a dedicated DinMo user with a strong, unique password
CREATE USER dinmo_user WITH PASSWORD '<your_strong_password>';

-- Create schemas that DinMo will use to store technical data
CREATE SCHEMA IF NOT EXISTS dinmo_delta_storage AUTHORIZATION DINMO_USER;
CREATE SCHEMA IF NOT EXISTS dinmo_segments AUTHORIZATION DINMO_USER;
CREATE SCHEMA IF NOT EXISTS dinmo_stats AUTHORIZATION DINMO_USER;
CREATE SCHEMA IF NOT EXISTS dinmo_predictions AUTHORIZATION DINMO_USER;


-- Grant the DinMo user full access to these schemas
GRANT ALL ON SCHEMA dinmo_delta_storage TO DINMO_USER;
GRANT ALL ON SCHEMA dinmo_segments TO DINMO_USER;
GRANT ALL ON SCHEMA dinmo_stats TO DINMO_USER;
GRANT ALL ON SCHEMA dinmo_predictions TO DINMO_USER;

Notes:

• Replace <your_strong_password> with a secure password.

• The AUTHORIZATION dinmo_user clause sets the owner of the schema to dinmo_user.

Step 3: Grant Necessary Privileges

DinMo needs read access to the schemas containing the data you wish to use.

Grant Access to Data Schemas

For each schema that contains relevant data:

-- Replace <data_schema> with the name of your data schema
GRANT USAGE ON SCHEMA <data_schema> TO DINMO_USER;
GRANT SELECT ON ALL TABLES IN SCHEMA <data_schema> TO DINMO_USER;

-- Ensure future tables are accessible (important if using tools like dbt)
ALTER DEFAULT PRIVILEGES IN SCHEMA <data_schema>
GRANT SELECT ON TABLES TO DINMO_USER;

Notes:

• Repeat these commands for each data schema you want DinMo to access.

• The ALTER DEFAULT PRIVILEGES command ensures that any new tables created in the schema will automatically grant SELECT privileges to DINMO_USER.

Step 4: Configure Connection Settings in DinMo

Back in your DinMo workspace, fill in the required information:

• Host: The endpoint of your Redshift cluster (exclude the port and database name).

  • Found in the Redshift console under Clusters > [Your Cluster] > Properties > Endpoint.

• Database: The name of your database.

  • Found under Clusters > [Your Cluster] > Properties > Database Name.

• Port: The port number your cluster uses (default is 5439).

  • Found under Clusters > [Your Cluster] > Properties > Port.

• User: dinmo_user (or the username you created).

• Password: The password you set for dinmo_user.

• Connection Type: Choose between Direct Connection or SSH Tunnel

Tunneling enables DinMo securely open a connection to your data warehouse in your private network or Virtual Private Cloud (VPC) without exposing it to the internet.

SSH tunnels are secure, authenticated, encrypted, and dedicated to your workspace.

If you chose SSH Tunnel as the connection type, you have to fill the required information:

• Enter the hostname or IP for the SSH bastion

• Enter the port for the SSH bastion

• Enter a username to connect to your SSH bastion

• Copy/past the SSH Public Key generated in your file containing the different authorized keys (~/.ssh/authorized_keys file for the specific user)

Click Save & Test to verify the connection. If the test is successful, your Redshift source is now connected to DinMo.

Handling Privilege Issues when using dbt

Understanding the Issue

When using dbt or similar transformation tools, tables are often dropped and recreated as part of the transformation process. This can result in loss of privileges for DINMO_USER because:

• Default privileges in Redshift are user-specific: They only apply to objects created by the user who set them.

• Tables created by dbt won't inherit privileges set by other users.

Solution Overview

To ensure DINMO_USER retains access to newly created tables, you can:

1. Set default privileges for the dbt user.

2. Use dbt’s built-in grants configuration to automatically grant privileges.

Solution 1. Setting Default Privileges for the dbt User

You can set default privileges for the dbt user to ensure DINMO_USER retains access.

Detailed Steps

1. Identify the dbt User and Target Schema

• dbt User: The username that dbt uses to connect to Redshift (e.g., dbt_user).

• Data Schema: The schema where dbt creates or recreates tables.

2. Set Default Privileges for the dbt User

Run the following commands as the dbt user or a superuser:

-- Replace <data_schema> with your data schema
-- Replace dbt_user with your actual dbt user 
ALTER DEFAULT PRIVILEGES FOR USER dbt_user IN SCHEMA <data_schema> GRANT SELECT ON TABLES TO DINMO_USER;

3. Grant Privileges on Existing Tables

GRANT SELECT ON ALL TABLES IN SCHEMA <data_schema> TO DINMO_USER;

Important Notes:

• The ALTER DEFAULT PRIVILEGES command must be run by the dbt user or a superuser.

• If multiple users create tables, you need to set default privileges for each one.

• This ensures DBT_USER retains SELECT access to tables even after they are recreated.

Solution 2. Using dbt's Built-in Grants Configuration

Instead of modifying default privileges in Redshift, you can configure dbt to automatically grant the necessary privileges to DINMO_USER when models are created or refreshed. This is done by specifying grants in your dbt_project.yml file.

Steps to Implement

1. Configure Grants in dbt

   In your dbt_project.yml file, add the following configuration:

   codemodels:
     your_project_name:
       +grants:
         select: ['DINMO_USER']

   • Replace your_project_name with the name of your dbt project.

   • This configuration tells dbt to grant SELECT privileges on all models to DINMO_USER after they are created or updated.

2. Configure Grants for Specific Schemas or Models (Optional)

   If you want to grant privileges only on specific schemas or models, adjust the configuration accordingly:

   codemodels:
     your_project_name:
       your_schema_name:
         +grants:
           select: ['DINMO_USER']

   • Replace your_schema_name with the name of the schema.

3. Run dbt Jobs

   After adding the grants configuration, when you run your dbt jobs, dbt will execute the necessary GRANT statements after creating or updating the tables and views.

Example Configuration

Here's a complete example of how to configure the grants in dbt_project.yml:

codename: 'your_project_name'
version: '1.0'
config-version: 2

models:
  your_project_name:
    +grants:
      select: ['DINMO_USER']

How It Works

• dbt's grants Feature: dbt has a built-in feature to manage database object grants. By specifying +grants in your model configurations, dbt will automatically apply the specified grants after running the models.

• No Need for ALTER DEFAULT PRIVILEGES: This method eliminates the need to modify default privileges in Redshift, as the grants are applied directly by dbt during the model run.

• Flexible and Granular Control: You can specify grants at the project level, schema level, or even per model, giving you granular control over permissions.

Important Notes

• dbt Version: Ensure you are using a version of dbt that supports the grants configuration (dbt version 0.20.0 or later).

• Database User Permissions: The dbt user must have the necessary permissions to grant privileges to other users. Specifically, the dbt user needs to have the GRANT OPTION for the privileges being granted.

• Consistency: Using dbt's grants ensures that permissions are consistently applied every time models are run, which is especially useful in environments with frequent schema changes.

---

Troubleshooting

• Connection Test Fails: Verify the host, port, database, user, and password are correct.

• Privilege Errors: Ensure DINMO_USER has USAGE and SELECT privileges on the necessary schemas and tables.

• Access Issues with Recreated Tables:

  • Confirm that default privileges are set for the user who creates the tables (e.g., the dbt user).

  • Ensure the ALTER DEFAULT PRIVILEGES command was executed by the correct user.

---

Support

If you encounter an error or question not listed below and need assistance, don't hesitate to reach out to your account manager or to [e-mail]. We're here to help!