大网数据广告活动
服务器运维问答

RDS PostgreSQL Import+Export to/from S3

· 52CE 我爱测

Is it possible to enable both importing and exporting with an RDS PostgreSQL instance to an S3 bucket? I've been able to use the following pattern to enable one or the other with consistent success: rds-s3-io-role { "Version": "2…

问题原文

原帖链接 · Jerbot · CC BY-SA 4.0

Is it possible to enable both importing and exporting with an RDS PostgreSQL instance to an S3 bucket? I've been able to use the following pattern to enable one or the other with consistent success:

  • rds-s3-io-role
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole",
            "Condition": {
                "StringEquals": {
                    "aws:SourceArn": "arn:aws:rds:us-west-2:112233445566:db:dbname",
                    "aws:SourceAccount": "112233445566"
                }
            }
        }
    ]
}
  • rds-s3-io-policy
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts",
                "s3:PutObject",
                "s3:ListBucket"
            ],
            "Effect": "Allow",
            "Resource": [
                "arn:aws:s3:::bucket_name",
                "arn:aws:s3:::bucket_name/*"
            ]
        }
    ]
}

I am attaching the policy to the role like so

aws iam attach-role-policy \
    --policy-arn "arn:aws:iam::112233445566:policy/rds-s3-io-policy"\
    --role-name "rds-s3-io-role"

Unfortunately, I can only add s3Import or s3Export like so

aws rds add-role-to-db-instance \
    --db-instance-identifier "db_name" \
    --feature-name s3Import \
    --role-arn "arn:aws:iam::112233445566:role/rds-s3-io-role" \
    --region us-west-2

How can I enable both s3Import and s3Export on the same database instance? Is there another 'feature' that I can somehow enable on the role that will allow me to use the aws_s3.query_export_to_s3() and aws_s3.table_import_from_s3() functions within PostgreSQL?

已采纳解答

原帖链接 · Tim · CC BY-SA 4.0

This AWS blog post suggests you simply add both roles to the database. I know it talks about Aurora, but I think it will work for PostgreSQL. It looks like this type of feature role is separate from the instance role. I've done something similar with Oracle RDS in the past.

My poking around in the console suggests that the RDS database can have multiple roles, as on the main page for the database there's a section "Current IAM roles for this instance".

The general steps seem to be

  1. Create one policy as below
  2. Create two roles, one named something like rds-import-role and one named rds-export role
  3. Associate the one policy with both of the roles
  4. Add the roles to the database
aws rds add-role-to-db-cluster --db-cluster-identifier aurora-postgres-cl --feature-name s3Export --role-arn arn:aws:iam::123456789012:role/aurora-s3-export-role

aws rds add-role-to-db-cluster --db-cluster-identifier aurora-postgres-cl --feature-name s3Import --role-arn arn:aws:iam::123456789012:role/aurora-s3-import-role

Policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:AbortMultipartUpload",
                "s3:DeleteObject",
                "s3:ListMultipartUploadParts",
                "s3:PutObject",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::aurora-pg-sample-loaddata01/*",
                "arn:aws:s3:::aurora-pg-sample-loaddata01"
            ]
        }
    ]
}

Trust Relationship

{"Version": "2012-10-17","Statement": [
    {
      "Effect": "Allow","Principal": {
        "Service": "rds.amazonaws.com"
      },"Action": "sts:AssumeRole"
    }
  ]
}

Demo

I tried this myself, which took about 20 mins. Here's the RDS instance

Roles Added in Console

Roles Added Using CLI

aws rds add-role-to-db-instance --db-instance-identifier timtest1 --feature-name s3Import --role-arn arn:aws:iam::123456789:role/tim-temp-postgresql-test-import

aws rds add-role-to-db-instance --db-instance-identifier timtest1 --feature-name s3Export --role-arn arn:aws:iam::123456789:role/tim-temp-postgresql-test-export

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2022-08-23)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 4.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。