HomeDocumentationChangelog
HomeRequest DemoContact
HomeRequest DemoContact

Redshift

Create a Redshift Destination

Prepare credentials and permission in Redshift

Certain credentials and permission are required for Validio to egress data to your Destination:

For detailed information about permissions in Redshift, refer to Managing database security.

You can use this SQL script to set up a user and grant the necessary permissions for the Destination in Validio:

  1. Copy this script to a SQL worksheet.
  2. Follow the steps described in the script.
/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<validio_user>        -- Name of validio-user. It's not recommended to use an alread existing user.
<Some_Password123>    -- Password for vaidio-user.
<egress_schema>       -- Schema for egress.

*/------------------------------------------------ Script -----------------------------------------------------

-- An admin-user such as awsuser is necessary to perform this setup. 
SET SESSION AUTHORIZATION 'awsuser';

-- Create Validio-user with necessary grants
CREATE USER <validio_user> PASSWORD '<Some_Password123>';   -- Create a separate Validio-user

-- Grant usage on egress-schema
GRANT USAGE, CREATE ON SCHEMA <egress_schema> TO <validio_user>;     -- Grant usage and create permissions on egress-schema

Create the Destination in Validio

Credential parameters

FieldRequiredDescriptionExample
NameIdentifier for the Credentials. Used when accessing Destinations.service_acount_product_staging
HostEndpoint of the Redshift cluster database.test-redshift-cluster-1.ynypdanx10uk.eu-north-1.redshift.amazonaws.com
PortPort number of the Redshift database.
UserUsername of Redshift account with write access to the desired table.
PasswordPassword of the specified Redshift user.
Default databaseName of the default Redshift database.

Configuration parameters

FieldRequiredDescription
NameIdentifier for the Destination. Used when setting up egress in Validators.
DatabaseName of the Redshift database that contains the schema and table to egress into.
SchemaName of the Redshift schema that contains the table to egress into.
TableName of the Redshift table created when initializing the destination connector.
TLS encryptionEnable or disable TLS encryption.

Note: You must enable TLS encryption for clusters that only allow SSL traffic.

Remove credentials and permissions

You can use this SQL script to remove configured permissions for the Destination in Validio:

  1. Copy this script to a SQL worksheet.
  2. Follow the steps described in the script.
/*--------------------------------------- Parameters used in this script -------------------------------------------------
Search/replace all instances of these parameters in the script with names of your choice.

<egress_schema>     -- Name of egress-schema where you want to remove all egress tables and stages.
<validio_user>      -- Name of validio-user you want to remove.
<validio_group>     -- Name of validio-group you want to remove (if you have set up a group for Validio)

*/------------------------------------------------ Script -----------------------------------------------------

-- An admin-user, such as awsuser, is necessary to perform this setup. 
SET SESSION AUTHORIZATION 'awsuser';

-- This script generates statements to remove Validio. Run the script, then copy&paste the statements into a worksheet and run them to remove all Validio-related entities (see inline-comments below for more details) from this Snowflake-account. We recommend you to carefully look through the statements before you run then to make sure you really want to remove each entity.
SELECT 'REVOKE ALL ON SCHEMA <egress_schema> FROM <validio_user>;' AS statement UNION ALL  -- Revoke statement for all permissions on egress-schema.
SELECT 'DROP TABLE IF EXISTS ' || schemaname || '.' || tablename || ';' FROM pg_catalog.pg_tables WHERE schemaname = '<egress_schema>' AND tableowner = '<validio_user>; -- Remove this row if you want to keep egress tables' UNION ALL  -- Drop-statements for egress-tables.
SELECT 'DROP GROUP <validio_group>; -- Ignore this row if you have not set up a Validio-group or you use the same group for a source connector' UNION ALL  -- Drop-statement for Validio-group
SELECT 'DROP USER IF EXISTS <validio_user>; -- Ignore this row if you use the same user for a source connector';  -- Drop-statement for Validio-user