Amazon Redshift

Connect an Amazon Redshift cluster or Redshift Serverless workgroup to Fresco BI. Fresco BI only reads: it lists schemas, tables, views and Spectrum external tables, and imports them into a Data Table.

Feature	Supported
Import	Yes
DirectQuery	No
SSL/TLS	6 modes, default require
SSH tunnel	Yes
Cloud	Yes
On-premises (via Gateway)	Yes

Prerequisites

• A Redshift provisioned cluster or Serverless workgroup, and its endpoint.
• A superuser or admin who can create a user and grant privileges.
• Network access from Fresco BI to the endpoint on port 5439, an SSH bastion in the VPC, or a Gateway Agent that can reach the cluster.

Step 1: Create a read-only user

Connect to the database as an admin and run, once per schema you want to read:

CREATE USER fresco_reader PASSWORD '<strong-password>';
GRANT USAGE ON SCHEMA <schema> TO fresco_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO fresco_reader;

The grant above does not cover tables created later. Add a default privilege for the user that creates tables in the schema:

ALTER DEFAULT PRIVILEGES FOR USER <table_owner> IN SCHEMA <schema>
  GRANT SELECT ON TABLES TO fresco_reader;

For a Spectrum external schema, grant USAGE on that schema too:

GRANT USAGE ON SCHEMA <external_schema> TO fresco_reader;

Fresco BI tags its queries with the query group frescobi, so you can find them in WLM and the system tables. It only reads, but still use a user that cannot write.

Step 2: Allow network access

Cloud. Allow inbound connections from Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on port 5439.

• Make the cluster or Serverless workgroup Publicly accessible.
• In the VPC security group attached to it, add an inbound rule: type Redshift, TCP 5439, source each IP as /32.

SSH tunnel. If the cluster is private, run a bastion host in the same VPC, keep Cloud and turn on the SSH tunnel in the form. Allow Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on the bastion’s SSH port, and allow the bastion’s security group on 5439.

On-premises (via Gateway). If the cluster is reachable only from your private network (for example over VPN or Direct Connect), choose On-premises (via Gateway); this panel then switches to the Gateway guide.

Step 3: Fill in the form

1. Connection type: Cloud or On-premises (via Gateway). For On-premises, pick the Gateway cluster.
2. Display name: the name your team sees in Fresco BI.
3. Host: the endpoint host only. From my-cluster.abc123.us-east-1.redshift.amazonaws.com:5439/dev, enter my-cluster.abc123.us-east-1.redshift.amazonaws.com.
4. Port: 5439 is filled in.
5. Database: the database name, e.g. dev.
6. User and Password: the fresco_reader user from Step 1.
7. SSL mode: see the table below. Keep require or stronger.
8. CA certificate (PEM): shown for verify-ca and verify-full. Paste the CA that signed the server certificate (required for verify-ca); empty uses the system trust store for verify-full.
9. Connect through an SSH tunnel (bastion) (Cloud only): fill SSH host, SSH port (22), SSH user, SSH private key (OpenSSH or PEM) and Key passphrase (if any). Keys must be Ed25519 or RSA of at least 3072 bits. The first test shows the bastion’s key; compare it with the fingerprint from your administrator and click Trust this key, then test again.
10. Click Test connection, then Save.

Each table is read in its own read-only transaction, so tables in one load are not a single point-in-time snapshot.

SSL modes

Mode	What it does
disable	No encryption
allow	Encrypts only when the server requires it
prefer	Encrypts when the server supports it
require (default)	Always encrypts; the certificate is not checked
verify-ca	Encrypts and checks the certificate is signed by a trusted CA
verify-full	Encrypts, checks the CA and the host name (most secure)

The CA certificate (PEM) field appears only for verify-ca and verify-full. verify-ca needs a CA certificate.

Troubleshooting

• The server rejected the user name or password. Check the user and password from Step 1.
• The server did not answer in time. Usually a firewall that drops packets, or a wrong host or port. Check the cluster is publicly accessible and the security group allows Fresco BI’s outbound IP addresses (the setup guide beside the data source form lists them) on 5439.
• No connection could be opened to this host and port. Check the host, the port, and that the firewall allows Fresco BI’s IP. Check the endpoint host and port 5439.
• The host name could not be resolved (DNS). Check the Host field. Enter only the endpoint host, without :5439/dev.
• The host is in a blocked range (private, loopback or metadata). A database inside a private network needs an On-premises connection through a Gateway. The endpoint resolves to a private VPC address: make it publicly accessible, use an SSH tunnel, or choose On-premises (via Gateway).
• The server certificate is not signed by a trusted CA. Paste the right CA into the CA certificate field. Paste the CA bundle for your Redshift endpoint.
• The database user cannot read this object. Grant USAGE on the schema and SELECT on the table (Step 1).
• The database or object was not found on the server. Check the Database name.
• A size or concurrent-connection limit was exceeded. The cluster hit a cursor result size or connection limit; check the cluster’s limits and WLM queues, or retry when it is less busy.