Redshift: SSH tunnel

Advanced setup. Use an SSH tunnel only if you can’t use a public endpoint or a VPC endpoint, for example because your cluster is DC2. You run and maintain the bastion host, and its availability limits Delphina’s.

Use this setup when your Redshift endpoint is private and you already run, or are willing to run, an SSH bastion host. Delphina opens an SSH connection to the bastion from a fixed set of IP addresses and forwards its database connection through it to your warehouse. Authentication works with an IAM role or a username and password. Part of the Redshift Connection Guide. Not sure which setup you need? Start there.

Before you start

Ask your Delphina contact for:

Delphina’s IP addresses, for the bastion’s security group.
Delphina’s SSH public key, for the bastion user.
If you use an IAM role: Delphina’s principal ARN and a unique external ID, for the role’s trust policy.
If you use a password: a secure way to share it.

You also need a Linux host in your warehouse’s VPC that you can administer, a Redshift superuser (Step 2, and Step 3 unless you use an IAM role on Serverless), and, for an IAM role, permission to create IAM roles in the AWS account that owns your warehouse. Each step has Serverless and Provisioned tabs where they differ. Serverless endpoints contain redshift-serverless (<workgroup>.<account>.<region>.redshift-serverless.amazonaws.com); provisioned endpoints look like <cluster>.<id>.<region>.redshift.amazonaws.com.

Step 1: Set up the bastion host

Use an existing bastion or launch a small Linux EC2 instance (for example, t4g.nano on Amazon Linux) in a public subnet of your warehouse’s VPC, with an Elastic IP.
In the bastion’s security group, add an inbound rule: TCP 22 from each Delphina IP address.
In your workgroup’s or cluster’s security group, add an inbound rule: TCP on the warehouse port (default 5439) from the bastion’s security group.

On the bastion, create a user for Delphina with no login shell, and add Delphina’s public key:

sudo useradd --create-home --shell /usr/sbin/nologin delphina
sudo mkdir -p /home/delphina/.ssh
echo 'restrict,port-forwarding <DELPHINA_PUBLIC_KEY>' | sudo tee /home/delphina/.ssh/authorized_keys
sudo chown -R delphina:delphina /home/delphina/.ssh
sudo chmod 700 /home/delphina/.ssh
sudo chmod 600 /home/delphina/.ssh/authorized_keys

Limit the user to forwarding connections to your warehouse. Add this to the end of /etc/ssh/sshd_config, where <WAREHOUSE_ENDPOINT> is your workgroup’s or cluster’s endpoint hostname and <PORT> its port:
Match User delphina AllowTcpForwarding local PermitOpen <WAREHOUSE_ENDPOINT>:<PORT> MaxSessions 0 X11Forwarding no AllowAgentForwarding no PermitTTY no
MaxSessions 0 blocks shells and commands while still allowing forwarding, AllowTcpForwarding local blocks reverse tunnels, and PermitOpen allows only your warehouse as a destination. Check the configuration, then reload SSH:
sudo sshd -t && sudo systemctl reload sshd
On Ubuntu and Debian, the service is called ssh instead of sshd.

Step 2: Grant database access

Run this as a superuser, such as the admin user created with your workgroup or cluster, connected to the database Delphina will use: the one you send us in Step 4. A Delphina connection reads one database. Use a superuser if you can. A non-superuser needs the CREATE ROLE permission and the right to grant on every schema and table involved, either by owning them or by holding the privileges WITH GRANT OPTION, and can’t set default privileges for other users’ tables (below).

-- Roles apply to the whole workgroup or cluster, so create it once.
CREATE ROLE delphina_role;

-- Repeat for each schema Delphina should read.
GRANT USAGE ON SCHEMA <schema_name> TO ROLE delphina_role;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO ROLE delphina_role;

Cover tables created later. GRANT SELECT ON ALL TABLES only covers tables that exist when you run it. Set default privileges for each user that creates tables in the schema, such as your dbt, Fivetran, or Airflow users. Redshift applies default privileges per creating user, so one statement doesn’t cover everyone.

