[SOLVED] AWS RDS / EC2: TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full

Issue

I'm attempting to retrieve a User model from a Node js 8.12.0 API, using knex and bookshelf ORM. Database is Postgres 10.4.

The API works fine locally, but hosted on ElasticBeanstalk EC2 and RDS, I get error:

Unhandled rejection TimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

I'm able to connect and make queries to the RDS instance separately via connection string / password (it prompts for pw after I enter this):

psql -h myinstance.zmsnsdbakdha.us-east-1.rds.amazonaws.com -d mydb -U myuser

Security Groups:

• The EC2 security group (set up by EB) is sg-0fa31004bd2b763ce, and RDS has an inbound security rule for PostgreSQL / TCP / port 5432 / for the matching source (sg-0fa31004bd2b763ce)— so it doesn't seem like the security group is a problem

RDS was created in a VPC, but the VPC's security rules are open too:

- security groups attached (multiple)
    - name: mysgname
    - group ID: sg-05d003b66fe1a4a94
    - Inbound rules: 
        - All Traffic (0.0.0.0/0) 
        - HTTP (80) for TCP (0.0.0.0/0) 
        - SSH (22) for TCP (0.0.0.0/0) 
        - PostgreSQL (5432) for TCP (0.0.0.0/0) 

Publicly accessible: Yes

users controller:

router.get('/users', function(req, res) {
    new User.User({'id': 1})
        .fetch({withRelated: ['addresses']})
        .then((user) => {
            res.send(user);
    });
});

Knexfile:

production: {
    client: 'pg',
    version: '7.2',
    connection: {
        host: process.env.PG_HOST || 'localhost',
        port: process.env.PG_PORT || '5432',
        user: process.env.PG_USER || 'myuser',
        password: process.env.PG_PASSWORD || '',
        database: process.env.PG_DB || 'mydb',
        charset: 'utf8',
    },
    pool: {
        min: 2,
        max: 20
    },
},

Firstly, why is this happening only on AWS hosted environment and not locally. Secondly, how can I fix this issue? Should I increase max for pools?

Solution

You need to check your Network Access Control List (NACL) in your VPC and make sure your INBOUND and OUTBOUND are configured correctly. Security Groups are at the Instance level of security and the NACL is security at the Subnet level.

Most of the time when you are experiencing a Timeout error connecting to something in a custom VPC it will be a configuration problem with a Security Group or a NACL or Both.

Answered By - Chad Elias