Unable to connect staging env - [DatabaseConnectError: Unable to connect to the given uri] / [Connection error: no pg_hba.conf entry for host]

[FlorianBruniaux]

Hello all,

Feature(s) impacted

Deployment > Configure your environment variables step

Observed behavior

I’m trying to deploy forestadmin staging env but i’ve some issues
In ForestAdmin web interface(deployement > Configure your environment variables), it’s still loading “Waiting for your backend to run …”

I’ve an error in logs (see full trace after)

DatabaseConnectError: Unable to connect to the given uri: postgres:/xxxxxxx.
Connection error: no pg_hba.conf entry for host "xxxxxxx", user "xxxxxxx", database "xxxxxxx", no encryp...

My target server is on AWS ECS via Fargate
Database : RDS / PostgreSQL
Everything works correctly without forest (i’ve access to db etc)

I’ve read several similar threads on this community and github

• Stuck at the final configuration step - #14 by philip
• BUG - staging env not available - #13 by GuillaumeGautreau
• Server does not support SSL connections on remote db · Issue #193 · ForestAdmin/lumber · GitHub
• …

So I’ve set the following env var

DATABASE_SSL=true
DATABASE_REJECT_UNAUTHORIZED=false

But still stuck

Do you have any idea ?

Expected behavior

Deploy staging env in forest UI with onboarding/deployment …

Failure Logs

DatabaseConnectError: Unable to connect to the given uri: postgres:/xxxxxxx.
Connection error: no pg_hba.conf entry for host "xxxxxxx", user "xxxxxxx", database "xxxxxxx", no encryp...
  File "/usr/src/app/node_modules/@forestadmin/datasource-sql/dist/connection/handle-errors.js", line 24, col 15, in handleErrors
    throw new errors_1.DatabaseConnectError(`${nameWithSpaces}: ${error.message}`, options.debugDatabaseUri);
  File "/usr/src/app/node_modules/@forestadmin/datasource-sql/dist/connection/index.js", line 50, col 37, in connect
    (0, handle_errors_1.default)(sshTunnel?.error ?? socksProxy?.error ?? reverseProxy?.error ?? e, options);
  File "node:internal/process/task_queues", line 95, col 5, in process.processTicksAndRejections
  File "/usr/src/app/node_modules/@forestadmin/datasource-sql/dist/index.js", line 43, col 21, in buildSequelizeInstance
    sequelize = await (0, connection_1.default)(options);
  File "/usr/src/app/node_modules/@forestadmin/datasource-sql/dist/index.js", line 57, col 27, in <anonymous>
    const sequelize = await buildSequelizeInstance(uriOrOptions, logger, options?.introspection);
  File "/usr/src/app/node_modules/@forestadmin/datasource-customizer/dist/datasource-customizer.js", line 46, col 30, in <anonymous>
    let dataSource = await factory(logger);
  File "/usr/src/app/node_modules/@forestadmin/datasource-customizer/dist/decorators/decorators-stack.js", line 75, col 13, in DecoratorsStack.applyQueuedCustomizations
    await queuedCustomizations.shift()(logger); // eslint-disable-line no-await-in-loop
  File "/usr/src/app/node_modules/@forestadmin/datasource-customizer/dist/datasource-customizer.js", line 126, col 9, in DataSourceCustomizer.getDataSource
    await this.stack.applyQueuedCustomizations(logger);
  File "/usr/src/app/node_modules/@forestadmin/datasource-customizer/dist/datasource-customizer.js", line 46, col 30, in <anonymous>
    let dataSource = await factory(logger);
  File "/usr/src/app/node_modules/@forestadmin/datasource-customizer/dist/decorators/decorators-stack.js", line 75, col 13, in DecoratorsStack.applyQueuedCustomizations
    await queuedCustomizations.shift()(logger); // eslint-disable-line no-await-in-loop

Context

• Project name: native-spaces
• Environment name: staging
• Agent (forest package) name & version: Node agent → @forestadmin/agent@^1.34.1
• Database type: PostgreSQL (AWS RDS)
• Server type : AWS ECS with Fargate

