Postgraphile: Connecting to digital ocean managed database

[ajaymore]

Summary

I am getting below error

Error: self signed certificate in certificate chain
at TLSSocket.onConnectSecure (_tls_wrap.js:1473:34)
at TLSSocket.emit (events.js:311:20)
at TLSSocket._finishInit (_tls_wrap.js:916:8)
at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:686:12)

Connection details

npx postgraphile -c "postgres://doadmin:[email protected]:25060/apu-prod?ssl=1&sslrootcert=ca-certificate.crt" -s 'my_db' --retry-on-init-fail --watch --enhance-graphiql --dynamic-json

Tried few more combinations
npx postgraphile -c "postgres://doadmin:[email protected]:25060/apu-prod?ssl=1&sslmode=require&sslrootcert=ca-certificate.crt" -s 'my_db' --retry-on-init-fail --watch --enhance-graphiql --dynamic-json

npx postgraphile -c "postgres://doadmin:[email protected]:25060/apu-prod?sslmode=prefer" -s 'my_db' --retry-on-init-fail --watch --enhance-graphiql --dynamic-json

Below is connection string copied from digital ocean console.
postgresql://doadmin:[email protected]:25060/apu-prod?sslmode=require

Am I missing something while running postgraphile?

Additional context

[benjie]

Try giving an absolute path to the cert rather than relative

[benjie]

You can use sslmode=no-verify if you’re not concerned about verification

[ajaymore]

Try giving an absolute path to the cert rather than relative

Tried that as well.

npx postgraphile -c 'postgres://[e-mail]:25060/apu-prod?sslmode=require&sslrootcert=/Users/mystical/Downloads/ca-certificate.crt' -s 'my_db' --retry-on-init-fail --watch --enhance-graphiql --dynamic-json

sslmode=no-verify does not work as digital ocean managed database strictly enforces SSL

@benjie the command works for local database and AWS RDS database where SSL option is not required.

[benjie]

@ajaymore Try and get the connection running with the pg (node-postgres) module. If you can do that, try and get it running with the pg module using a connectionString. If that works then it should work in PostGraphile.

[benjie]

(I suspect the certificate you're passing doesn't have the content required to make the connection authenticated. But I don't know what to advise there. Here's one that works with RDS (note it's a certificate chain): https://github.com/graphile/starter/blob/main/data/amazon-rds-ca-cert.pem)

[ajaymore]

@ajaymore Try and get the connection running with the pg (node-postgres) module. If you can do that, try and get it running with the pg module using a connectionString. If that works then it should work in PostGraphile.

It works with below configuration

const { Client } = require('pg');

const client = new Client({
  user: 'doadmin',
  host: '******-postgres-do-user-4864288-0.db.ondigitalocean.com',
  database: 'shiparc',
  password: '*******',
  port: 25060,
  ssl: {
    rejectUnauthorized: false,
  },
});

client
  .connect()
  .then((info) => console.log(info))
  .catch((err) => console.log('errror', err));

Not sure how to convert this to connection string.

[benjie]

?sslmode=no-verify _should_ do the same assuming you're using this version of pg-connection-string:

https://github.com/brianc/node-postgres/blob/4b229275cfe41ca17b7d69bd39f91ada0068a5d0/packages/pg-connection-string/index.js#L96

Maybe you need to update pg and pg-connection-string?

[benjie]

In Graphile Starter I had to use yarn resolutions to force the use of the latest pg-connection-string: https://github.com/graphile/starter/blob/563b74f716cddbc6b915b79d7272a78720d5b42c/package.json#L83

[benjie]

@ajaymore what solved the issue in the end?

[ajaymore]

It didn't work for me, documentation for Digital Ocean managed database itself is quite sparse and also quite old.
https://github.com/digitalocean/databases/tree/master/examples/node-contacts

It is working with self hosted postgres so continuing with that.

[thomashermine]

If you are using Postgraphile as a library, you can pass any options to node-pg lib in the first parameter. This solved it for me:

const postgraphileServer = postgraphile({
    connectionString: POSTGIS_CS
     // Needed for DigitalOcean Postgres Managed Database ⤵️
    ssl: {
      rejectUnauthorized: false,
    },
  }, SCHMEMA, POSTGRAPHILE_OPTIONS)