Question 18863 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

How to create Postgres DB with Public access using AWS CloudFormation – Amazon Web Sevices

for testing purposes, I need to create Postgres Database which will have Public access and will be available from anywhere. My current CloudFormation looks like this:

---
AWSTemplateFormatVersion: "2010-09-09"
Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: 10.0.0.0/16
      EnableDnsSupport: true
      EnableDnsHostnames: true

  InternetGateway:
    Type: AWS::EC2::InternetGateway

  VPCGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref VPC
      InternetGatewayId: !Ref InternetGateway

  PublicSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.1.0/24
      MapPublicIpOnLaunch: true
      AvailabilityZone: eu-central-1a

  PublicSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.2.0/24
      MapPublicIpOnLaunch: true
      AvailabilityZone: eu-central-1b

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnets for RDS database
      SubnetIds:
        - !Ref PublicSubnet1
        - !Ref PublicSubnet2

  SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow all inbound traffic
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          CidrIp: 0.0.0.0/0

  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBInstanceIdentifier: ourpostgres
      DBName: "database"
      AllocatedStorage: "5"
      DBInstanceClass: db.t3.micro
      Engine: postgres
      VPCSecurityGroups:
        - !Ref SecurityGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      PubliclyAccessible: true
      MasterUsername: myusername
      MasterUserPassword: mypassword

Outputs:
  DBInstanceEndpoint:
    Description: Endpoint to access the Postgres database
    Value: !GetAtt [DBInstance, Endpoint.Address]

After running this CloudFormation database instance starts successfully but I am still unable to login into the database from a local machine (using Sequel Pro as the viewer).
I tried already set up VPC, Security Groups, Gateway and Subnets but it still seems like I am missing something.
enter image description here

Can you help me to identify the issues in CloudFormation above?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    With help of the community, I was able to make this running. Here is the the complete solution

    ---
AWSTemplateFormatVersion: "2010-09-09"
Resources:
  VPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: 10.0.0.0/16
      EnableDnsSupport: true
      EnableDnsHostnames: true

  InternetGateway:
    Type: AWS::EC2::InternetGateway

  VPCGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      VpcId: !Ref VPC
      InternetGatewayId: !Ref InternetGateway

  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
        - Key: Name
          Value: Public

  PublicRoute1:
    Type: AWS::EC2::Route
    DependsOn: InternetGateway
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

  PublicSubnet1:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.1.0/24
      MapPublicIpOnLaunch: true
      AvailabilityZone: eu-central-1a

  PublicSubnet2:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref VPC
      CidrBlock: 10.0.2.0/24
      MapPublicIpOnLaunch: true
      AvailabilityZone: eu-central-1b

  Subnet1Association:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId:
        Ref: PublicRouteTable
      SubnetId:
        Ref: PublicSubnet1

  Subnet2Association:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId:
        Ref: PublicRouteTable
      SubnetId:
        Ref: PublicSubnet2

  DBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: Subnets for RDS database
      SubnetIds:
        - !Ref PublicSubnet1
        - !Ref PublicSubnet2

  SecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow all inbound traffic
      VpcId: !Ref VPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 5432
          ToPort: 5432
          CidrIp: 0.0.0.0/0

  DBInstance:
    Type: AWS::RDS::DBInstance
    Properties:
      DBInstanceIdentifier: ourpostgres
      DBName: "dividends"
      AllocatedStorage: "5"
      DBInstanceClass: db.t3.micro
      Engine: postgres
      VPCSecurityGroups:
        - !Ref SecurityGroup
      DBSubnetGroupName: !Ref DBSubnetGroup
      PubliclyAccessible: true
      MasterUsername: myusername
      MasterUserPassword: mypassword

Outputs:
  DBInstanceEndpoint:
    Description: Endpoint to access the Postgres database
    Value: !GetAtt [DBInstance, Endpoint.Address]

    Also do not use SequelPro for connecting to Postgres Database see https://stackoverflow.com/a/60947061/14027842


  2. 0

    The reason for your issue is that your subnets are private.

    While the CloudFormation template does create an Internet Gateway and attaches it to the VPC, the two subnets are using the default Route Table, which does not contain a route to the Internet Gateway. Therefore, the subnets are effectively private subnets.

    VPC Route Table

    You would need to update the template to:

    • Create a Route Table
    • Route traffic for 0.0.0.0/0 to the Internet Gateway
    • Associate the Route Table to both of the subnets

    As an example, here’s some code that I grabbed from Building a VPC with CloudFormation – Part 1:

      # Some route tables for our subnets:
  PublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref VPC
      Tags:
      - Key: Name
        Value: Public
  PublicRoute1:   # Public route table has direct routing to IGW:
    Type: AWS::EC2::Route
    DependsOn: AttachGateway
    Properties:
      RouteTableId: !Ref PublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref InternetGateway

    You would need something similar to that.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search