- 07 Dec 2022
- 1 Minute to read
-
Print
-
DarkLight
Redshift Connection Configuration
- Updated on 07 Dec 2022
- 1 Minute to read
-
Print
-
DarkLight
This is a machine-translated version of the original Japanese article.
Please understand that some of the information contained on this page may be inaccurate.
summary
This is a help page for setting Redshift connection information.
In order to transfer data to Redshift at high speed, trocco saves the data in S3 and bulk loads it to Redshift with the COPY command.
Therefore, in the Redshift connection information, it is necessary to set permissions etc. for both S3 and Redshift.
Create IAM User
Data transfer to Redshift may temporarily store data in S3.
At that time, you will use IAM User credentials, so create an IAM User with the following permissions and enter your AWS credentials.
- s3:GetObject
- s3:PutObject
- s3:DeleteObject
- s3:ListBucket
- sts:GetFederationToken
- s3: GetBucketLocation (when using buckets in different regions)
To grant the least privileges, refer to the following policies:
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "VisualEditor0",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:GetObject",
"s3:ListBucketMultipartUploads",
"s3:AbortMultipartUpload",
"sts:GetFederationToken",
"s3:ListBucket",
"s3:DeleteObject",
"s3:GetBucketLocation"
],
"Resource": [
"arn:aws:s3:::<YOUR_DESTINATION_BUCKET_NAME>",
"arn:aws:s3:::<YOUR_DESTINATION_BUCKET_NAME>/*",
"arn:aws:sts::<YOUR_AWS_ACCOUNT_ID>:federated-user/<YOUR_IAM_USER_NAME>"
]
}
]
}
Redshift user creation and permission setting
You must create a user with the following privileges:
- CREATE/DROP TABLE PERMISSION IN DESTINATION SCHEMA
- Permission to execute the COPY command in the destination schema
To grant the minimum privileges to the user, refer to the following SQL command.
ALTER DEFAULT PRIVILEGES FOR USER <your_db_user_name> IN SCHEMA <your_destination_schema>
GRANT
SELECT,
INSERT,
DELETE
ON TABLES TO <your_db_user_name>;
GRANT USAGE ON SCHEMA <your_destination_schema> TO <your_db_user_name>