Thanks !

[Enki]

Hello @FlorianBruniaux,

Could you share your code setting up the server and the DB connection ?
This error is often due to the env variables you mentioned (then it shouldn’t be that), or not trying to connect via ssl. What URI do you use to connect ?

[FlorianBruniaux]

Hello @Enki

Thanks for your answer

Here is the code block that connects forest agent on my nestjs main.ts

  /***************************************************************************/
  /* Forest Admin */
  const forestAdminEnabled = configService.get('FOREST_ENABLED', false) === 'true';
  Logger.log(`🌲ForestAdmin enabled ? ${forestAdminEnabled}`);
  if (forestAdminEnabled) {
    const agent = createAgent({
      authSecret: configService.get('FOREST_AUTH_SECRET'),
      envSecret: configService.get('FOREST_ENV_SECRET'),
      isProduction: configService.get('ENVIRONMENT') === 'production',
      typingsPath: './typings.ts',
      typingsMaxDepth: 5,
    })
      // Create your SQL datasource
      .addDataSource(createSqlDataSource(configService.get('DATABASE')['URL']));

    await agent.mountOnNestJs(app).start();
  }
  /* Forest Admin */

In my env file i’ve

FOREST_ENV_SECRET=xxxxx
FOREST_AUTH_SECRET=xxxx
NODE_ENV=staging
DATABASE_SSL=true
DATABASE_REJECT_UNAUTHORIZED=false

FYI ssl is enabled on my RDS instance

Some questions :

1 - Does the UI has to access the database or is it just my backend ?
I’m asking this as my instance is not accessible publicly; it’s only accessible by the ECS container. If i switch off forest admin with forestAdminEnabled=false then our app works without any issue

2 - if 1 is yes; can i have a forest admin ips range to whitelist ?

3 - I would like to “redo” staging deploy but i cannot remove it as it has been promoted as default env (no production yet). How can I put back my developement env as default ?

[Enki]

Thanks for the reply.

1. Only your backend can access to your DB, but your backend includes the forestadmin agent which also have to be able to connect to the DB.
   “it’s only accessible by the ECS container.” => You have to make your DB instance accessible from your server (not just the ECS container). “createSqlDataSource” is trying to establish a connection with the DB, if it cannot, it throws the error you encounter.

2. Forest admin server does not access to your DB, only your server/agent does. Your server/agent have to be able to connect to the Forest admin server, but that does not seem the issue here.

3. I don’t think you can put back your dev env as default, but you can recreate an environment, set it as default, and delete the old one. I’m not sure if it will be helpful thought. I think the issue you have is fixable.

There is another env variable that you have to setup :
NODE_TLS_REJECT_UNAUTHORIZED=0

[FlorianBruniaux]

Hey Enki,

Thanks again for the reply

1. You have to make your DB instance accessible from your server (not just the ECS container). “createSqlDataSource” is trying to establish a connection with the DB, if it cannot, it throws the error you encounter.
   ==> The agent is running on backend, it’s setup inside the main.ts and is using the same database connection. If it works for the app then it should works for the node agent

2. Ok top

3. Ok

I’ll try with env var NODE_TLS_REJECT_UNAUTHORIZED=0 to see if that solves the issue or not

A new question

4. Can it be an issue with my forest credentials ?

[Enki]

4. The forest credentials for your agent is just the env variable FOREST_ENV_SECRET, the only thing to do is to ensure it is the same in your .env than in your environment settings.
   I think the error would be different if this was the issue.

