Why can I not connect to an RDS Aurora DB via proxy?

Question

I setup a Postgresql Aurora DB and a Proxy via Terraform (code below), which is apparently running fine. But for some reason I can not connect to the DB through the proxy. The proxy claims that there are lacking credentials for the role, but if I connect directly to the DB everything is fine and the credentials are working.

I tried this from a VPN and directly from an EC2 instance:

$ psql -h [aurora-endpoint] -p 5432 -d [database] -U admin
Password for user admin: 
psql (13.3, server 11.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

[database]=> 

This works, but when I try to connect to the proxy:

$ psql -h [proxy-endpoint] -p 5432 -d [database] -U admin
psql: error: FATAL:  This RDS proxy has no credentials for the role cellwerkadmin. Check the credentials for this role and try again.
FATAL:  This RDS proxy has no credentials for the role cellwerkadmin. Check the credentials for this role and try again.

Does anyone have an idea what the problem is here?

Terraform code:

# Subnet group for Aurora
resource "aws_db_subnet_group" "aurora_sg_group" {
  name       = "aurora"
  subnet_ids = var.private_subnets_ids

  tags = {
    Name = "Subnet group for the Aurora DB"
  }
}

# RDS cluster parameter group for Aurora
resource "aws_rds_cluster_parameter_group" "aurora_eu_central_1" {
  name_prefix = "eu-central-1-aurora-postgres11-cluster-parameter-group"
  family      = "aurora-postgresql11"
  description = "eu-central-1-aurora-postgres11-cluster-parameter-group"
}

# Aurora RDS postgresql

module "aurora" {
  source = "../modules/terraform-aws-rds-aurora/"

  name                   = "cellwerk-aurora"
  username               = data.aws_ssm_parameter.db_username.value
  create_random_password = false
  password               = data.aws_ssm_parameter.db_password.value
  engine                 = "aurora-postgresql"
  engine_version         = "11.9"
  instance_type          = "db.r6g.large"
  instance_type_replica  = "db.t3.medium"

  vpc_id                = module.link_delivery_eu_central_1.vpc_id
  db_subnet_group_name  = "aurora"
  create_security_group = false
  allowed_cidr_blocks   = concat(... subnets )
  vpc_security_group_ids = [aws_security_group.rds.id]

  replica_count         = 1
  replica_scale_enabled = true
  replica_scale_min     = 1
  replica_scale_max     = 5

  monitoring_interval           = 60
  iam_role_name                 = "aurora-eu-central-1-enhanced-monitoring"
  iam_role_use_name_prefix      = false
  iam_role_description          = "eu-central-1 Aurora RDS enhanced monitoring IAM role"
  iam_role_path                 = "/autoscaling/"
  iam_role_max_session_duration = 7200

  apply_immediately   = true
  skip_final_snapshot = true

  db_parameter_group_name         = "aurora-postgresl11"
  db_cluster_parameter_group_name = aws_rds_cluster_parameter_group.aurora_eu_central_1.name  
  enabled_cloudwatch_logs_exports = ["postgresql"]

  tags = {
    Owner       = "company"
    Environment = "production"
  }
}

# Proxy for Aurora

resource "aws_iam_role" "iam_proxy_eu_central_1" {
  name = "iam_proxy_eu_central_1"

  assume_role_policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Principal": {
        "Service": "rds.amazonaws.com"
      },
      "Effect": "Allow",
      "Sid": ""
    }
  ]
}
EOF
}

resource "aws_iam_policy" "proxy_eu_central_1" {
  name        = "proxy-eu-central-1"
  path        = "/"
  description = "IAM policy for logging into the aurora db"

  policy = <<EOF
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Action": [
                "secretsmanager:GetResourcePolicy",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
      ],
      "Resource": "arn:aws:secretsmanager:eu-central-1:[account]:secret:[company]/aurora-Pa40We",
      "Effect": "Allow"
    }
  ]
}
EOF
}

resource "aws_db_proxy" "proxy_eu_central_1" {
  name                   = "proxy-eu-central-1"
  debug_logging          = true
  engine_family          = "POSTGRESQL"
  idle_client_timeout    = 1800
  require_tls            = false
  role_arn               = aws_iam_role.iam_proxy_eu_central_1.arn
  vpc_security_group_ids = [aws_security_group.rds.id]
  vpc_subnet_ids         = module.link_delivery_eu_central_1.private_subnets_ids

  auth {
    auth_scheme = "SECRETS"
    description = "allows the connection to the aurora db"
    iam_auth    = "DISABLED"
    secret_arn  = "arn:aws:secretsmanager:eu-central-1:[account]:secret:[company]/aurora-Pa40We"
  }

  tags = {
    Name = "aurora proxy"
  }
}

resource "aws_db_proxy_default_target_group" "proxy_eu_central_1" {
  db_proxy_name = aws_db_proxy.proxy_eu_central_1.name

  connection_pool_config {
    connection_borrow_timeout    = 120
    init_query                   = "SET x=1, y=2"
    max_connections_percent      = 100
    max_idle_connections_percent = 50
    session_pinning_filters      = ["EXCLUDE_VARIABLE_SETS"]
  }
}

resource "aws_db_proxy_target" "proxy_eu_central_1" {
  db_cluster_identifier  = module.aurora.rds_cluster_id
  db_proxy_name          = aws_db_proxy.proxy_eu_central_1.name
  target_group_name      = aws_db_proxy_default_target_group.proxy_eu_central_1.name
}

Answer 1

Your aws_iam_policy policy has no effect. You forgot to associate it with the role:

resource "aws_iam_policy_attachment" "test-attach" {
  name       = "role-proxy-attachment"
  roles      = [aws_iam_role.proxy_eu_central_1.name]
  policy_arn = aws_iam_policy.proxy_eu_central_1.arn
}

Answer 2

I added the following Trust relationship and altered the Role (proxy) permissions automatically assigned to the Lambda Function's Role when attaching the RDS proxy in Lambda Function /Configuration/ Database proxies. The question doesn't specifically mention Terraform -- this isn't an IoC solution.

Trust relationship
AWS Tutorial

 {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
          "Service": "rds.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
  }

AWSLambdaRDSProxyExecutionRole
The following policy uses the "*" character to match all DB instances and database accounts for a particular AWS account and AWS Region.

{
"Version": "2012-10-17",
"Statement": [
    {
        "Effect": "Allow",
        "Action": "rds-db:connect",
        "Resource": "arn:aws:rds-db:us-east-2:<account_number>:dbuser:*/*"
    }
]

}