AWS Redshift
Connecting DinMo to Redshift: Step-by-Step Guide
Last updated
Connecting DinMo to Redshift: Step-by-Step Guide
Last updated
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.
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.
In your DinMo workspace:
Navigate to Workspace Settings.
Go to the Sources tab.
Click on Add New Source.
Select Amazon Redshift from the list of available sources.
To ensure secure and organized access, we'll create a dedicated user for DinMo and specific schemas for DinMo's use.
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
Notes:
Replace <your_strong_password>
with a secure password.
The AUTHORIZATION dinmo_user
clause sets the owner of the schema to dinmo_user
.
DinMo needs read access to the schemas containing the data you wish to use.
For each schema that contains relevant data:
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
.
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.
Port: The port number your cluster uses (default is 5439
).
Found under Clusters > [Your Cluster] > Properties > Port.
Database: The name of your database.
Found under Clusters > [Your Cluster] > Properties > Database Name.
User: dinmo_user
(or the username you created).
Password: The password you set for dinmo_user
.
Click Save & Test to verify the connection. If the test is successful, your Redshift source is now connected to DinMo.
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.
Set default privileges for the dbt user to ensure DINMO_USER
retains access to newly created tables.
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:
3. Grant Privileges on Existing Tables
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.
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.
If you encounter any issues or need assistance, please contact our support team at support@dinmo.com.