Attempting export to s3 on RDS Postgres 13.10 always results in error "credentials stored with the database cluster can’t be accessed"

Subject: Attempting export to s3 on RDS Postgres 13.10 always results in error "credentials stored with the database cluster can’t be accessed"

I have a production PostgreSQL RDS instance that has been running for over a year.

There is a daily job that exports some data to S3 using the aws_s3 extension.

Recently a few jobs using the aws_s3 extension in Postgres started failing. I fiddled around and fixed an error with s3Import, but I can't get any s3Export jobs to work. I am wondering if the community has any suggestions or fixes. Here is the problem and what I have tried so far:

Whenever I try to do a SELECT aws_s3.query_export_to_s3 query I get an error. Here's an example query and the output from psql (I have changed the bucket name for privacy):

postgres=> select * from aws_s3.query_export_to_s3('select 1', aws_commons.create_s3_uri('my-bucket', 's3_test/test.txt', 'us-west-2'));
ERROR:  credentials stored with the database cluster can’t be accessed
HINT:  Has the IAM role Amazon Resource Name (ARN) been associated with the feature-name "s3Export"?
CONTEXT:  SQL function "query_export_to_s3" statement 1

To debug the issue, I created a brand new PostgreSQL RDS instance with all of the same settings as the production instance: Same version of PostgreSQL, same parameter group, same security groups etc. I even created the same role association with the same role for s3Export. (See the end of the message for some outputs describing the two instances.) The most significant difference between the two instances is that one was created in April 2022 and one was created in August 2023.

On the new instance, the above query works without error and exports a file to the bucket.

I have tried rebooting the cluster, updating the cluster to the latest PostgreSQL 13, removing and reinstating the s3Export role association, dropping and re-creating the aws_s3 and aws_commons extensions, and waiting overnight in case some change I made took time to propagate. I still get the error on the production cluster.

So, my question is, how can I fix this? Am I missing something? Thank you, community.

Here are some command results that might be of interest, with identifiers changed for privacy. And FYI, the DB parameter group uses all of the same values as the default Postgres13 parameter group supplied by AWS, except for changing the value of rds.custom_dns_resolution from 0 to 1.

$ aws rds describe-db-instances --db-instance-identifier PROD_INSTANCE_ID --out json
{
    "DBInstances": [
        {
            "DBInstanceIdentifier": "PROD_INSTANCE_ID",
            "DBInstanceClass": "db.t4g.micro",
            "Engine": "postgres",
            "DBInstanceStatus": "available",
            "MasterUsername": "postgres",
            "Endpoint": {
                "Address": "PROD_INSTANCE_ID.abcdefabcdef.us-west-2.rds.amazonaws.com",
                "Port": 5432,
                "HostedZoneId": "Z111111111111"
            },
            "AllocatedStorage": 100,
            "InstanceCreateTime": "2022-04-07T18:33:07.939000+00:00",
            "PreferredBackupWindow": "23:46-00:16",
            "BackupRetentionPeriod": 7,
            "DBSecurityGroups": [],
            "VpcSecurityGroups": [
                {
                    "VpcSecurityGroupId": "sg-11111111111111111",
                    "Status": "active"
                }
            ],
            "DBParameterGroups": [
                {
                    "DBParameterGroupName": "postg-postg-prod-params",
                    "ParameterApplyStatus": "in-sync"
                }
            ],
            "AvailabilityZone": "us-west-2b",
            "DBSubnetGroup": {
                "DBSubnetGroupName": "vpc-11111111111111111",
                "DBSubnetGroupDescription": "Created from the RDS Management Console",
                "VpcId": "vpc-11111111111111111",
                "SubnetGroupStatus": "Complete",
                "Subnets": [
                    {
                        "SubnetIdentifier": "subnet-11111111111111111",
                        "SubnetAvailabilityZone": {
                            "Name": "us-west-2b"
                        },
                        "SubnetOutpost": {},
                        "SubnetStatus": "Active"
                    },
                    {
                        "SubnetIdentifier": "subnet-22222222222222222",
                        "SubnetAvailabilityZone": {
                            "Name": "us-west-2a"
                        },
                        "SubnetOutpost": {},
                        "SubnetStatus": "Active"
                    }
                ]
            },
            "PreferredMaintenanceWindow": "sat:09:00-sat:09:30",
            "PendingModifiedValues": {},
            "LatestRestorableTime": "2023-08-18T19:14:31+00:00",
            "MultiAZ": true,
            "EngineVersion": "13.10",
            "AutoMinorVersionUpgrade": true,
            "ReadReplicaDBInstanceIdentifiers": [
                "arn:aws:rds:us-east-2:111111111111:db:postgres-replica-dev-replica"
            ],
            "LicenseModel": "postgresql-license",
            "OptionGroupMemberships": [
                {
                    "OptionGroupName": "default:postgres-13",
                    "Status": "in-sync"
                }
            ],
            "SecondaryAvailabilityZone": "us-west-2a",
            "PubliclyAccessible": false,
            "StorageType": "gp2",
            "DbInstancePort": 0,
            "StorageEncrypted": true,
            "KmsKeyId": "arn:aws:kms:us-west-2:111111111111:key/KEY_ID_1",
            "DbiResourceId": "db-GGG",
            "CACertificateIdentifier": "rds-ca-2019",
            "DomainMemberships": [],
            "CopyTagsToSnapshot": true,
            "MonitoringInterval": 60,
            "EnhancedMonitoringResourceArn": "arn:aws:logs:us-west-2:111111111111:log-group:RDSOSMetrics:log-stream:db-GGG",
            "MonitoringRoleArn": "arn:aws:iam::111111111111:role/rds-monitoring-role",
            "DBInstanceArn": "arn:aws:rds:us-west-2:111111111111:db:PROD_INSTANCE_ID",
            "IAMDatabaseAuthenticationEnabled": false,
            "PerformanceInsightsEnabled": true,
            "PerformanceInsightsKMSKeyId": "arn:aws:kms:us-west-2:111111111111:key/KEY_ID_1",
            "PerformanceInsightsRetentionPeriod": 7,
            "EnabledCloudwatchLogsExports": [
                "postgresql",
                "upgrade"
            ],
            "DeletionProtection": true,
            "AssociatedRoles": [
                {
                    "RoleArn": "arn:aws:iam::111111111111:role/postg-prod-rds-lambda-invoke-role",
                    "FeatureName": "Lambda",
                    "Status": "ACTIVE"
                },
                {
                    "RoleArn": "arn:aws:iam::111111111111:role/postg-prod-test-rds-s3-role-export",
                    "FeatureName": "s3Export",
                    "Status": "ACTIVE"
                },
                {
                    "RoleArn": "arn:aws:iam::111111111111:role/postg-prod-rds-s3-role",
                    "FeatureName": "s3Import",
                    "Status": "ACTIVE"
                }
            ],
            "MaxAllocatedStorage": 1000,
            "TagList": [

            ],
            "CustomerOwnedIpEnabled": false,
            "ActivityStreamStatus": "stopped",
            "BackupTarget": "region",
            "NetworkType": "IPV4",
            "StorageThroughput": 0,
            "CertificateDetails": {
                "CAIdentifier": "rds-ca-2019",
                "ValidTill": "2024-08-22T17:08:50+00:00"
            }
        }
    ]
}