-- Repeat for each user that creates tables in the schema.
ALTER DEFAULT PRIVILEGES FOR USER <creator_user> IN SCHEMA <schema_name>
  GRANT SELECT ON TABLES TO ROLE delphina_role;

Other data sources.

Source	Extra grant
Spectrum external schemas (Glue / S3)	`GRANT USAGE ON SCHEMA <external_schema> TO ROLE delphina_role;` The IAM role attached to your cluster or namespace must already be able to read the underlying Glue tables and S3 data (and Lake Formation, if you use it).
Datashares (consumer database)	To read a datashare, send its consumer database as the database in Step 4. Then run `GRANT USAGE ON DATABASE <consumer_database> TO ROLE delphina_role;` If the database was created `WITH PERMISSIONS`, also grant access to its objects: `GRANT USAGE ON SCHEMA <consumer_database>.<schema> TO ROLE delphina_role;` and `GRANT SELECT ON ALL TABLES IN SCHEMA <consumer_database>.<schema> TO ROLE delphina_role;`

Give Delphina access to query history. Delphina learns which tables, joins, and metrics matter from how your team queries them. By default, a non-superuser only sees its own queries in Redshift’s query history, so grant the built-in monitoring role. Run this once; it isn’t per database.

GRANT ROLE sys:monitor TO ROLE delphina_role;

This lets Delphina read the text of every user’s queries. You can choose which users’ queries Delphina uses during onboarding, but that’s a setting in Delphina, not a database permission: the role can still read all of them. If some query text contains values Delphina must not be able to access, talk to your Delphina contact before granting it.

Step 3: Set up authentication

Choose one.

IAM role (recommended)

Serverless
Provisioned

Create an IAM role in the AWS account that owns the workgroup, for example delphina-redshift-access.Trust policy. Your Delphina contact provides the Delphina principal ARN and a unique external ID.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowDelphinaAssumeRole",
      "Effect": "Allow",
      "Principal": { "AWS": "<DELPHINA_PRINCIPAL_ARN>" },
      "Action": "sts:AssumeRole",
      "Condition": { "StringEquals": { "sts:ExternalId": "<EXTERNAL_ID>" } }
    }
  ]
}

Permissions policy. Replace the placeholders with your region, account ID, and workgroup ID (the UUID in the workgroup ARN).

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ServerlessCredentials",
      "Effect": "Allow",
      "Action": [
        "redshift-serverless:GetCredentials",
        "redshift-serverless:GetWorkgroup"
      ],
      "Resource": "arn:aws:redshift-serverless:<REGION>:<ACCOUNT_ID>:workgroup/<WORKGROUP_ID>"
    }
  ]
}

Map the role to the database role. Add a tag to the IAM role with key RedshiftDbRoles and value delphina_role. When Delphina connects, Redshift signs it in as the database user IAMR:delphina-redshift-access and grants it delphina_role.

Delphina connects as a database user named delphina_user. Create it without a password, because Delphina gets short-lived credentials for it through the IAM role, and give it the Delphina role:

CREATE USER delphina_user PASSWORD DISABLE;
GRANT ROLE delphina_role TO delphina_user;

Create an IAM role in the AWS account that owns the cluster, for example delphina-redshift-access.Trust policy. Your Delphina contact provides the Delphina principal ARN and a unique external ID.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "AllowDelphinaAssumeRole",
      "Effect": "Allow",
      "Principal": { "AWS": "<DELPHINA_PRINCIPAL_ARN>" },
      "Action": "sts:AssumeRole",
      "Condition": { "StringEquals": { "sts:ExternalId": "<EXTERNAL_ID>" } }
    }
  ]
}

Permissions policy. Replace the placeholders with your region, account ID, cluster identifier, and database name.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ClusterCredentials",
      "Effect": "Allow",
      "Action": "redshift:GetClusterCredentials",
      "Resource": [
        "arn:aws:redshift:<REGION>:<ACCOUNT_ID>:dbuser:<CLUSTER_ID>/delphina_user",
        "arn:aws:redshift:<REGION>:<ACCOUNT_ID>:dbname:<CLUSTER_ID>/<DATABASE>"
      ]
    },
    {
      "Sid": "DescribeCluster",
      "Effect": "Allow",
      "Action": "redshift:DescribeClusters",
      "Resource": "arn:aws:redshift:<REGION>:<ACCOUNT_ID>:cluster:<CLUSTER_ID>"
    }
  ]
}

