Amazon Redshift Integration

Prerequisites for integrating with Amazon Redshift

Certain credentials and permission are required for Validio to validate your data. For detailed information about permissions in Redshift, refer to Managing database security.

Credential Permission RequirementsValidio Credentials require VIEWER access rights when connecting to sources to read and access data. Admins must ensure that they do not provide EDITOR access rights to their credentials.

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

Copy this script to a SQL worksheet.
Follow the steps described in the script.

SQL

/*--------------------------------------- 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 validio-user.
<user_creating_tables> [, <user2_creating_tables>...]  -- One or more users who will create future tables validio-user should immediately get access to.
<schema_to_monitor> [, <schema2_to_monitor>...]        -- Optionally filter access to one or more schemas 

*/------------------------------------------------ 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 user access to metadata
ALTER USER <validio_user> SYSLOG ACCESS UNRESTRICTED;   -- Grant access to query logs.
GRANT SELECT ON svv_table_info TO <validio_user>;       -- Grant access to table metadata.

-- Grant access to the data to validate. You can either write your own statements, or to simplify the process use the following query which generates grant statements for all
-- existing and future tables in your database, or filtered to specific schemas if you like. Run the query and then copy&paste the result (the generated statements) into a worksheet. 
-- Review the generated statements before you run them, to ensure the grants will apply as you prefer.
SELECT p1 || schema_name || p2 || CASE WHEN o = 0 THEN ':    --------------' ELSE ' TO <validio_user>;' END AS statement
FROM (
    SELECT 0 AS o, '--------------    Privileges for schema ' AS p1, '' AS p2 UNION ALL
    SELECT 1, 'GRANT USAGE ON SCHEMA ', '' UNION ALL
    SELECT 2, 'GRANT SELECT ON ALL TABLES IN SCHEMA ', '' UNION ALL
    SELECT 3, 'ALTER DEFAULT PRIVILEGES FOR USER <user_creating_tables> [, <user2_creating_tables...>...] IN SCHEMA ', ' GRANT SELECT ON TABLES'
) AS s,
information_schema.schemata
--    WHERE schema_name IN ('<schema_to_monitor>' [, '<schema2_to_monitor>'...]) -- Uncomment and edit this line to limit the result to specific schemas
ORDER BY schema_name, o;

Add a Redshift credential

To add a credential for Redshift,

Navigate to Credentials, and click + New credential.
Under Namespace, select a namespace to define where resources will be created and who can access it.
Under Credential Type, select AWS Redshift Credential.
Fill in the Configuration parameters for the credential. Refer to the Redshift Credential Parameters table.
(Optional) Click Test credential to validate that Validio can successfully access the Redshift account. If validation fails, check that you provided the correct parameter values.
(Optional) Check Use for catalog and schema checks to automatically discover assets from this credential and add them to the Catalog page.
Click Create credential.

Validio will automatically start fetching data and you will be able to view Redshift assets and their relationships in the Catalog (if selected) and Lineage pages. Once the credential is created, you can add a source to monitor Redshift data.

Redshift credential parameters

Field	Description
Name	Identifier for the credentials. Used when accessing Sources.
Host	Endpoint of the Redshift cluster database, for example test-redshift-cluster-1.ynypdanx10uk.eu-north-1.redshift.amazonaws.com
Port	Port number of the Redshift database.
User	Username of Redshift account with read access to the desired table.
Password	Password of the specified Redshift user.
Default database	Name of the default Redshift database.
SSL mode	How Validio secures the connection to the Redshift server. Defaults to Prefer. See SSL modes.
TLS CA certificate	(Optional, SSL mode Require, Verify CA, or Verify full) Paste or upload the CA certificate, in PEM format, used to verify the server certificate. Leave empty to use the system trust store.

SSL modes

The SSL mode sets whether Validio encrypts the connection to the Redshift server, and how strictly it verifies the server certificate. The modes are listed from least to most strict.