$ aws rds describe-db-instances --db-instance-identifier DEBUG_INSTANCE_ID --out json
{
    "DBInstances": [
        {
            "DBInstanceIdentifier": "DEBUG_INSTANCE_ID",
            "DBInstanceClass": "db.t4g.micro",
            "Engine": "postgres",
            "DBInstanceStatus": "available",
            "MasterUsername": "postgres",
            "Endpoint": {
                "Address": "DEBUG_INSTANCE_ID.abcdefabcdef.us-west-2.rds.amazonaws.com",
                "Port": 5432,
                "HostedZoneId": "Z111111111111"
            },
            "AllocatedStorage": 100,
            "InstanceCreateTime": "2023-08-18T04:07:15.224000+00:00",
            "PreferredBackupWindow": "23:46-00:16",
            "BackupRetentionPeriod": 7,
            "DBSecurityGroups": [],
            "VpcSecurityGroups": [
                {
                    "VpcSecurityGroupId": "sg-11111111111111111",
                    "Status": "active"
                }
            ],
            "DBParameterGroups": [
                {
                    "DBParameterGroupName": "postg-postg-prod-params",
                    "ParameterApplyStatus": "in-sync"
                }
            ],
            "AvailabilityZone": "us-west-2b",
            "DBSubnetGroup": {
                "DBSubnetGroupName": "vpc-11111111111111111",
                "DBSubnetGroupDescription": "Created from the RDS Management Console",
                "VpcId": "vpc-11111111111111111",
                "SubnetGroupStatus": "Complete",
                "Subnets": [
                    {
                        "SubnetIdentifier": "subnet-11111111111111111",
                        "SubnetAvailabilityZone": {
                            "Name": "us-west-2b"
                        },
                        "SubnetOutpost": {},
                        "SubnetStatus": "Active"
                    },
                    {
                        "SubnetIdentifier": "subnet-22222222222222222",
                        "SubnetAvailabilityZone": {
                            "Name": "us-west-2a"
                        },
                        "SubnetOutpost": {},
                        "SubnetStatus": "Active"
                    }
                ]
            },
            "PreferredMaintenanceWindow": "sat:09:00-sat:09:30",
            "PendingModifiedValues": {},
            "LatestRestorableTime": "2023-08-18T19:14:33+00:00",
            "MultiAZ": true,
            "EngineVersion": "13.10",
            "AutoMinorVersionUpgrade": true,
            "ReadReplicaDBInstanceIdentifiers": [
                "arn:aws:rds:us-east-2:111111111111:db:DEBUG_INSTANCE_ID-replica"
            ],
            "LicenseModel": "postgresql-license",
            "OptionGroupMemberships": [
                {
                    "OptionGroupName": "default:postgres-13",
                    "Status": "in-sync"
                }
            ],
            "SecondaryAvailabilityZone": "us-west-2a",
            "PubliclyAccessible": false,
            "StorageType": "gp2",
            "DbInstancePort": 0,
            "StorageEncrypted": true,
            "KmsKeyId": "arn:aws:kms:us-west-2:111111111111:key/KEY_ID_1",
            "DbiResourceId": "db-HH",
            "CACertificateIdentifier": "rds-ca-2019",
            "DomainMemberships": [],
            "CopyTagsToSnapshot": true,
            "MonitoringInterval": 60,
            "EnhancedMonitoringResourceArn": "arn:aws:logs:us-west-2:111111111111:log-group:RDSOSMetrics:log-stream:db-HH",
            "MonitoringRoleArn": "arn:aws:iam::111111111111:role/rds-monitoring-role",
            "DBInstanceArn": "arn:aws:rds:us-west-2:111111111111:db:DEBUG_INSTANCE_ID",
            "IAMDatabaseAuthenticationEnabled": false,
            "PerformanceInsightsEnabled": true,
            "PerformanceInsightsKMSKeyId": "arn:aws:kms:us-west-2:111111111111:key/KEY_ID_1",
            "PerformanceInsightsRetentionPeriod": 7,
            "EnabledCloudwatchLogsExports": [
                "postgresql",
                "upgrade"
            ],
            "DeletionProtection": true,
            "AssociatedRoles": [
                {
                    "RoleArn": "arn:aws:iam::111111111111:role/postg-prod-test-rds-lambda-invoke-role",
                    "FeatureName": "Lambda",
                    "Status": "ACTIVE"
                },
                {
                    "RoleArn": "arn:aws:iam::111111111111:role/postg-prod-test-rds-s3-role-export",
                    "FeatureName": "s3Export",
                    "Status": "ACTIVE"
                },
                {
                    "RoleArn": "arn:aws:iam::111111111111:role/postg-prod-test-rds-s3-role",
                    "FeatureName": "s3Import",
                    "Status": "ACTIVE"
                }
            ],
            "MaxAllocatedStorage": 1000,
            "TagList": [

            ],
            "CustomerOwnedIpEnabled": false,
            "ActivityStreamStatus": "stopped",
            "BackupTarget": "region",
            "NetworkType": "IPV4",
            "StorageThroughput": 0,
            "CertificateDetails": {
                "CAIdentifier": "rds-ca-2019",
                "ValidTill": "2024-08-22T17:08:50+00:00"
            }
        }
    ]
}

