Amazon Redshift

Covers Redshift provisioned clusters and Redshift Serverless. The gateway signs in with a database user and password, or with IAM so that no database password is stored.

Before you start

• A database administrator (the admin user you created with the cluster or workgroup) to run the SQL below.
• The cluster or workgroup endpoint, without the port, such as wg.123456789012.us-east-1.redshift-serverless.amazonaws.com. Add that exact name to the gateway’s allowed hosts (Allow the host); a wildcard on amazonaws.com is refused.

Network access

The gateway connects to the endpoint’s port, 5439 by default. Allow that port from the gateway in the security group of the cluster or workgroup, as described in Network access. TLS is always on. The gateway checks the certificate and host name (sslmode=verify-full) against the CA bundle in Amazon’s Redshift driver, so you don’t need to paste a CA.

Create a read-only login

Run this as the admin user. Replace sales with the schema Portward will read and <password> with a password of 8 to 64 characters with an upper-case letter, a lower-case letter and a digit.

CREATE USER portward_reader PASSWORD '<password>';
GRANT USAGE ON SCHEMA sales TO portward_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO portward_reader;

Tables created later need their own GRANT SELECT. Grant nothing else. Portward can’t expose a table the user could INSERT, UPDATE, DELETE or DROP, and setup refuses a user with CREATE on the schema.

Redshift lets every user create tables in the public schema unless someone has revoked it. If your tables are in public, the check refuses the user for that reason. Revoke it, if nothing else in your warehouse relies on it:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Or sign in with IAM

With IAM, the gateway gets short-lived database credentials from AWS each time instead of storing a password. Setup then asks for an AWS access key whose policy allows only this:

• On a provisioned cluster, redshift:GetClusterCredentials for the portward_reader database user and your database. Setup also asks for that database user’s name.
• On Serverless, redshift-serverless:GetCredentials on the workgroup. Serverless signs the IAM identity in as its own database user, so grant that user the USAGE and SELECT above.

AWS’s guide to IAM database credentials has the policy format. On AWS, the gateway can use its own AWS role instead of an access key once the gateway’s operator sets PORTWARD_REDSHIFT_GATEWAY_ROLE=1.

Connect it in setup

Choose Amazon Redshift and fill in Name (the name people and the AI see, such as sales), Endpoint, Port (empty means 5439), Database (such as dev), Schema (empty means public) and Sign-in. For a database user and password, fill in Database user and Password. For IAM, fill in AWS access key id and AWS secret access key, and on a provisioned cluster Database user too.

What the setup check tests

Before it saves, setup connects and checks, in this order:

1. The endpoint is on the allowed hosts, listed by its exact name (“is on a domain other customers share”).
2. The sign-in fields fit the method: a user and password (“give the database user and its password, or choose IAM”), or, for IAM, a Redshift endpoint and an empty password. IAM sign-in as admin, awsuser, rdsdb, root or master is refused (“IAM sign-in as an administrator user isn’t allowed”).
3. It can connect and sign in. Each failure has its own message: “Redshift refused the sign-in; check the user and password”, “AWS refused the IAM sign-in; check the access key and its Redshift permissions”, or “the server didn’t answer or refused TLS; check the host, port and its firewall”.
4. The user is not a superuser (“connect with a read-only user, not a superuser”).
5. A token computed inside Redshift matches the gateway’s own (“in-database tokens don’t match Python”).
6. The user has none of the roles sys:dba, sys:superuser, sys:secadmin or sys:operator.
7. The user can’t create objects in the schema (“user can CREATE in schema”).
8. It lists the schema’s tables. A table the user could INSERT, UPDATE, DELETE or DROP, or change through a column-level grant, is listed as one it could change, and can’t be exposed.

Each query runs in a read-only transaction that is rolled back.