# 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="https://3204318043-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxzBTp1t4OfqV67nXkVse%2Fuploads%2FrfTzkx4uS9uaf2DdIZQD%2Fimage.png?alt=media&#x26;token=d2dd6e3c-bdfe-4539-9a29-ed36624e205e" 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](https://docs.dinmo.io/security-and-privacy/networking)

{% 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:&#x20;

* 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="https://3204318043-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FxzBTp1t4OfqV67nXkVse%2Fuploads%2FGIsHYG2CR0ttqmnBVqeC%2Fimage.png?alt=media&#x26;token=1757b48b-6bee-4028-a724-eabc5a0bb454" 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!