Mode	Behavior
Disable	Connects without SSL.
Allow	Tries a non-SSL connection first, and falls back to SSL if that fails.
Prefer	Tries an SSL connection first, and falls back to a non-SSL connection if that fails. This is the default.
Require	Connects with SSL only, without verifying the server certificate. If you provide a TLS CA certificate, Validio verifies the server certificate against it, as with Verify CA.
Verify CA	Connects with SSL only, and verifies that a trusted certificate authority issued the server certificate.
Verify full	Connects with SSL only, verifies the server certificate as with Verify CA, and also verifies that the server hostname matches the one in the certificate.

Credentials created before SSL mode was available connect as Prefer. Changing the mode to Disable, Allow, or Prefer removes any stored TLS CA certificate.

Add an Amazon Redshift source

To add a source for Redshift,

Navigate to Sources and click + New source.
Under Source type, select Redshift.
Under Config,
1. Select the valid Credential or create a new credential to authenticate your connection to the data warehouse.
2. You have two options for specifying the dataset and tables to monitor:
  1. Select an existing table — Enter the Database, Schema, and Table to specify where the data comes from. Selecting more than one table will create a new source for each table. Refer to the Source Configuration Parameters table.
  2. Use Custom SQL — Write a valid SQL query to specify the tables to monitor.
3. Set the Polling schedule, which is how frequently the validators on the source will check for changes.
Under Schema, click Continue to automatically infer the schema fields from the tables you selected. If you select many tables, this operation can take a few minutes to complete.
Under Source details,
1. Add Tags to help group related sources or to use for routing notifications.
2. Add an Owner who will be the contact for incident notifications.
3. Assign a Priority, which indicates the importance of incidents detected on this source.
Click Continue to create the source. Source names are generated automatically and will be displayed when the source creation completes. If there are more than 5 sources, you will see the names for the first five and a count of the remaining sources.

Source configuration parameters

Parameter	Description
Database	Name of the Redshift database.
Schema	Name of the schema that contains the table.
Table	Name of the table to read data from.

Remove credentials and permissions

You can use the following SQL script to remove configured permissions for the Source in Validio:

Copy this script to a SQL worksheet.
Follow the steps described in the script.

SQL

/*--------------------------------------- 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 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 Catalog. 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 ' || schema_name || ' FROM <validio_user>;' AS statement FROM pg_catalog.svv_all_schemas UNION ALL  -- Revoke all permissions on schemas from Validio-user...
SELECT 'REVOKE ALL ON ALL TABLES IN SCHEMA ' || schema_name || ' FROM <validio_user>;' FROM pg_catalog.svv_all_schemas UNION ALL  --..and tables and views
SELECT 'REVOKE ALL ON SCHEMA ' || schema_name || ' FROM GROUP <validio_group>;' AS statement FROM pg_catalog.svv_all_schemas UNION ALL  -- Revoke all permissions on schemas from Validio-group... (Remove this row if you haven't set up a dedicated Validio-group)
SELECT 'REVOKE ALL ON ALL TABLES IN SCHEMA ' || schema_name || ' FROM GROUP <validio_group>;' FROM pg_catalog.svv_all_schemas UNION ALL  --..and tables and views (Remove this row if you haven't set up a dedicated Validio-group)
SELECT 'DROP GROUP <validio_group>;' UNION ALL  -- DROP Validio-group (Remove this row if you haven't set up a Validio-group)
SELECT 'DROP USER IF EXISTS <validio_user>;';  -- DROP Validio-user

Introduction

Using Validio

Validio Tutorials

AI Features

Credentials

Sources

Validators

Catalog

Lineage

Incidents & Errors

Notifications

Integrations

Schema & Profiling

Filters

Windows

Segmentation

Thresholds

Access & Settings

Deployment

Security

Other

Amazon Redshift

Prerequisites for integrating with Amazon Redshift

Add a Redshift credential

Redshift credential parameters

SSL modes

Add an Amazon Redshift source

Source configuration parameters

Remove credentials and permissions

​Prerequisites for integrating with Amazon Redshift

​Add a Redshift credential

​Redshift credential parameters

​SSL modes

​Add an Amazon Redshift source

​Source configuration parameters

​Remove credentials and permissions

Prerequisites for integrating with Amazon Redshift

Add a Redshift credential

Redshift credential parameters

SSL modes

Add an Amazon Redshift source

Source configuration parameters

Remove credentials and permissions