Create a dedicated user castled for connecting to your Hydra 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 hydra 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--------------------------------------------------------------------------------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 Hydra 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 Hydra server is
listening for connections. Default value: 5432.
Database name: Name of database you created for your cluster
Schema Name: Schema in your database to be used for the sync.
Database User: Database username
Database Password: Database password
Enable SSH Tunnel: Enable this option to connect Hydra database host to
Castled Data using a SSH Tunnel.This is done to provide an additional level of
security and avoid exposing the Hydra 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 in cases where Hydra is 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 and at the same time connect to your private
Hydra warehouse. The basic admin actions mentioned below are required for the
same
Create a new user for Castled on the SSH Host (This user account is not
related to the user account created for database related operations.
Recommend using the same account name , but can be kept different if you
want)
While configuring a new warehouse connector for Hydra 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
On successful creation of the new Connector , Castled will generate a key
pair fo the SSH authentication . Refer Connecting through SSH Tunnel for
details on how to add the castled public key to authorized_keys.