how to connect an aws api gateway to a private lambda function inside a vpc

Question

I am trying to connect an aws api gateway to a lambda function residing in a VPC then retrieve the secret manager to access a database using python code with boto3. The database and vpc endpoint were created in a private subnet.

lambda function

def test_secret():

    secret = "mysecret"
    region = "MY-REGION" :)
    
    session = boto3.session.Session()
    client = session.client(
        service_name="secretsmanager",
        region_name=region
    )
    secret_value_response = client.get_secret_value(SecretId=secret)

    try:
        result =  json.loads(secret_value_response["SecretString"])

    except Exception as e:
        result = "Error found: {}".format(e)
    return result


def handler(event, context):

    get_secrets = test_secret() # THE CODE FAIL HERE IN CLOUDWATCH

    try:
        some_string = event["queryStringParameters"]["some_string"]

        response = {}

        response["statusCode"] = 200
        response["body"] = some_string + " " + get_secrets["name"]

        print("secrets: ", some_string + " " + get_secrets["name"])

    except Exception as e:
        response = "Error: {}".format(e)

    return response

TERRAFORM

security group

resource "aws_security_group" "db" {
  name   = "db"
  vpc_id = aws_vpc.default.id

  ingress {
    from_port   = 443
    to_port     = 443
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }

  ingress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

lambda

resource "aws_lambda_function" "lambda_test" {
  function_name       = "lambda-test"
  ...

  # Attach Lambda to VPC
  vpc_config {
    subnet_ids = [aws_subnet.private_subnet.id]
    security_group_ids = [aws_security_group.db.id]
  }
}

resource "aws_iam_policy" "lambda_test" {
  name        = "lambda-test"

  policy = <<EOF
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:CreateLogGroup",
                "logs:PutLogEvents",
                "ec2:DescribeSecurityGroups",
                "ec2:DescribeSubnets",
                "ec2:DescribeVpcs",
                "ec2:DescribeNetworkInterfaces",
                "ec2:CreateNetworkInterface",
                "ec2:DeleteNetworkInterface",
                "ec2:AttachNetworkInterface",
                "ec2:AssignPrivateIpAddresses",
                "ec2:UnassignPrivateIpAddresses",
                "autoscaling:CompleteLifecycleAction",
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
              "arn:aws:lambda:::${aws_lambda_function.lambda_test.arn}",
              "arn:aws:lambda:::${aws_lambda_function.lambda_test.arn}/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "secretsmanager:GetSecretValue",
            "Resource": [
              "arn:aws:lambda:::${data.aws_secretsmanager_secret.my_secret.arn}",
              "arn:aws:lambda:::${data.aws_secretsmanager_secret.my_secret.arn}/*"
            ]
        }
    ]
}
EOF
}

resource "aws_iam_role" "lambda_test_role" {
  name = "lambda-test-role"

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

resource "aws_iam_role_policy_attachment" "lambda_test" {
  policy_arn  = aws_iam_policy.lambda_test.arn
  role        = aws_iam_role.lambda_test_role.name
}

resource "aws_iam_role_policy_attachment" "lambda_test_vpc_access" {
  policy_arn = "arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole"
  role        = aws_iam_role.lambda_test_role.name
}

vpc endpoint

resource "aws_vpc_endpoint" "vpc_endpoint" {
  vpc_id                      = aws_vpc.default.id
  service_name                = "com.amazonaws.${var.AWS_REGION}.secretsmanager"
  vpc_endpoint_type           = "Interface"

  security_group_ids          = [aws_security_group.db.id]
  private_dns_enabled         = true

  policy                      = <<EOF
{
    "Version": "2012-10-17",
    "Statement": [
        {
          "Effect": "Allow",
          "Action": "*",
          "Principal": "*",
          "Resource": "*"
        }
    ]
}
EOF
}

Without trying to access secretsmanager, the lambda itself work fine, i am able to access the url endpoint, provide parameters then see the result in cloudwatch logs but as soon as i try to call secretsmanager in the lambda function endpoint, the page return {"message": "Internal server error"} and when i look at the logs it say {"errorMessage": "Could not connect to the endpoint URL: \"https://secretsmanager.REGIONHIDDEN.amazonaws.com/\"", "errorType": "EndpointConnectionError"

Is there anything that i am doing wrong above?