$ aws iam get-role --role postg-prod-test-rds-s3-role-export
{
    "Role": {
        "Path": "/",
        "RoleName": "postg-prod-test-rds-s3-role-export",
        "RoleId": "ROLE_ID",
        "Arn": "arn:aws:iam::111111111111:role/postg-prod-test-rds-s3-role-export",
        "CreateDate": "2023-08-18T04:03:36+00:00",
        "AssumeRolePolicyDocument": {
            "Version": "2012-10-17",
            "Statement": [
                {
                    "Effect": "Allow",
                    "Principal": {
                        "Service": "rds.amazonaws.com"
                    },
                    "Action": "sts:AssumeRole"
                }
            ]
        },
        "MaxSessionDuration": 3600,
        "Tags": [
            {
                "Key": "ProjectName",
                "Value": "postg"
            },
            {
                "Key": "StackName",
                "Value": "postg-prod"
            },
            {
                "Key": "Name",
                "Value": "postg_postg-prod"
            }
        ],
        "RoleLastUsed": {
            "LastUsedDate": "2023-08-18T04:31:16+00:00",
            "Region": "us-west-2"
        }
    }
}
$ aws iam list-role-policies --role postg-prod-test-rds-s3-role-export
{
    "PolicyNames": [
        "s3_export"
    ]
}
$ aws iam get-role-policy --role postg-prod-test-rds-s3-role-export --policy s3_export
{
    "RoleName": "postg-prod-test-rds-s3-role-export",
    "PolicyName": "s3_export",
    "PolicyDocument": {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "s3export",
                "Action": [
                    "s3:PutObject",
                    "s3:AbortMultipartUpload"
                ],
                "Effect": "Allow",
                "Resource": [
                    "arn:aws:s3:::my-bucket/*",
                    "arn:aws:s3:::my-bucket"
                ]
            }
        ]
    }
}