Search code examples
pythonamazon-web-servicesboto3amazon-aurora

Python: AWS Aurora Serverless Data API: password authentication failed for user

I am running out of ideas.

I have created a Aurora Serverless RDS (Version 1) with Data API enabled. I now wish to execute SQL statements against it using the Data API (https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html)

I have made a small test script using the provided guidelines (https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html#data-api.calling:~:text=Calling%20the%20Data%20API%20from%20a%20Python%20application)

import boto3

session = boto3.Session(region_name="eu-central-1")
rds = session.client("rds-data")
secret = session.client("secretsmanager")

cluster_arn = "arn:aws:rds:eu-central-1:<accountID>:cluster:aurorapostgres"
secret_arn = "arn:aws:secretsmanager:eu-central-1:<accountID>:secret:dbsecret-xNMeQc"

secretvalue = secret.get_secret_value(
    SecretId = secret_arn
)

print(secretvalue)

SQL = "SELECT * FROM pipelinedb.dataset"

res = rds.execute_statement(
    resourceArn = cluster_arn,
    secretArn = secret_arn,
    database = "pipelinedb",
    sql = SQL
)

print(res)

However I get the error message: BadRequestException: An error occurred (BadRequestException) when calling the ExecuteStatement operation: FATAL: password authentication failed for user "bjarki"; SQLState: 28P01

I have verified the following:

  • Secret value is correct
  • Secret JSON structure is correctly following recommended structure (https://docs.aws.amazon.com/secretsmanager/latest/userguide/reference_secret_json_structure.html)
  • IAM user running the python script has Admin access to the account, and thus is privileged enough
  • Cluster is running in Public Subnets (internet gateways attached to route tables) and ACL and security groups are fully open.
  • The user "bjarki" is the master user and thus should have the required DB privileges to run the query

I am out of ideas on why this error is appearing - any good ideas?

Solution

  • Try this AWS tutorial that is located in the AWS Examples Code Library. It shows how to use the AWS SDK for Python (Boto3) to create a web application that tracks work items in an Amazon Aurora database and emails reports by using Amazon Simple Email Service (Amazon SES). This example uses a front end built with React.js to interact with a Flask-RESTful Python backend.

    Integrate a React.js web application with AWS services.

    List, add, and update items in an Aurora table.

    Send an email report of filtered work items by using Amazon SES.

    Deploy and manage example resources with the included AWS CloudFormation script.

    https://docs.aws.amazon.com/code-library/latest/ug/cross_RDSDataTracker_python_3_topic.html

    Try running the CDK to properly setup the database too.

    Once you successfully implemented this example, you wil get this front end with a Python backend. enter image description here