Guide to using AWS RDS public endpoint

Introduction

In the previous article, I introduced the basic concepts of AWS RDS as well as how to securely connect using a tunnel; you can review it to grasp the necessary information before proceeding.

In this article, I will guide you through creating an RDS Postgres with public access, meaning this database can be accessed from any computer. This method may be considered less secure than the previous one, but it is useful when you need to share database connections with many users during the development process who do not have an AWS account to connect via a tunnel.

Detailed Instructions

Using AWS CDK, create the file lib/rds-public-stack.ts

import * as cdk from "aws-cdk-lib"
import * as ec2 from "aws-cdk-lib/aws-ec2"
import * as rds from "aws-cdk-lib/aws-rds"
import * as secretsmanager from "aws-cdk-lib/aws-secretsmanager"
import * as ssm from "aws-cdk-lib/aws-ssm"

export class RdsPublicStack extends cdk.Stack {
  constructor(scope: cdk.App, id: string, props?: cdk.StackProps) {
    super(scope, id, props)

    const dbConfig = {
      databaseName: "my_custom_db",
      dbPort: 5432,
      schema: "app_schema",
    }

    const vpc = new ec2.Vpc(this, "MyVpc", {
      maxAzs: 2,
      natGateways: 0,
      subnetConfiguration: [
        {
          name: "Public",
          subnetType: ec2.SubnetType.PUBLIC,
        },
      ],
    })

    const dbSecret = new secretsmanager.Secret(this, "DbSecret", {
      secretName: "my-postgres-credentials-public",
      generateSecretString: {
        secretStringTemplate: JSON.stringify({
          username: "postgres",
          dbname: dbConfig.databaseName,
          port: dbConfig.dbPort,
          schema: dbConfig.schema,
        }),
        generateStringKey: "password",
        excludeCharacters: '"@/\\',
      },
    })

     dbSecret.addRotationSchedule("RotationSchedule", {
      hostedRotation: secretsmanager.HostedRotation.postgreSqlSingleUser(),
      automaticallyAfter: cdk.Duration.days(30),
    })

    const dbInstance = new rds.DatabaseInstance(this, "MyDb", {
      engine: rds.DatabaseInstanceEngine.postgres({
        version: rds.PostgresEngineVersion.VER_15,
      }),
      instanceType: ec2.InstanceType.of(
        ec2.InstanceClass.T3,
        ec2.InstanceSize.MICRO,
      ),
      vpc,
      vpcSubnets: { subnetType: ec2.SubnetType.PUBLIC },
      publiclyAccessible: true,
      credentials: rds.Credentials.fromSecret(dbSecret),
      allocatedStorage: 20,
      port: dbConfig.dbPort,
      databaseName: dbConfig.databaseName,
      removalPolicy: cdk.RemovalPolicy.DESTROY,
    })

    dbInstance.connections.allowFrom(
      ec2.Peer.anyIpv4(),
      ec2.Port.tcp(dbConfig.dbPort),
      "Allow public access to Postgres",
    )

    new ssm.StringParameter(this, "DbHostParam", {
      parameterName: "/my-app/db-host",
      stringValue: dbInstance.dbInstanceEndpointAddress,
    })
    new cdk.CfnOutput(this, "DbEndpoint", {
      value: dbInstance.dbInstanceEndpointAddress,
      description: "Use this endpoint to connect from local",
    })
    new cdk.CfnOutput(this, "DbPort", { value: dbConfig.dbPort.toString() })
  }
}

dbConfig: database information, port, and schema can be changed according to your needs.
ec2.Vpc has a subnetType of ec2.SubnetType.PUBLIC to allow external access, so we no longer need to create a bastion to connect via a tunnel.
Here, I use ec2.Peer.anyIpv4() as an example; if your internet or organization uses IP version 6, please replace it with ec2.Peer.anyIpv6(), but the best practice is to limit the accessible IP addresses as follows: ec2.Peer.ipv6("<ip address>").

Next, update the file bin/aws-cdk.ts

#!/usr/bin/env node
import * as cdk from "aws-cdk-lib/core"
import { RdsPublicStack } from "../lib/rds/rds-public-stack"

const app = new cdk.App()
new RdsPublicStack(app, "RdsPublicStack")

After successful deployment, the result will be as follows:

✅  RdsPublicStack
✨  Deployment time: 593.98s

Outputs:
RdsPublicStack.DbEndpoint = rdspublicstack-mydb3c7179b2-0h8kuolhqxrr.clk0e4sk6z9o.ap-southeast-1.rds.amazonaws.com        
RdsPublicStack.DbPort = 5432

✨  Total time: 635.95s

Resources on the AWS console have been successfully created.

Next, in the NestJS project, update the file database.service.ts as follows:

import {
  GetSecretValueCommand,
  SecretsManagerClient,
} from '@aws-sdk/client-secrets-manager'
import {GetParameterCommand, SSMClient} from '@aws-sdk/client-ssm'
import {Injectable, Logger} from '@nestjs/common'
import {TypeOrmModuleOptions} from '@nestjs/typeorm'
import * as fs from 'fs'
import * as path from 'path'

