1

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?

3
  • Your bash script is difficult to read. Can you instead show the roles and policies as implemented, maybe copied from the AWS console? Each RDS instance has one role, it should be easy enough to have multiple policies within the role. The question is also a bit difficult to understand, perhaps you could consider rewording it.
    – Tim
    Aug 23, 2022 at 22:39
  • My edit maybe helps?
    – Jerbot
    Aug 24, 2022 at 1:50
  • Yes, much easier to read and understand.
    – Tim
    Aug 24, 2022 at 2:08

1 Answer 1

1

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

RDS Instance

Roles Added in Console

RDS Roles added using 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

Roles Added Using CLI

9
  • I've already attempted this 2 ways. 1) adding the role and specifying the s3Import feature, then attempting to add the same role again, but specifying s3Export feature. This results in an error saying s3Export is already associated. 2) Creating 2 roles (with complementary policies), adding them, and appropriately selecting s3Import and s3Export. This seems to succeed, but whichever role+feature I add last is the one that functions. The other will not. I believe Aurora and RDS do not behave in the same way for this purpose.
    – Jerbot
    Aug 24, 2022 at 3:23
  • Have you tried doing this using the RDS console? The GUI strongly suggests this is possible. I can give it a go some time if that doesn't work for you.
    – Tim
    Aug 24, 2022 at 8:29
  • I've done this both through the console and the cli, same result.
    – Jerbot
    Aug 24, 2022 at 17:13
  • I just spent a half hour creating a PostgreSQL instance, followed the instructions on the blog post, and it all worked fine for me using console or CLI. I've added screenshots to my answer. If you would like more help suggest you need to provide more information - edit your post. I wonder if it's that you haven't added separate roles for import and export, even though they use the same policy / trust relationship they do need to be separate.
    – Tim
    Aug 24, 2022 at 23:50
  • Yeah, I now think my issue lies elsewhere, actually. I simplified some things based on what you've said here, and both the import and export worked a single time, then subsequent imports would fail (exports continue to work). However, if I wait an hour, it works again just the once. Maybe session related? Maybe instance-tier-limitation related? Still digging in further. I'll leave this question open until I can get a comprehensive understanding of what's going on.
    – Jerbot
    Aug 25, 2022 at 15:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .