Follow the instructions below to let Singular to export data in a streamlined and automated manner to a Redshift database.
Step 1: Create a Redshift User with Necessary Permissions
Create a user with the following command:
CREATE USER singular WITH PASSWORD '<password>';
And then grant it the following permissions:
GRANT CREATE ON DATABASE "<enter database name here>" TO "singular"; GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO singular; GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO singular;
What do these permissions given to Singular mean?
- The first permission allows us to create new schemas on the provided database. It will not allow Singular to read or make any changes to other schemas / tables in the database.
- The second and third permissions allow us to verify the structure of the tables Singular will update.
The following details should be provided back to Singular:
- Redshift host
- Redshift username (If different from “singular”)
- Redshift password
- Redshift database name
Step 2: Whitelist Singular Server IPs
In many cases, a Redshift instance may only be accessible from certain IPs as defined in the associated Amazon Security Group. If you are using a Security Group and only allow certain IPs to connect to your Redshift instance, you will need to add Singular’s servers to the whitelisted IPs in your security group:
You can accomplish that with the following steps:
Go to your redshift clusters list and click on the cluster you want us to use:
In the cluster settings, edit the VPC security group:
Edit your Inbound rules in the security group:
Create three rules with the IPs provided above:
Click Save to finish.