RDS PostgreSQL 13.10导出S3报错：无法访问集群存储凭证

问题背景 #

运行超1年的生产环境PostgreSQL RDS实例，每日通过aws_s3扩展导出数据到S3，近期s3Export任务持续失败，s3Import功能已修复。执行导出查询时触发以下错误：

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

已创建与生产配置完全一致的新实例（仅创建时间不同），新实例可成功完成S3导出，排除IAM角色权限、S3桶配置等通用问题。

已执行的排查操作：

• 重启RDS集群
• 升级到PostgreSQL 13.10最新版本
• 移除并重新关联s3Export角色
• 删除重建aws_s3和aws_commons扩展
• 等待配置生效（超过24小时）

配置信息对比 #

生产实例配置 #

$ 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"}}]}

IAM角色配置 #

$ 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"]}]}}

遗漏排查点及解决建议 #

• 调整IAM角色关联顺序：先移除所有关联角色，再按s3Export、s3Import、Lambda的顺序重新关联，避免潜在的角色加载冲突。
• 验证KMS密钥权限：检查生产实例使用的KMS密钥信任策略，确保允许s3Export角色或rds.amazonaws.com服务执行kms:Decrypt操作（若S3对象使用该密钥加密）。
• 确认PostgreSQL用户权限：执行导出的用户需拥有rds_s3角色，执行以下命令验证并授予：

  GRANT rds_s3 TO <your-export-user>;
  

• 查看RDS详细日志：在PostgreSQL日志中搜索s3Export相关条目，获取更具体的错误信息（如角色无法被assume、临时凭证获取失败）。
• 临时切换单AZ测试：将生产实例临时切换为单AZ模式，重启后测试导出，若成功再切回MultiAZ，触发配置同步。
• 检查VPC端点策略：若使用VPC端点访问S3，确认端点策略允许s3Export角色执行s3:PutObject操作，且端点状态正常。
• 创建全新s3Export角色：配置相同权限的新角色关联到生产实例，测试导出是否成功，排除原角色的隐藏配置问题。

内容的提问来源于stack exchange，提问作者meva