Skip to main content

How to connect to AWS Redshift

Learn how to connect elvex to your Redshift data

Updated over 3 weeks ago

This guide provides specific instructions for preparing an AWS Redshift database for connection to elvex.

elvex supports connecting to Redshift provisioned clusters as well as Redshift Serverless instances regardless of whether they are publicly hosted or within a private VPC.

Before You Begin

Before connecting elvex to your Redshift database, you should:

  • Have administrator access to your AWS account (Redshift specifically) or work with someone who does

  • Understand which databases, schemas, and tables you want to make accessible

  • Identify which users or groups will need access to this data through elvex

  • Consider any data security or compliance requirements that apply to your data

Step 1: Network Connectivity

There are generally two ways to allow vendors like elvex to connect to your Redshift instance: private connection over AWS PrivateLink or publicly accessible with specific IPs allowed to connect.

Option 1: AWS PrivateLink (Recommended)

This is generally the recommended approach as it allows you to keep your Redshift instance only accessible via private subnets in your VPC.

Redshift-managed PrivateLink

If for some reason, you are unable to or prefer not to follow the instructions below, there are alternatives (e.g. Interface-type PrivateLink). Please contact [email protected] for more information as custom setup is required.

Redshift-managed provides a far simpler setup with no additional cost, which makes it preferable.

  1. Locate the Granted accounts section of the Redshift configuration.

    1. For Redshift clusters this will be on the Properties tab.
      ​

      Redshift Properties tab
    2. For Redshift Serverless, this will be under Data access when viewing a workgroup
      ​

      ​

  2. In the Granted accounts section, click Grant access.

  3. Enter the AWS Account ID 257394462323 (the elvex production AWS account ID).

  4. Choose Grant access to all VPCs.

Option 2: Publicly Accessible with IP Allowlist

Note: This option does expose your Redshift instance to the internet. While network configuration can prevent access, it is generally considered a less secure route than a VPC endpoint.

To enable elvex to connect to your public Redshift cluster you will need to modify the security group (and possibly other network settings) to allow elvex servers to connect.

Ensure you allow inbound traffic on your Redshfit port (normally 5439) to the following IP addresses:

elvex Public IP CIDRs

35.169.109.217/32
44.193.131.243/32
107.23.168.102/32

For more information, see AWS documentation on configuring Redshift security group communication.

Step 2: Read-only User

elvex will access your Redshift database by using username and password credentials you provide directly and are stored securely on our servers.

We strongly recommend creating a read-only user for Redshift that can access the relevant databases and schemas.

AI models can hallucinate and cause dangerous actions. elvex already makes attempts to prevent models from executing anything but SELECT queries, but accidents can happen.

Here's a sample SQL script to create a read-only user with the username elvex (please adapt to your needs):

-- Amazon Redshift: Create Read-Only elvex User
-- Replace placeholders with your actual values

-- Step 1: Create the user
CREATE USER elvex
WITH PASSWORD 'your_secure_password'
NOCREATEDB
NOCREATEUSER;

-- Step 2: Connect to the target database
-- \c your_database;

-- Step 3: Grant database connection
GRANT CONNECT ON DATABASE your_database TO elvex;

-- Step 4: Grant TEMP privilege for temporary tables/views and CTEs
GRANT TEMP ON DATABASE your_database TO elvex;

-- Step 5: Grant access to information_schema and system catalogs
-- Required for metadata queries like column information, constraints, etc.
GRANT SELECT ON information_schema.columns TO elvex;
GRANT SELECT ON information_schema.tables TO elvex;
GRANT SELECT ON information_schema.views TO elvex;
GRANT SELECT ON information_schema.table_constraints TO elvex;
GRANT SELECT ON information_schema.key_column_usage TO elvex;
GRANT SELECT ON information_schema.constraint_column_usage TO elvex;

-- Additional system catalog access
GRANT SELECT ON pg_catalog.pg_tables TO elvex;
GRANT SELECT ON pg_catalog.pg_views TO elvex;
GRANT SELECT ON pg_catalog.pg_class TO elvex;
GRANT SELECT ON pg_catalog.pg_attribute TO elvex;
GRANT SELECT ON pg_catalog.pg_constraint TO elvex;
GRANT SELECT ON pg_catalog.pg_namespace TO elvex;

-- OPTION A: Grant access to a SPECIFIC SCHEMA
-- Replace 'your_schema' with the actual schema name
GRANT USAGE ON SCHEMA your_schema TO elvex;
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema TO elvex;
GRANT SELECT ON ALL VIEWS IN SCHEMA your_schema TO elvex;

-- Set default privileges for future objects in the specific schema
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema
GRANT SELECT ON TABLES TO elvex;

ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema
GRANT SELECT ON VIEWS TO elvex;

-- OPTION B: Grant access to ALL SCHEMAS in the database
-- This approach grants access to all current and future schemas
GRANT USAGE ON ALL SCHEMAS IN DATABASE your_database TO elvex;
GRANT SELECT ON ALL TABLES IN DATABASE your_database TO elvex;
GRANT SELECT ON ALL VIEWS IN DATABASE your_database TO elvex;

-- Set default privileges for future objects in all schemas
ALTER DEFAULT PRIVILEGES
GRANT SELECT ON TABLES TO elvex;

ALTER DEFAULT PRIVILEGES
GRANT SELECT ON VIEWS TO elvex;

Step 3: Prepare Connection Information

In order to add a datasource in elvex that uses Redshift, you will need to note the following information:

Public clusters (Serverless or Provisioned)

  • Host name or IP address

  • Port (default is 5439)

  • Database name

  • Schema name

  • Username

  • Password

Private Serverless clusters

  • Your AWS Account ID

  • Workgroup name

  • Database name

  • Schema name

  • Username

  • Password

Private Provisioned clusters

  • Your AWS Account ID

  • Cluster identifier

  • Database name

  • Schema name

  • Username

  • Password

Step 4: Configure the connection in elvex

Once you've completed the preparation steps, you're ready to configure the Redshift connection in elvex:

  1. In elvex, navigate to the Datasources section

  2. Click "Create New Datasource"

  3. Select "Database Connection" as the datasource type

  4. Choose "Redshift" as the database type

  5. Enter the connection details you gathered above

  6. Select the tables you want to include in your datasource

  7. Review the schema and add any additional descriptions if needed

  8. Configure access permissions for the datasource

  9. Save the datasource

Did this answer your question?