@Injectable()
export class DatabaseService {
  private readonly secretClient = new SecretsManagerClient()
  private readonly ssmClient = new SSMClient()
  private readonly logger = new Logger(DatabaseService.name)

  async getFreshConfig(): Promise<TypeOrmModuleOptions> {
    this.logger.log('Fetching fresh credentials from Secrets Manager...')

    const command = new GetSecretValueCommand({
      SecretId: 'my-postgres-credentials-public',
    })
    const secretRes = await this.secretClient.send(command)
    const secrets = JSON.parse(secretRes.SecretString || '{}')

    const parameterCommand = new GetParameterCommand({
      Name: '/my-app/db-host',
      WithDecryption: true,
    })
    const parameterRes = await this.ssmClient.send(parameterCommand)
    const host = parameterRes.Parameter?.Value

    return {
      type: 'postgres',
      host,
      port: secrets.port,
      username: secrets.username,
      password: secrets.password,
      schema: secrets.schema,
      database: secrets.database,
      autoLoadEntities: true,
      synchronize: true,
      logging: ['error', 'warn'],
      ssl: {
        ca: fs
          .readFileSync(path.join(__dirname, '../assets/global-bundle.pem'))
          .toString(),
        rejectUnauthorized: true,
        checkServerIdentity: () => undefined,
      },
    } as TypeOrmModuleOptions
  }
}

There are not many changes here, only the additional use of GetParameterCommand to retrieve the endpoint for the host (instead of connecting to localhost as in the previous example).

Next is the file database.module.ts

import {Global, Module} from '@nestjs/common'
import {TypeOrmModule} from '@nestjs/typeorm'
import {DataSource} from 'typeorm'
import {DatabaseService} from './database.service'
import {TestEntity} from 'src/entity/test.entity'
import {TestEntityService} from 'src/service/test-entity.service'

@Global()
@Module({
  providers: [DatabaseService, TestEntityService],
  imports: [
    TypeOrmModule.forRootAsync({
      inject: [DatabaseService],
      dataSourceFactory: async options => {
        if (!options) throw new Error('TypeORM options are missing')

        const pgOptions = options as any
        const targetSchema = pgOptions.schema

        if (targetSchema && targetSchema !== 'public') {
          const bootstrapDataSource = new DataSource({
            ...options,
            schema: 'public',
            synchronize: false,
            migrationsRun: false,
            logging: false,
          } as any)

          try {
            await bootstrapDataSource.initialize()
            await bootstrapDataSource.query(
              `CREATE SCHEMA IF NOT EXISTS "${targetSchema}"`
            )
            await bootstrapDataSource.destroy()
          } catch (error) {
            console.error(
              `[Database] Failed to create schema: ${error.message}`
            )
            if (bootstrapDataSource.isInitialized)
              await bootstrapDataSource.destroy()
          }
        }

        const dataSource = await new DataSource(options).initialize()
        const originalQuery = dataSource.query.bind(dataSource)
        dataSource.query = async (query, parameters, queryRunner) => {
          try {
            return await originalQuery(query, parameters, queryRunner)
          } catch (error) {
            if (error.code === '28P01') {
              try {
                await dataSource.destroy()
                const dbService = new DatabaseService()
                const newConfig = await dbService.getFreshConfig()

                Object.assign(dataSource.options, newConfig)
                await dataSource.initialize()
                return await dataSource.query(query, parameters, queryRunner)
              } catch (retryError) {
                console.error(
                  '[Database] Critical: Failed to refresh connection.',
                  retryError
                )
                throw retryError
              }
            }
            throw error
          }
        }
        return dataSource
      },
      useFactory: async (configService: DatabaseService) => {
        return await configService.getFreshConfig()
      },
    }),
    TypeOrmModule.forFeature([TestEntity]),
  ],
  exports: [DatabaseService, TestEntityService],
})
export class DatabaseModule {}

The main change here is in the dataSourceFactory, because the schema name created in the CDK has changed (using app_schema instead of the default public), so before creating the connection, you must check whether this schema exists to initialize the schema first.
The remaining part is also to check for the '28P01' error when the password is invalid, as I mentioned in the previous article.

Then, try starting the project and you will see the corresponding new schema being created.

2025-12-17T04:45:53.055Z  info [NestFactory] Starting Nest application... +0ms
2025-12-17T04:45:53.072Z  info [DatabaseService] Fetching fresh credentials from Secrets Manager... +17ms
2025-12-17T04:45:53.076Z  info [InstanceLoader] TypeOrmModule dependencies initialized +4ms
2025-12-17T04:45:53.077Z  info [InstanceLoader] ConfigHostModule dependencies initialized +1ms
2025-12-17T04:45:53.082Z  info [InstanceLoader] ConfigModule dependencies initialized +5ms
[Database] Ensuring schema "app_schema" exists...
[Database] Schema "app_schema" is ready.
[Database] Main DataSource initialized successfully.
2025-12-17T04:45:55.219Z  info [InstanceLoader] TypeOrmCoreModule dependencies initialized +2s
2025-12-17T04:45:55.220Z  info [InstanceLoader] TypeOrmModule dependencies initialized +1ms