I can't connect mysql client to RDS through VPN

Question

I've been really struggling to use AWS RDS. All networking configuration things is a really pain in the back since I have no skills in networking and I don't like it either.

My goal is to create my mysql DB on RDS be able to connect to it through any mysql client, run my SQL script to create the DB and execute my lambdas to insert data to this DB.

So,

mysql client --> RDS (mysql) <-- lambdas

They all need to connect to each other.

After many weeks of research trying to understand all networking things around AWS, copying examples from one place and another.

I've got the following scenario:

I have a VPC, public and private subnets, security groups, EIPs, RDS and VPN all in my cloud formation template.

I can deploy everything ok, all seems to be working.

I can connect to my VPN and ping the private IP of my EIP.

But still I can't connect my mysql client to my RDS. So, I can't run my SQL script and I can't test my lambdas to see if they are really connecting to my RDS.

This is part of my configuration that I'm guessing could be related with the problem but as you can imagine, my lack of networking knowledge is making it harder.

The only thing that comes to my mind is that VPN and RDS are not part of the same subnets.

Full configuration: https://gist.github.com/serraventura/ec17d9a09c706e7ace1fd3e3be9972aa

RouteTableDB is always only connecting to private subnets while VPN (ec2) only connects to public subnet.

  SubnetRouteTableAssociationPrivateDB1:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId:
        Ref: RouteTableDB
      SubnetId:
        Ref: SubnetDBPrivate1

  SubnetRouteTableAssociationPrivateDB2:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId:
        Ref: RouteTableDB
      SubnetId:
        Ref: SubnetDBPrivate2

  SubnetRouteTableAssociationPrivate1:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId:
        Ref: RouteTableDB
      SubnetId:
        Ref: SubnetPrivate1

  SubnetRouteTableAssociationPrivate2:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref RouteTableDB
      SubnetId: !Ref SubnetPrivate2

RDS, VPN

RDSMySQL:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: ${self:custom.infra.allocatedStorage}
      DBInstanceClass: ${self:custom.infra.dbInstanceClass}
      Engine: ${self:custom.infra.engine}
      DBInstanceIdentifier: ${self:custom.app.dbName}
      DBName: ${self:custom.app.dbName}
      MasterUsername: ${self:custom.app.dbUser}
      MasterUserPassword: ${self:custom.app.dbPass}
      DBSubnetGroupName:
        Ref: myDBSubnetGroup
      MultiAZ: ${self:custom.infra.multiAZ}
      PubliclyAccessible: true
      StorageType: gp2
      VPCSecurityGroups:
        - Ref: RDSSecurityGroup
    DeletionPolicy: Delete

  VPNEIP:
    Type: AWS::EC2::EIP
    Properties:
      InstanceId:
        Ref: VPNEC2Machine
      Domain: vpc

  VPNEC2Machine:
    Type: AWS::EC2::Instance
    Properties:
      KeyName: ${self:custom.infra.ec2KeyPairName.${self:provider.region}}
      ImageId: ${self:custom.infra.openVPNAMI.${self:provider.region}}
      InstanceType: ${self:custom.infra.instanceType}
      AvailabilityZone: ${self:provider.region}a
      Monitoring: true
      SecurityGroupIds:
        - Ref: VPNSecurityGroup
      SubnetId:
        Ref: SubnetPublic1
      Tags:
        - Key: Name
          Value: ${self:custom.companyName} OpenVPN ${self:provider.stage}

  VPNRouteRecordSet:
    Type: AWS::Route53::RecordSet
    DependsOn:
      - VPNEC2Machine
      - VPNEIP
    Properties:
      HostedZoneName: ${self:custom.domains.base}.
      Comment: Record for the VPN subdomain
      Name: vpn-${self:provider.stage}.${self:custom.domains.base}.
      Type: A
      TTL: 60
      ResourceRecords:
        - Ref: VPNEIP

  VPNSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow Access From machines to the VPN and Private Network
      VpcId:
        Ref: VPCStaticIP
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: ${self:custom.app.dbPort}
          ToPort: ${self:custom.app.dbPort}
          CidrIp: 0.0.0.0/0
          Description: 'Postgres Port'
        - IpProtocol: tcp
          FromPort: 22
          ToPort: 22
          CidrIp: 0.0.0.0/0
          Description: 'SSH Port'
        - IpProtocol: udp
          FromPort: 1194
          ToPort: 1194
          CidrIp: 0.0.0.0/0
          Description: 'OpenVPN Server Access Port'
        - IpProtocol: tcp
          FromPort: 443
          ToPort: 443
          CidrIp: 0.0.0.0/0
          Description: 'OpenVPN HTTPS Admin Port'
        - IpProtocol: tcp
          FromPort: 943
          ToPort: 943
          CidrIp: 0.0.0.0/0
          Description: 'OpenVPN Server Port'
      Tags:
        - Key: Name
          Value: ${self:custom.companyName} VPN SG ${self:provider.stage}

