Configure Redshift, Snowflake, and Postgres during Migration

Before You Begin: Identify any custom roles assigned to your Serverless instance for Redshift, Snowflake, or Postgres. Locate the <instanceId>_dataload_drDataSourcePump serverless instance Lambda, replacing instanceId with the instance ID of your Qrvey platform. Select Configuration > Permissions and open the execution role in a new window. Identify and open any custom policies related to the Redshift, Snowflake, or Postgres databases so the information can be retrieved later.

1. Access the Qrvey Kubernetes Cluster

1. Log in to the Qrvey AWS account.
2. Open the Amazon Elastic Kubernetes Service (EKS) console.
3. Navigate to Clusters.
4. Select the appropriate Qrvey Kubernetes cluster.
5. Go to the Access tab.
6. Scroll down to the Pod Identity associations section.
7. Open the associated IAM Role.

2. Add Inline Policies to the Pod Identity Role

For each data source (Redshift, Snowflake, and Postgres), you must add the corresponding IAM policy to the Pod Identity role.

---

IAM Policy for Redshift

1. In the Pod Identity IAM Role, click Add inline policy.
2. Go to the JSON tab.
3. Paste the following policy, replacing {AWS_ACCOUNT_REDSHIFT} with the Redshift AWS account number:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "sts:AssumeRole",
                "sts:TagSession"
            ],
            "Resource": [
                "arn:aws:iam::{AWS_ACCOUNT_REDSHIFT}:role/RedshiftDataAccessRole"
            ]
        }
    ]
}

4. Locate the IAM role named:

<instanceID>_dataload_DbDatasourceExportToS3Role_MP

5. Go to the Trust Relationships tab.
6. Add or update the following trust policy, replacing {AWS_ACCOUNT_REDSHIFT} with your actual values:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::{AWS_ACCOUNT_REDSHIFT}:role/RedshiftDataAccessRole"
            },
            "Action": [
                "sts:AssumeRole",
                "sts:TagSession"
            ]
        },
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

7. Save the Pod Identity IAM Role.
8. Update the RedshiftDataAccessRole in the Redshift account by adding the following statement to its policy:

{
    "Sid": "VisualEditor1",
    "Effect": "Allow",
    "Action": [
        "sts:AssumeRole",
        "secretsmanager:GetSecretValue",
        "sts:TagSession"
    ],
    "Resource": [
        "arn:aws:iam::767397749050:role/pbqxz_dataload_DbDatasourceExportToS3Role_MP"
    ]
}

---

IAM Policy for Snowflake

1. In the Pod Identity IAM Role, click Add inline policy.
2. Open the JSON tab and paste the following policy, replacing <SECRET_ARN> and <KMS_KEY_ARN> with your actual values:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "getSecretValue",
      "Effect": "Allow",
      "Action": [
        "secretsmanager:GetSecretValue"
      ],
      "Resource": [
        "<SECRET_ARN>"
      ]
    },
    {
      "Sid": "getPublicKey",
      "Effect": "Allow",
      "Action": [
        "kms:GetPublicKey",
        "kms:Sign"
      ],
      "Resource": ["<KMS_KEY_ARN>"]
    }
  ]
}

3. Save the Pod Identity IAM Role.

---

IAM Policy for Postgres

1. In the Pod Identity IAM Role, click Add inline policy.
2. Open the JSON tab and paste the following policy, replacing {AWS_ACCOUNT_POSTGRES} with your actual values:

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": [
            "sts:AssumeRole",
            "sts:TagSession"
        ],
        "Resource": [
            "arn:aws:iam::{AWS_ACCOUNT_POSTGRES}:role/rds-s3-export-role"
        ]
    }
}

3. Save the Pod Identity IAM Role.
4. Go to the Trust Relationships tab.
5. Add or update the following trust policy, replacing {AWS_ACCOUNT_POSTGRES} with your actual values:

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": [
            "sts:AssumeRole",
            "sts:TagSession"
        ],
        "Resource": [
            "arn:aws:iam::{AWS_ACCOUNT_POSTGRES}:role/rds-s3-export-role"
        ]
    }
}

6. Save

Restart Services

Restart the services to apply the Redshift, Snowflake, and Postgres permissions:

# Be sure to replace linux/amd64 with your actual platform type.
docker run --platform=linux/amd64 -v $(pwd)/config.json:/app/qrvey/config.json -it --rm qrvey.azurecr.io/qrvey-terraform-aws:${qrvey_version} restart