Amazon web services – How to access RDS PostgreSQL database from AWS Lambda with AWS CDK?

I’m trying to build a stack using AWS CDK, which includes an RDS PostgreSQL instance and a Lambda function. From the Lambda, I’m trying to access the PostgreSQL, but I’m not succeeding in any way. My problem is that no matter what I do, I always get this error:

FATAL: no pg_hba.conf entry for host "IP", user "postgres",
database "codeSageVector", no encryption

Could someone help me figure out where the error is? I think it’s related to the security groups, but I have no clue. I’m starting to go crazy.

This is my Stack:

import * as cdk from 'aws-cdk-lib';
import {
  aws_cloudfront as cloudfront,
  aws_dynamodb as dynamodb,
  aws_ec2 as ec2,
  aws_events as events,
  aws_events_targets as targets,
  aws_iam as iam,
  aws_lambda as lambda,
  aws_lambda_destinations,
  aws_rds as rds,
  aws_s3 as s3,
  aws_secretsmanager as secretsmanager,
  Duration
} from 'aws-cdk-lib';
import { Construct } from 'constructs';
import { AccountRootPrincipal, AnyPrincipal, Effect, PolicyStatement } from 'aws-cdk-lib/aws-iam';
import { BlockPublicAccess, BucketAccessControl, ObjectOwnership } from 'aws-cdk-lib/aws-s3';
import * as path from 'path';
import * as cognito from 'aws-cdk-lib/aws-cognito';
import * as appsync from 'aws-cdk-lib/aws-appsync';
import { CustomStackProps } from '../types/custom-stack-props.type';
import { OriginAccessIdentity } from 'aws-cdk-lib/aws-cloudfront';
import { Peer, Port } from 'aws-cdk-lib/aws-ec2';