Sorry you’re struggling. We’ve all been there. I’m sure the community will be able to help you through it, though! I’m on mobile right now and didn’t go too deep to test it but from a quick glance it looks like you haven’t allowed connections to your RDS instance from your VPN security group. In your RDS security group add another inbound block that allowing inbound on 3306 from your VPN security group; that should allow you to connect to RDS from your mysql client when connected to your VPN. — hephalump, Aug 31 '19 at 12:40
Ok. Let me go through the configuration again. Do you think I need two EIP? — Thiago C. S Ventura, Aug 31 '19 at 14:39
No, you should not need a second EIP. See my answer; I haven't tested it but that should resolve it. Once you add that Ingress rule, connect to your VPN and you should then be able to connect to the RDS instance. — hephalump, Aug 31 '19 at 14:44

hephalump · Accepted Answer · 2019-09-01T20:29:59.843

3

Your RDS instance is accepting inbound connections on 3306 from the LambdaSecurityGroup, which is fine for anything with the LambdaSecurityGroup SG attached to it, but you also need to allow connections from your VPNSecurityGroup.

Change your RDSSecurityGroupBlock to look as follows and that should allow you to connect to RDS from your VPN:

  RDSSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: Allow My SQL access from lambda subnets
      VpcId:
        Ref: VPCStaticIP
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: '3306'
          ToPort: '3306'
          SourceSecurityGroupId:
            Ref: LambdaSecurityGroup
        - IpProtocol: tcp
          FromPort: '3306'
          ToPort: '3306'
          SourceSecurityGroupId:
            Ref: VPNSecurityGroup
      Tags:
        - Key: Name
          Value: RDSSecurityGroup

As a side note, the VPNSecurityGroup is accepting connections from anywhere for 3306, 22, 1194, 443, 943. This may be intentional but given that these are exposed for management purposes it would not be best practice. You should give serious consideration to scoping the CidrIp's for those ports to trusted CidrIp sources to avoid any potential unwanted exposures. You may also with to consider removing the 3306 block from there, all together, as it would seem to be unnecessary to have that port open on the VPN itself.

EDIT As per the OP’s comments, in addition to the above, you also need to change PubliclyAccessible to False to resolve the issue.

edited Sep 01 '19 at 20:29

answered Aug 31 '19 at 14:40

hephalump

5,860
1
22
23

Ok. I will test now. I believe in your example you meant to use VPNSecurityGroup instead of VPCSecurityGroup. right? – Thiago C. S Ventura Sep 01 '19 at 15:36
Just tested it. Still same problem. Let me have a look around the config. – Thiago C. S Ventura Sep 01 '19 at 16:10
What is the error you are receiving? What client are you using? – hephalump Sep 01 '19 at 16:20
I'm getting timeout. I'm using sequel Pro client and also a command line: nc -zv dbname.chwrerhruweyruiy.us-east-1.rds.amazonaws.com 3306 both getting timeout. – Thiago C. S Ventura Sep 01 '19 at 17:50
I've just done a new test changing ```PubliclyAccessible: false``` and now I can connect. I did this change along with your suggestion. Now I will test if my lambdas can connect to the DB. – Thiago C. S Ventura Sep 01 '19 at 20:09
Another thing I want to do is to remove one of the EIP without breaking stuff. – Thiago C. S Ventura Sep 01 '19 at 20:10
Great that it is resolved! I’ve updated my answers to reflect your comment about changing PubliclyAccessible to False. – hephalump Sep 01 '19 at 20:31
I think now the only problem left is that I can't connect my lambda to RDS. I'm getting timeout. Should it be part of this question or better to close it? My question seems to be more focussed on mysql client. – Thiago C. S Ventura Sep 03 '19 at 09:02

score 0 · Answer 2 · answered Sep 04 '19 at 10:13

I would like to give a full answer to the question since the title implies a problem just with mysql client not able to connect RDS.

Along with @hephalump changes I had to do more two changes to also enable my lambdas to connect to RDS and now I'm able to connect mysql client and also lambdas.

I had to create a new IAM Role for my lambdas

  LambdaRole:
    Type: AWS::IAM::Role
    Properties:
      Path: '/'
      RoleName: LambdaRole
      AssumeRolePolicyDocument:
        Version: '2012-10-17'
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - lambda.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: ec2LambdaPolicies
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: Allow
                Action:
                  - ec2:CreateNetworkInterface
                  - ec2:DescribeNetworkInterfaces
                  - ec2:DetachNetworkInterface
                  - ec2:DeleteNetworkInterface
                Resource: "*"
        - PolicyName: 'AllowInvoke'
          PolicyDocument:
            Version: '2012-10-17'
            Statement:
              - Effect: 'Allow'
                Action: 'lambda:InvokeFunction'
                Resource: '*'
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
        - arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole

The important bit here to solve the problem seems to be this:

      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSLambdaBasicExecutionRole
        - arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole

Then I had to remove my iamRoleStatements from my provider and add the new role role: LambdaRole.

And now I need to add my lambdas to the right security group.

So, I changed my VPC on my provider to be:

  vpc:
    securityGroupIds:
      - Ref: LambdaSecurityGroup
    subnetIds:
      - Ref: SubnetPrivate1

I've updated the gist with the latest changes.

I can't connect mysql client to RDS through VPN

2 Answers2