As a user, you can use Amazon Redshift Connector after the organization administrator ensures that users have access to the Secure Agent directory that contains the success and error files. This directory path must be the same on each Secure Agent machine in the runtime environment. The organization administrator must also perform the following tasks:
•Get the Amazon Redshift JDBC URL.
•Manage Authentication. Use either of the following two methods:
- Create an Access Key ID and Secret Access Key.
Provide the values for access key ID and secret access key when you configure the Amazon Redshift connection. For more information about creating an access key ID and secret access key, see the AWS documentation.
- Configure AWS Identity and Access Management (IAM) Authentication to enhance security.
If you use IAM authentication, do not provide access key ID and secret access key explicitly in the Amazon Redshift connection. Instead, you must create an Redshift Role Amazon Resource Name (ARN), add the minimal Amazon IAM policy to the Redshift Role ARN, and add the Redshift Role ARN to the Redshift cluster.
Provide the Redshift Role ARN in the AWS_IAM_ROLE option in the UNLOAD and COPY commands when you create a task.
If you specify both, access key ID and secret access key in the connection properties and AWS_IAM_ROLE in the UNLOAD and COPY commands, AWS_IAM_ROLE takes the precedence.
You must add IAM EC2 role and IAM Redshift role to the customer master key when you use IAM authentication and server-side encryption using customer master key.
Hosted Agent does not support IAM authentication. For more information about how to configure IAM authentication for Amazon Redshift Connector, see IAM authentication
•Configure Amazon Redshift for SSL if you want to support an SSL connection.
•Create a master symmetric key if you want to enable client-side encryption.
•Create an AWS Key Management Service (AWS KMS)-managed customer master key if you want to enable server-side encryption.
•Create minimal Amazon IAM policy for Amazon Redshift Connector.
• When you create a temporary table for an upsert, update, or delete operation in the local staging area, you must create the temporary table in the following format:
Note: By default, you have the permission to create the temporary tables as you have the PUBLIC group membership. To deny the permission, revoke the TEMP permission from the PUBLIC group and allow the TEMP permission to specific or groups of individuals.
Configure Amazon Redshift Connector for SSL
To use SSL to connect to Amazon Redshift, you need to configure the Secure Agent for SSL and enable SSL through the JDBC URL in the Amazon Redshift connection properties.
1Download the SSL certificate for Amazon Redshift.
2To add the certificate file to the key store, run the following command in the command prompt:
3Log in to your Informatica Intelligent Cloud Services account.
4In Administrator, click Runtime Environments.
5Select the Secure Agent and click Edit Secure Agent in the Actions menu.
6In the System Configuration Details section, configure the following properties:
aSelect the Service as Data Integration Server.
bSelect the Type as DTM.
cClick the Edit Agent Configuration icon next to JVMOption1 and add the following value:
-Djavax.net.ssl.trustStore=<trustore_path>
dClick the Edit Agent Configuration icon next to JVMOption2 and add the following command:
-Djavax.net.ssl.trustStorePassword=<password>
Specify the same password you used when importing the certificate in step 2.
7Click Save.
8In Administrator, click Connections.
9Edit your Amazon Redshift connection and add the following parameter to the JDBC URL: ssl=true.
For example, jdbc:redshift://mycluster.xyz789.us-west- 2.redshift.amazonaws.com:5439/dev?ssl=true.
10Click Save.
Create a minimal Amazon IAM policy
Create an Amazon IAM policy and define the required permissions to stage the data in Amazon S3 when you want to read data from and write data to Amazon Redshift.
Use the following minimum required permissions to stage the data in Amazon S3:
•PutObject
•GetObject
•DeleteObject
•ListBucket
•GetBucketPolicy
You can use the following sample Amazon IAM policy:
You must make sure that the Amazon S3 bucket and Amazon Redshift cluster reside in the same region to run a session successfully.
The supported regions are:
•Asia Pacific (Mumbai)
•Asia Pacific (Seoul)
•Asia Pacific (Singapore)
•Asia Pacific (Sydney)
•Asia Pacific (Tokyo)
•AWS GovCloud
•Canada (Central)
•China (Bejing)
•EU (Ireland)
•EU (Frankfurt)
•South America (Sao Paulo)
•US East (N. Virginia)
•US East (Ohio)
•US West (N. California)
•US West (Oregon)
IAM authentication
Optional. You can configure IAM authentication when on an Amazon Elastic Compute Cloud (EC2) system. Use IAM authentication for secure and controlled access to Amazon Redshift resources when you run
Use IAM authentication when you want to run a on an EC2 system. Perform the following steps to configure IAM authentication:
2Create the Amazon EC2 role. Associate the minimal Amazon IAM policy while creating the EC2 role. The Amazon EC2 role is used when you create an EC2 system in the Redshift cluster. For more information about creating the Amazon EC2 role, see the AWS documentation.
3Create an EC2 instance. Assign the Amazon EC2 role that you created in step #2 to the EC2 instance.
4Create the Amazon Redshift Role ARN for secure access to Amazon Redshift resources. Associate the minimal Amazon IAM policy while creating the Amazon Redshift role. You can use the Amazon Redshift Role ARN in the UNLOAD and COPY commands. For more information about creating the Amazon Redshift Role ARN, see the AWS documentation.
5Add the Amazon Redshift Role ARN to the Amazon Redshift cluster to successfully perform the read and write operations. For more information about adding the Amazon Redshift Role ARN to the Amazon Redshift cluster, see the AWS documentation.