export class CodeSageStack extends cdk.Stack {
  constructor(scope: Construct, id: string, props?: cdk.StackProps, customStackProps?: CustomStackProps) {
    super(scope, id, props);

    /**
     * Postgres
     */
    const postgresDatabaseName = 'codeSageVector';

    // Create a VPC with 2 availability zones
    const vpc = new ec2.Vpc(this, 'Vpc', {
      maxAzs: 2
    });

    // Create a security group for the database
    const postgresSecurityGroup = new ec2.SecurityGroup(this, 'postgresSecurityGroup', {
      vpc,
      allowAllOutbound: true
    });

    // Allow inbound traffic on port 5432 (PostgreSQL) from the VPC CIDR range
    postgresSecurityGroup.addIngressRule(Peer.anyIpv4(), Port.tcp(5432));

    // Create a secret for the database credentials
    const postgresDbSecret = new secretsmanager.Secret(this, 'postgresDbSecret', {
      generateSecretString: {
        secretStringTemplate: JSON.stringify({
          username: 'postgres'
        }),
        generateStringKey: 'password',
        excludePunctuation: true,
        includeSpace: false
      }
    });

    // Create a PostgreSQL database instance
    const postgresDb = new rds.DatabaseInstance(this, 'postgresDb', {
      engine: rds.DatabaseInstanceEngine.postgres({
        version: rds.PostgresEngineVersion.VER_15_2
      }),
      databaseName: postgresDatabaseName,
      instanceType: ec2.InstanceType.of(
        ec2.InstanceClass.BURSTABLE3,
        ec2.InstanceSize.SMALL
      ),
      vpc: vpc,
      vpcSubnets: {
        subnetType: ec2.SubnetType.PUBLIC
      },
      multiAz: false,
      securityGroups: [postgresSecurityGroup],
      credentials: rds.Credentials.fromSecret(postgresDbSecret),
      allocatedStorage: 20,
      storageType: rds.StorageType.GP2,
      deletionProtection: false
    });

    /**
     * CodeBase Bucket
     */
    const codeBaseBucket = new s3.Bucket(this, 'code-base', {
      removalPolicy: cdk.RemovalPolicy.DESTROY,
      autoDeleteObjects: true,
      eventBridgeEnabled: true,
      cors: [
        {
          allowedHeaders: ['*'],
          allowedMethods: [s3.HttpMethods.PUT],
          allowedOrigins: [...(customStackProps?.s3?.codeBaseBucket?.allowedOrigins || []), `https://${distribution.distributionDomainName}`]
        }
      ]
    });

    /**
     * Lambda Layers
     */

    const ulidLayer3_9 = new cdk.aws_lambda.LayerVersion(this, 'codeSageUlid3_9', {
      layerVersionName: 'CodeSageUlid3_9',
      code: cdk.aws_lambda.Code.fromAsset(path.join(__dirname, `../lambda/layers/ulid/`)),
      description: 'A ULID is a universally unique lexicographically sortable identifier',
      compatibleRuntimes: [cdk.aws_lambda.Runtime.PYTHON_3_9],
      removalPolicy: cdk.RemovalPolicy.DESTROY
    });

    const psycopg2Layer = new cdk.aws_lambda.LayerVersion(this, 'codeSagePsycopg2Layer', {
      layerVersionName: 'codeSagePsycopg2Layer',
      code: cdk.aws_lambda.Code.fromAsset(path.join(__dirname, `../lambda/layers/psycopg2/`)),
      description: 'Psycopg is the most popular PostgreSQL database adapter for the Python programming language',
      compatibleRuntimes: [cdk.aws_lambda.Runtime.PYTHON_3_9],
      removalPolicy: cdk.RemovalPolicy.DESTROY
    });

    /**
     * Lambda Functions
     */

    // Defines the CreateProject AWS Lambda resource
    const createProjectFunctionSG = new ec2.SecurityGroup(this, 'createProjectFunctionSG', {
      vpc
    });

    const createProjectFunction = new lambda.Function(this, 'CreateProject', {
      runtime: lambda.Runtime.PYTHON_3_9,
      code: cdk.aws_lambda.Code.fromAsset(path.join(__dirname, `../lambda/functions/create-project/`)),
      handler: 'app.lambda_handler',
      layers: [psycopg2Layer, ulidLayer3_9],
      environment: {
        APP_TABLE_NAME: appDynamoDbTable.tableName,
        S3_CODE_BASE_BUCKET: codeBaseBucket.bucketName,
        REGION: cdk.Stack.of(this).region,
        POSTGRES_DB_ENDPOINT: postgresDb.dbInstanceEndpointAddress,
        POSTGRES_DB_NAME: postgresDatabaseName,
        POSTGRES_SECRET_ARN: postgresDb.secret?.secretFullArn || ''
      }
    });
    postgresDb.connections.allowDefaultPortFrom(createProjectFunctionSG);
    postgresDb.secret?.grantRead(createProjectFunction);

    codeBaseBucket.grantReadWrite(createProjectFunction);

   
  }
}

And this is my Lambda:

import os
import boto3
import logging
from datetime import datetime
import psycopg2
import psycopg2.extras
import psycopg2.sql
import json

region= os.environ['REGION']
postgres_db_endpoint = os.environ['POSTGRES_DB_ENDPOINT']
postgres_db_name = os.environ['POSTGRES_DB_NAME']
postgres_secret_arn = os.environ['POSTGRES_SECRET_ARN']

def lambda_handler(event, context):
    secretManager = boto3.client('secretsmanager', region_name=region)

    secretParams = {
       'SecretId': postgres_secret_arn
    }

    dbSecret = secretManager.get_secret_value(**secretParams)
    secretString = dbSecret['SecretString']

    if not secretString:
       raise Exception('Secret string is empty')

    secretData = json.loads(secretString)
    password = secretData['password']

    conn = psycopg2.connect(
        user='postgres',
        host=postgres_db_endpoint,
        database=postgres_db_name,
        password=password,
        port=5432
    )

    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    cursor.execute(psycopg2.sql.SQL('SELECT * FROM users'))
    result = cursor.fetchone()
    print(result['message'])
    cursor.close()
    conn.close()

    return {
        
    }

<!-- end snippet -->