RDS PostgreSQL Import+Export to/from S3

Question

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?

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 '22 at 22:39

Tim · Accepted Answer · 2022-08-24T23:49:29.477

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

Create one policy as below
Create two roles, one named something like rds-import-role and one named rds-export role
Associate the one policy with both of the roles
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

edited Aug 24 '22 at 23:49

answered Aug 24 '22 at 02:08

Tim

31,888
7
52
78

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 '22 at 03: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 '22 at 08:29
I've done this both through the console and the cli, same result. – Jerbot Aug 24 '22 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 '22 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 '22 at 15:52
Suggest you close this question as it has been answered. You're welcome to create a new question when you can describe the problem, you can reference this question in the new question. – Tim Aug 25 '22 at 19:13
Turns out my issue was that there was something bungled in the VM. In the process of troubleshooting I had migrated the db from a t2micro VM to t3micro and everything magically worked. – Jerbot Sep 12 '22 at 15:44
That's unusual. Could it be that a restart applied pending changes? – Tim Sep 12 '22 at 18:39
I restarted the VM several times as troubleshooting steps, and it wasn't until I had done this migration that it worked. :shrug: – Jerbot Sep 12 '22 at 19:22

RDS PostgreSQL Import+Export to/from S3

1 Answers1