Username/password

Create a database user with a strong password and give it the Delphina role. This is the same on Serverless and provisioned.

CREATE USER delphina_user PASSWORD '<strong-password>';
GRANT ROLE delphina_role TO delphina_user;

Redshift passwords must be 8–64 characters with at least one uppercase letter, one lowercase letter, and one number. They can’t contain ', ", \, /, @, or spaces. Share the password with your Delphina contact through a secure channel, never over email or chat.

Step 4: Send us the details

Detail	Example
Bastion public IP or hostname	`203.0.113.10`
Bastion SSH port	`22`
Bastion username	`delphina`
Warehouse endpoint and port	`analytics-wg.123456789012.us-east-1.redshift-serverless.amazonaws.com`, `5439`
Workgroup name or cluster identifier	`analytics-wg`
Node type (provisioned only)	`ra3.4xlarge`
AWS account ID and region	`123456789012`, `us-east-1`
Database	`dev`
Default schema (optional)	`analytics`
Network option	SSH tunnel
IAM role ARN, or username (share the password separately)	`arn:aws:iam::123456789012:role/delphina-redshift-access`

Step 5: Verify the connection

Your Delphina contact adds the connection, runs a test query, and sends you the list of schemas Delphina can see. Check it against what you meant to grant: a missing schema usually means a missing grant, and an extra one means a grant is broader than intended. Once the connection works, onboarding starts. Delphina reads your schema and table metadata and your team’s query history to build its context layer, and your Delphina contact walks you through reviewing it.

Troubleshooting

Problem	Fix
SSH connection times out	Confirm the bastion is in a public subnet with an Elastic IP, and its security group allows Delphina’s IPs on port 22.
SSH authentication fails	Confirm the public key in `authorized_keys` matches what your Delphina contact provided, and that the file and `.ssh` directory are owned by the user with the permissions above.
SSH connects, but the database connection fails	Confirm the warehouse’s security group allows the bastion on the warehouse port, and the `PermitOpen` hostname and port in the `Match User delphina` block match the endpoint exactly.
`AccessDenied` on `sts:AssumeRole`	Check the trust policy’s principal ARN and external ID match what your Delphina contact provided.
`AccessDenied` on `GetCredentials` or `GetClusterCredentials`	Check the permissions policy’s resource ARNs: workgroup ID (Serverless), or cluster identifier, `delphina_user`, and database name (provisioned).
Password authentication fails	Confirm the username and password, and that the user wasn’t created with `PASSWORD DISABLE`.
Connected, but no tables visible	IAM role on Serverless: confirm the `RedshiftDbRoles` tag on the IAM role is exactly `delphina_role`. Otherwise: confirm `delphina_role` is granted to `delphina_user`. Then check `USAGE` on the schema and `SELECT` on its tables.
Tables lose access after a dbt run	Add `ALTER DEFAULT PRIVILEGES` for the user dbt runs as, then re-run `GRANT SELECT ON ALL TABLES IN SCHEMA` to restore access to existing tables.
Spectrum tables fail with a permissions error	The IAM role attached to your cluster or namespace (not Delphina’s role) needs Glue and S3 access to the external data.

Getting Started

Analytics

Context Layer

Administration

Sandbox

Legal

Redshift: SSH tunnel

Before you start

Step 1: Set up the bastion host

Step 2: Grant database access

Step 3: Set up authentication

IAM role (recommended)

Username/password

Step 4: Send us the details

Step 5: Verify the connection

Troubleshooting

​Before you start

​Step 1: Set up the bastion host

​Step 2: Grant database access

​Step 3: Set up authentication

​IAM role (recommended)

​Username/password

​Step 4: Send us the details

​Step 5: Verify the connection

​Troubleshooting

Before you start

Step 1: Set up the bastion host

Step 2: Grant database access

Step 3: Set up authentication

IAM role (recommended)

Username/password

Step 4: Send us the details

Step 5: Verify the connection

Troubleshooting