💡 This is a step‑by‑step guide to integrate Amazon Redshift and Querio using a dedicated, read‑only user authenticated with username & password.

1. Create a Read‑Only Database User

What: Establish a login that Querio will use to query your warehouse.

How (SQL)

-- Replace <strong_password> with a password you generate
CREATE USER querio_reader WITH PASSWORD '<strong_password>'

You must run this as a superuser or an admin role that has CREATEUSER permission. (AWS Redshift CREATE USER)

2. Grant Required Privileges

Redshift follows the PostgreSQL permission model: without an explicit GRANT the new user cannot see anything. Give the minimal access Querio needs:

2.1 Grant Access to Analytics Schemas

This will give Querio access to the data used for analytics.

GRANT USAGE ON SCHEMA YOUR-SCHEMA TO querio_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA YOUR-SCHEMA TO querio_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA YOUR-SCHEMA
    GRANT SELECT ON TABLES TO

• Replace YOUR-SCHEMA above with the desired analytics schemas (e.g., public,analytics, mart)

• USAGE lets the user reference objects in the schema. (AWS Redshift GRANT USAGE)

• SELECT grants read access to existing tables. (AWS Redshift GRANT SELECT)

• ALTER DEFAULT PRIVILEGES ensures future tables are also visible. (AWS Redshift ALTER DEFAULT PRIVILEGES)

2.2 Grant Access to Information Schema

This will give Querio access to the necessary metadata used to understand your data structure.

-- -----------------------------
-- Introspection metadata access
-- Allows Querio to read INFORMATION_SCHEMA views
-- (tables, columns, foreign keys, etc.)
-- -----------------------------
GRANT USAGE ON SCHEMA information_schema TO querio_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO

3. Whitelist Querio's Outbound IP (Port 5439)

What: Allow Querio’s analytics engine (running in AWS) to reach your Redshift cluster on the default PostgreSQL port (5439) by adding an inbound rule that targets our fixed egress IP.

Querio IP address

How (AWS Console)

1. Open the Amazon Redshift console → Clusters → your‑cluster → Network and security tab.

2. Note the VPC security groups attached to the cluster. Click the group ID to open it in the EC2 Security Groups page.

3. Inbound rules → Edit inbound rules → Add rule:

4. Inbound rules → Edit inbound rules → Add rule:

5. Save rules.

How (AWS CLI)

aws ec2 authorize-security-group-ingress \
  --group-id sg-0123456789abcdef0 \
  --protocol tcp --port 5439 \
  --cidr 3.74.220.168 \
  --description "Querio outbound production IP"
  
  
  aws ec2 authorize-security-group-ingress \
  --group-id sg-0123456789abcdef0 \
  --protocol tcp --port 5439 \
  --cidr 82.32.77.215 \
  --description "Querio outbound staging IP"

To enforce encrypted transport, enable the parameter require_SSL=true in your cluster (or workgroup) parameter group. (AWS Redshift require_SSL parameter)

4. Collect & Share Connection Details for Querio

What to Collect

Once collected, share these details with the Querio team: