# AWS Redshift

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.

<figure><img src="/files/flDE9ZIpW7vZIt44EEHp" alt=""><figcaption></figcaption></figure>

### 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.

```sql
-- 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:

```sql
-- 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](/security-and-privacy/networking.md)

{% hint style="success" %}
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**.
{% endhint %}

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.

<figure><img src="/files/mw3PNUEAGdidyjQdUgMl" alt=""><figcaption></figcaption></figure>

### 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:

```sql
-- 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**

```sql
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:

   ```yaml
   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:

   ```yaml
   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`:

```yaml
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 <support@dinmo.com>. We're here to help!


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.dinmo.io/integrations/data-sources/aws-redshift.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.