This guide will explain how to allow the Secoda to integrate with Redshift to read the metadata.
This document was last updated on 25 February, 2021. If you notice any gaps, out-dated information or simply want to leave some feedback to help us improve our documentation, let us know!
There are three steps to get started using Redshift with Secoda:
The username and password you’ve already created for your cluster is your admin password, which you should keep for your own usage. For Secoda, and any other 3rd-parties, it is best to create distinct users. This will allow you to isolate queries from one another using WLM and perform audits easier.
To create a new user, you’ll need to log into the Redshift database directly and run the following SQL commands:
<aside> 💡 Redshift doesn't allow for non super users to access the system tables, which is where we pull the metadata from. Below is a workaround so you aren't giving Secoda super user access. Secoda only uses the system tables for our metadata extraction, the extraction query can be viewed here.
</aside>
-- Create a user named "secoda" that Secoda will use when connecting to your Redshift cluster.
CREATE USER secoda PASSWORD '<enter password here>';
-- Allows the non super user "secoda" to query metadata
-- Explaination of query here -> <https://stackoverflow.com/questions/48567440/granting-permissions-on-redshift-system-tables-to-non-superusers>
ALTER USER secoda SYSLOG ACCESS UNRESTRICTED;
-- Complete this query for any schemas you would like Secoda to extract
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO secoda_test
When connecting to Redshift in Secoda, use the username/password you’ve created here instead of your admin account.
After creating a Redshift warehouse, the next step is to connect Secoda: