Postgres DB engine: 14.10
After much experiment, I found out that Aurora Postgres Cluster can not access S3 for copying data. For otherwise exactly same setup, a native RDS Postgres instance can access s3 for copying data.
I followed this article on setting up the environment: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PostgreSQL.S3Import.html
I have a Aurora postgres cluster - playground-1-cluster
and a postgres (not Aurora) - test-db-1-s3-copy
In the end, I found postgres (not Aurora) - test-db-1-s3-copy
is able to access S3 and copied the data. But Aurora postgres cluster - playground-1-cluster
is met with error: "SQL Error [XX000]: ERROR: Unable to generate pre-signed url, look at engine log for details."
Here are the summary steps:
- IAM Role Permissions - role -
rds-s3-import-role
has the necessary permissions to access the S3 bucket. The role has policy attached - s3:GetObject
, s3:ListBucket
, on the relevant S3 bucket and bucket/*
- Trust Relationship. The IAM role has a trust relationship with the RDS service to assume the role.
- Attached the role to the RDS, so it can assume the role.
- The role is added to the DB cluster for Aurora-Postgres. It is not possible to add to a write instance directly in the cluster
aws rds add-role-to-db-cluster \
--feature-name s3Import \
--db-cluster-identifier arn:aws:rds:eu-west-1:1234567890:cluster:playground-1-cluster \
--role-arn arn:aws:iam::1234567890:role/rds-s3-import-role \
--region eu-west-1
- It is not possible add the role to the instance direct for **Aurora-Postgres**
aws rds add-role-to-db-instance \
--db-instance-identifier playground-1\
--feature-name s3Import \
--role-arn arn:aws:iam::1234567890:role/rds-s3-import-role \
--region eu-west-1
An error occurred (InvalidDBInstanceState) when calling the AddRoleToDBInstance operation: The playground-1 DB instance is associated with a database cluster. Manage the arn:aws:iam::1234567890:role/rds-s3-import-role IAM role from the cluster instead of from the DB instance.
- Or added to the db instance directly for RDS Postgres
aws rds add-role-to-db-instance \
--db-instance-identifier test-db-1-s3-copy \
--feature-name s3Import \
--role-arn arn:aws:iam::1234567890:role/rds-s3-import-role \
--region eu-west-1
- Check S3 Bucket Policy is not blocking the access from this role.
- VPC Endpoint for S3 to allow private data subnets access. This is not mentioned in the article, but is needed.
- Create aws_s3 extension and its dependencies on the db instance.
It looks to me that the problem is we can't attach the role rds-s3-import-role
to a db instance in Aurora cluster, which is required for access S3.
Please advice. Thanks