Basic Example in secure mode

Hi, there
I am trying to build an app with node.js using pg driver. I have went through the example on Cockroachdb website, however the tutorial is based on insecure mode, when I tried in secure mode (create users with certificates and grant privileges), the system keeps posting error:
could not connect to cockroachdb { error: node is running secure mode, SSL connection required…

It might be the issue with certificates, does anyone know how to solve this?
Thank you so much!
Best Regards

Hello!

this issue has been discussed on the forum before: Psql remote connection certificate not valid

Can you check that discussion and see if the provided solutions helps?

Thanks!

Hi, mate
Thank you for your advice! I have went through the solved issue you provided, that is very helpful in other aspects. It seems like my problem is much closer to [C/C++ secure connection example?], I have tried to google it the answer for node.js version, I cannot find an answer on how to add ssl connection sslmode=verify-full&sslcert=client.crt&sslkey=client.key&sslroot=ca.crt to the node.js basic example you provided in in-secure mode:
// Connect to the "bank" database. var config = { user: 'maxroach', host: 'localhost', database: 'bank', port: 26257 };
Would be really appreciated if you could give some help, Thanks!

We have an example with node using SSL here:

Can you check it out and see if it works for you?

1 Like

Hi, knz
Thank you so much for your info, I have done some research and tried to combine your given example with the basic_example code, but unfortunately it does not work quite well. Do you have an secure version of basic_example just like the [C/C++ secure connection example?] but in node.js version? Sorry for taking your time.
Best Wishes

This is a working node.js program that connects to a secure roach cluster:

#!/usr/bin/env node

const fs = require('fs')
const { Pool } = require('pg')

const pgconfig = {
  host: '29.333.203.4',
  port: 26257,
  user: 'root',
  database: 'time_series',
  // max: 100,  // The pool size
  ssl: {
    rejectUnauthorized: false,
    ca: fs.readFileSync('./certs/ca.crt').toString(),
    key: fs.readFileSync('./certs/client.root.key').toString(),
    cert: fs.readFileSync('./certs/client.root.crt').toString()
  }
}

const pool = new Pool(pgconfig)

const query = `
    UPSERT INTO aggregates_1
        SELECT
            source_id,
            $1::timestamp with time zone + ($2::interval * $3::integer) AS "timestamp",  -- End time of aggregate period
            avg(value) AS average,
            stddev(value) AS standard_deviation,
            count(value) AS count
        FROM series_1
        WHERE "timestamp" >= $1::timestamp with time zone + ($2::interval * $3::integer)  AND           -- Start of aggregate period
              "timestamp" <  $1::timestamp with time zone + ($2::interval * ($3::integer + 1))          -- End of aggregate period
        GROUP BY source_id;`

// The starte time of sereies_1 is 2017-09-07 15:37:00+00:00

const startTimestamp = '2018-02-01 00:00:00+00:00'
const interval = '1 hour'

function makeAggregates (startTimestamp, interval, offset) {
  pool.query(query, [startTimestamp, interval, offset], (err, res) => {
    if (err) {
      console.log('Error:', err)
    } else {
      console.log('OK: ', offset)
      makeAggregates(startTimestamp, interval, offset + 1)
    }
  })
}

makeAggregates(startTimestamp, interval, 0)

The secure cluster it talks to was created by naively following the instructions here:
https://www.cockroachlabs.com/docs/stable/manual-deployment.html

Looks like the trick here is the “.toString()” after fs.readFileSync

Hi, mate
Thanks a lot for your efforts, it does work! Thank you so much.
All the best