RDS Data API Execute Statement for postgres uses wrong secret value for database name

[fszymanski-blvd]

Describe the bug

I've created a Secrets Manager secret for testing the Data API with Aurora Postgres and I've confirmed that the secret is working in the console using the query editor.
When I try to run the following command:
aws rds-data execute-statement --resource-arn CLUSTER_ARN --secret-arn SECRET_ARN --sql "select * from information_schema.tables;"
it gives me the following error:
An error occurred (DatabaseErrorException) when calling the ExecuteStatement operation: ERROR: database "USERNAME" does not exist; SQLState: 3D000
where USERNAME is the value for the username in the secret.

Expected Behavior

The data API runs successfully in the same way as the query editor.

Current Behavior

dbname is not being read correctly so the data API does not work at all.

Reproduction Steps

1. Create an Aurora postgres instance with instance class and version that allow enabling the data api (I used postgres 13.13 and db.r7g.large). Make sure that the dbname for the database is not postgres.
2. Enable the data api on the cluster.
3. Create a secrets manager secret containing the credentials as described at https://docs.aws.amazon.com/secretsmanager/latest/userguide/reference_secret_json_structure.html#reference_secret_json_structure_rds-postgres
4. Run aws rds-data execute-statement --resource-arn CLUSTER_ARN --secret-arn SECRET_ARN --sql "select * from information_schema.tables;"

Possible Solution

No response

Additional Information/Context

No response

CLI version used

aws-cli/2.15.28

Environment details (OS name and version, etc.)

MacOS Sonoma 14.2.1

[amberkushwaha]

The file is also not changed in the mentioned for positioning of the concept development.

[tim-finnigan]

Thanks @fszymanski-blvd for reaching out and your patience here. The DatabaseErrorException you're seeing is returned by the ExecuteStatement API. Therefore this seems to be an issue involving the API rather than the CLI directly.

Do you have any updates on your end since posting this issue? I couldn't find any related public issues, but did find one internally referencing that error. It linked to these limitations using the RDS Data API and recommended using SCRAM for PostgreSQL password encryption. Not sure if that's helpful here but please let us know if that is something you've looked into.

Otherwise if you're still seeing an issue, please share your debug logs (with any sensitive info redacted) by adding --debug to your command, and we can escalate this to the RDS Data API team if needed.

[fszymanski-blvd]

Thanks @fszymanski-blvd for reaching out and your patience here. The DatabaseErrorException you're seeing is returned by the ExecuteStatement API. Therefore this seems to be an issue involving the API rather than the CLI directly.

Do you have any updates on your end since posting this issue? I couldn't find any related public issues, but did find one internally referencing that error. It linked to these limitations using the RDS Data API and recommended using SCRAM for PostgreSQL password encryption. Not sure if that's helpful here but please let us know if that is something you've looked into.

Otherwise if you're still seeing an issue, please share your debug logs (with any sensitive info redacted) by adding --debug to your command, and we can escalate this to the RDS Data API team if needed.

I haven't done any additional work as this was part of a POC to see if we could use the data api. As mentioned, the data api via the AWS console worked, so I wouldn't expect the issue to be a configuration for my setup.

I noticed that the execture-statement CLI command allows for a database parameter to be passed and I might need to try that. The database is a required field in the secret so I thought that the CLI would grab that value (which is where I thought the error might have occurred). If I find some time I can try to get back to a test with the -database parameter and try to get some logs.