Are you passing to createSqlDataSource only the connection URI to the DB ?
The example in the doc (https://docs.forestadmin.com/developer-guide-agents-nodejs/data-sources/provided-data-sources/sql) suggests to add an object :

createSqlDataSource({
  uri: 'postgres://user:pass@localhost:5432/myDatabase',
  sslMode: 'preferred',
})

[FlorianBruniaux]

@Enki

Thanks again

Still the same issue

To sumup I’ve :

1. checked my FOREST_ENV_SECRET and it’s correct

2. Set up following env vars

DATABASE_SSL=true
DATABASE_REJECT_UNAUTHORIZED=false
NODE_TLS_REJECT_UNAUTHORIZED=0

3. Adapted my agent set in backend in main.ts for createSqlDataSource() following doc

/***************************************************************************/
  /* Forest Admin */
  const forestAdminEnabled = configService.get('FOREST_ENABLED', false) === 'true';
  Logger.log(`🌲ForestAdmin enabled ? ${forestAdminEnabled}`);
  if (forestAdminEnabled) {
    const agent = createAgent({
      authSecret: configService.get('FOREST_AUTH_SECRET'),
      envSecret: configService.get('FOREST_ENV_SECRET'),
      isProduction: configService.get('ENVIRONMENT') === 'production',
      typingsPath: './typings.ts',
      typingsMaxDepth: 5,
    })
      // Create your SQL datasource
      .addDataSource(
        createSqlDataSource({
          uri: configService.get('DATABASE')['URL'],
          sslMode: 'preferred',
        })
      );

    await agent.mountOnNestJs(app).start();
  }
  /* Forest Admin */

The error is still

Unable to connect to the given uri: postgres://xxxxxx.eu-west-3.rds.amazonaws.com:5432/xxxxxx.
Connection error: no pg_hba.conf entry for host "xxxxxx", user "xxxxxx", database "xxxxxx", no encryp...

I also see that on my ForestAdmin Staging env that it has no origin.
5. Can it be linked to that ?

[Steve_Bunlon]

@FlorianBruniaux

sslMode: 'preferred',

For people interested in such a topic, when you face that kind of error with a remote database, it is often an issue regarding connecting to your database using ssl. Especially if you are sure that the user you attempt to connect with exists and has sufficient rights to connect.

In that regard, playing around with the sslMode option and the ssl one is the way to go. Most of the time, your database want to enforce ssl connection so using the following in your datasource creation code will do the trick:

ssl: true,
sslMode: "required" // or "verify"

That way, ssl connection with the database will be enforced.

The complete list of available configuration for the ssl connection is available here.

In the hope that it might help someone.

Steve.

[FlorianBruniaux]

Hey,

Thanks for your answer @Steve_Bunlon I will try with sslMode: "required" as with verify I had the issue :

ForestAdmin agent launch error: DatabaseConnectError: Unable to connect to the given uri: postgres://xxxx.eu-west-3.rds.amazonaws.com:5432/xxxx

Connection error: self-signed certificate in certificate chain [39m

So error with the certificate check.

For now in staging I can leave it in sslMode = "required" and skip certificate check but in production it wouldn’t be great…

While searching I saw that the subject was discussed in several threads.

• self signed certificate in certificate chain · Issue #2558 · brianc/node-postgres · GitHub
• How to establish a secure connection (SSL) from a Node.js API to an AWS RDS | by Fabricio Pautasso | Nexton | Medium
• Production custom URL failed with error "self signed certificate in certificate chain"

I would like to use the RDS DB certificate (https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem) to establish the connection like here: https://node-postgres. com/features/ssl#self-signed-cert

I have read your doc (in particular SQL (without ORM) - Node.js Developer Guide) but I do not see how to pass my certificate to guarantee that this works with sslMode="verify"

Could you tell me how to proceed to pass the certificate ?

Thanks in advance

Florian

[FlorianBruniaux]

Hey, I confirm that i works with sslMode: 'required'

[Steve_Bunlon]

Hello @FlorianBruniaux

Happy to hear that it works properly now and thanks for the update !

For people that might be interested, you give necessary information to ForestAdmin connector in order to make ssl connection work with self signed certificates.

In fact, you don’t want to skip self signed certificate verification on a production environment.

On the latest technology, we use sequelize under the hood and we provide you with a way to configure it with native options, some of these options being the ssl connection configuration.

Everything is explained in this documentation. You can look for “dialectOptions”, allowing you to pass any needed information to sequelize.

You can refer to this documentation form sequelize, and also the documentation from potsgresql to get the connection’s options available.

I hope this can help someone

Steve.