Redshift is a fully managed data warehouse in the cloud which is part of the cloud-computing platform Amazon Web Service (AWS)
Castled enables you to leverage the customer 360 data in your Redshift to engage
your customers across different channels like Email, SMS, Push and In-app
notifications.
Create a dedicated user CASTLED for connecting to your Redshift warehouse.For
making sure the connection is established successfully the CASTLED user created
should have the below permissions:
Required grants to create a new schema CASTLED with all required privileges
to create/update/delete tables with in that schema.
READ ONLY access to the existing as well as future tables and views in
the schemas from which you want Castled to sync the data to your destination
app.
Permission to execute the existing as well as future functions in the schema
from which you want Castled to sync the data to your destination app. You can
use the below script to configure the required access required by Castled.
Copy
-----------------------------------USER CREATON STARTS-------------------------------------------------------— Create a new user CASTLED for connecting to redshift DBCREATE USER CASTLED WITH PASSWORD '<strong, unique password>';-----------------------------------USER CREATON ENDS---------------------------------------------------------------------------------------------BOOK KEEPING SCHEMA ACCESS STARTS-------------------------------------------- Create a private bookkeeping schema for storing sync dataCREATE SCHEMA CASTLED;-- Give the CASTLED user full access to the bookkeeping schemaGRANT ALL ON SCHEMA CASTLED TO CASTLED;-- Give CASTLED user access to all objects existing n the bookkeeping schemaGRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA CASTLED TO CASTLED;-----------------------------------BOOK KEEPING SCHEMA ACCESS ENDS---------------------------------------------
Replace the < your schema> with your applicable schema for the below scripts
before executing them
Copy
-----------------------------------GRANT TABLE READ ONLY ACCESS STARTS-------------------------------------------- Give access to CASTLED user to SEE your schemaGRANT USAGE ON SCHEMA "<your schema>" TO CASTLED;-- Give READ ONLY access to CASTLED user to read from all existing tables in your schemaGRANT SELECT ON ALL TABLES IN SCHEMA "<your schema>" TO CASTLED;-- Give READ ONLY access to CASTLED user to read from all the future tables being created in your schemaALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT SELECT ON TABLES TO CASTLED;-- Give access to the CASTLED user to execute any existing functions in you schemaGRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "<your schema>" TO CASTLED;-- Give access to the CASTLED user to execute any new functions added to this schemaALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT EXECUTE ON FUNCTIONS TO CASTLED;-----------------------------------GRANT TABLE READ ONLY ACCESS ENDS---------------------------------------------
For configuring a new connector for Redshift the following fields needs to be
captured:
Name: A name to uniquely qualify the warehouse source created
Database Server Host: Host’s IP address or DNS Name.
Database Server Port: The port on which your Amazon Redshift server is
listening for connections. Default value: 5439.
Database name: Name of database you created for your cluster
Database User: Database username
Database Password: Database password
Enable SSH Tunnel: Enable this option to connect Redshift database host to
Castled Data using a SSH Tunnel.This is done to provide an additional level of
security and avoid exposing the Redshift setup to the public.
On enabling this option, you will have to enter the below details.Please
refer Connecting through SSH Tunnel for more details
SSH Host
SSH Port
SSH User
If the sync involves more than one schema in your database GRANT mentioned
above needs to be given for each of those schemas. If the views in your schema
references tables of another schema in your database, GRANT needs to be given
for those schemas as well.
Castled uses SSH Tunelling to connect to Redshift clusters accessible only on
private or internal networks. In order to make sure the connection is
established successfully with SSH Tunneling, you need to provide an SSH Host
visible on the public internet which can also connect to the private Redshift
warehouse. The basic admin actions mentioned below are required for the sameWhile configuring a new warehouse connector for Redshift on the ‘Create
Connector’, you need to enter the following details
SSH Host IP/DNS Name
SSH Port : Default is 22 unless changed
SSH Username : User created for Castled on the SSH Host Docusaurus Refer
Connecting through SSH Tunnel for details on how to add the castled public
key to authorized_keys.