Redshift
How to connect Redshift to Count
Last updated
How to connect Redshift to Count
Last updated
To connect your database, you'll need to enter:
Host
Port
Database name
Username
Password
You'll also need to ensure that your firewall accepts requests from the following IP addresses:
Connections between Count and your database are encrypted by SSL (TLS). Your database must be configured to use SSL in order for Count to connect.
Once you hit Connect you will see your connection details including a full list of tables accessible with this connection.
Count now supports SSH connections to your redshift database, via a bastion host.
This feature is in beta. Please contact Count support to request access to this feature.
To connect via SSH you must have
a bastion host in your VPC which can connect to your private database
whitelisted Count's server IPs so that we can securely connect to your bastion host VM
details for how to connect to your bastion host, including the host, port, user and a private SSH key.
Details for how to set up a bastion host can be found in your cloud service provider's documentation. On AWS, the documentation for connecting to a redshift instance via a bastion host can be found here.
On Count, you can additionally secure your traffic by specifying an SSH host and the algorithm used to generate it. Our servers will check this value against that provided by your VM on connection, helping to verify its identity. This information can be obtained when generating the SSH key pair. On AWS, this information is available in your EC2 instance System logs; you should see a entry in the log with a list of SSH host key keys
Select an algorithm and verification key pair and enter into the details in the above form.
The Redshift connection in Count supports a number of advanced settings:
Enable case-sensitive identifiers - read more about the impact of this setting here.
Max concurrent connections - Set the maximum number of simultaneous connections between Count and your database. If your database has a lower limit than the value set here, then the lower limit will take precedence.
Query timeout - queries executed from Count will be automatically aborted after this length of time.
If you're having trouble connecting, check the error message returned at the top of the Connection page against the table below for a possible fix.
Error snippet
Possible resolution
GETADDRINFO ENOTFOUND
Is the server running on
Connection time out
Connection refused
Could not connect to server
Failed to establish a connection
ECONNREFUSED
Check your Host and Port settings and that your database server is open to accept connections from the whitelisted Count IP addresses
Password authentication failed for user
Password authentication failed; please check Username, Password, and Database name settings
No PG_HBA.CONF entry for host
SETUSERID: USER
Check the Username with these credentials is authorised to connect to the database from the whitelisted Count IP addresses
If you do not see tables listed in Count that you expect, please check that the permissions for the user account used by Count are configured correctly. To test which tables your credentials have access to, you can try running a query from a Count cell like:
You should also confirm:
The user account used for Count is covered by any IP whitelisting rules you have established.
The user account used for Count has been granted usage permissions on any schemas you require. The permissions need to be granted by an account with schema ownership.
Some permissions may need a new session before they take effect. Count may retain a session for some time after it is last used, so waiting a little before refreshing schema may help.
If you are unable to obtain a copy of your database CA (e.g. some Heroku systems), the only way to initiate encrypted communication with your database is to trust that the certificate sent by the server is correct (SSL mode "require"). This ensures full protection against eavesdropping, but not against MITM attacks. Count will only use this mode of communication if you activate the "Trust server certificate" in the "Advanced" section of the connection setup.
You should consult your database administrator to confirm that this setting is appropriate for your system.
If you are having trouble connecting, reach out to us to schedule a help session
A commonly encountered idiosyncrasy for Redshift users is the Assert error. A query submitted to Redshift can result in an Assert error for a number of reasons, one of them being a bug in the Redshift itself. Below is a list of cases and workarounds for how to amend queries returning Assert errors.
Multiple subqueries in WHERE clause
The following query may result in an Assert error due to the subqueries in the WHERE clause:
Fix 1 - use a CROSS JOIN instead of multiple subqueries
Fix 2 - use a jinja template
