Step 1: Create a Limited User in Redshift

1. Connect to Redshift using the SQL client.
2. Execute the following query to create a user to write the data (replace with a password of your choice).

CREATE USER <username> PASSWORD '<password>';

2. Grant user create and temporary privileges on the database. create allows the service to create new schemas and temporary allows the service to create temporary tables.

GRANT CREATE, TEMPORARY ON DATABASE <database> TO <username>;

📘

The schema will be created during the first sync

The schema name supplied as part of Step 4 will be created during the first connection. If you prefer to create the schema yourself, you may remove the CREATE permission from Step 1.3 and instead run:

GRANT ALL ON SCHEMA <schema> TO <username>;
GRANT ALL ON ALL TABLES IN SCHEMA <schema> TO <username>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT ALL ON TABLES TO <username>;

Step 2: Whitelist connection

1. In the Redshift console, click Clusters.
2. Select the cluster you would like to connect.
3. In the General information pane, make note of the Endpoint details. You may need to use the copy icon to ** ** the full details to discover the full endpoint and port number.

4. Click the Properties tab.
5. Scroll down to the Network and security settings section.
6. In the VPC security group field, select a security group to open it.

7. In the Security Groups window, click Inbound rules.
8. Click Edit inbound rules.
9. In the Edit the Inbound rules window, follow the steps below to create custom TCP rules for Prequel's IP:
   a. Select Custom TCP in the drop-down menu.
   b. Enter your Redshift port number. (likely 5439)
   c. Enter the Fairing data syncing service static IP: 35.192.85.117
   d. Click Add rule.

Step 3: Create a staging bucket

Create staging bucket

1. Navigate to the S3 service page and click Create bucket.
2. Enter a Bucket name, select the same region as your Redshift cluster, and modify any of the other settings as desired.
3. Note: Object Ownership can be set to "ACLs disabled" and Block Public Access settings for this bucket can be set to "Block all public access" as recommended by AWS.
4. Click Create bucket.
   
   ##Create policy
5. Navigate to the IAM service page, click on the Policies navigation tab, and click Create policy.
6. Click the JSON tab, and paste the following policy, being sure to replace BUCKET_NAME with the name of the bucket chosen above.
   a. Note: the first permission in the list applies to BUCKET_NAME whereas the second permission applies only to the bucket's contents — BUCKET_NAME/* — an important distinction.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": "arn:aws:s3:::BUCKET_NAME"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": "arn:aws:s3:::BUCKET_NAME/*"
        }
    ]
}

7. Click through to the Review step, choose a name for the policy, for example, transfer-service-policy (this will be referenced in the next step), add a description, and click Create policy.

Create staging bucket user

8. Navigate to the IAM service page, click the Users navigation tab, and click Add users.
9. Enter a User name for the service, for example, transfer-service-user. Under Select AWS access type, select the Access key - Programmatic access option. Click Next: Permissions.
10. Click the Attach existing policies directly option, and search for the name of the policy created in the previous step. Select the policy, and click Next: Tags.
11. Click Next: Review and click Create user.
12. In the Success screen, record the Access key ID and the Secret access key.

Step 4: Add destination

1. Share your host name and staging bucket name with your data provider and they will share a connection form to complete the connection.