Why is TypeORM DataSource initialization seemingly setting my host to localhost instead of my public IP?

Question

I'm not sure if this is possible, but I don't see why it wouldn't be, so I'm a little stumped. I've been trying to connect to a remote SQL DB hosted on google cloud through a locally running instance of my Node application, but it keeps failing with the given setup for my DB:

//dbConnectorPlugin.ts
...
import typeormConfig from '../../ormconfig';

declare module 'fastify' {
  interface FastifyInstance {
    psqlDB: {
      messages: Repository<messages>;
      users: Repository<users>;
    };
  }
}

async function dbConnector(fastify: FastifyInstance) {
  try {
    const AppDataSource = await new DataSource(typeormConfig).initialize(); 
    fastify.decorate('psqlDB', {
      messages: AppDataSource.getRepository(messages),
      users: AppDataSource.getRepository(users),
    });
  } catch (e) {
    console.error(`Something went dreadfully wrong: ${e}`);
  }
}

export default fp(dbConnector);

It throws the error:

Error [ERR_TLS_CERT_ALTNAME_INVALID]: Hostname/IP does not match certificate's altnames: Host: localhost. is not cert's CN: <google-cloud-db-project-connection-name>

Where the typeOrmConfig variable in the dbConnector file holds the content:

//ormconfig.ts

export default {
  type: 'postgres',
  port: 5432,
  host: process.env.DB_HOST,
  username: process.env.DB_USER,
  password: process.env.DB_PASS,
  database: process.env.DB_NAME,
  logging: true,
  synchronize: false,
  ssl: { ...getSSLConfig() },
  entities: ['dist/src/modules/**/entity.js'],
  migrations: ['dist/src/migration/**/*.ts'],
  subscribers: ['src/subscriber/**/*.ts'],
} as DataSourceOptions;

function getSSLConfig() {
  if (process.env.SSL_CA && process.env.SSL_CERT && process.env.SSL_KEY) {
    return {
      sslmode: 'verify-full',
      ca: process.env.SSL_CA.replace(/\\n/g, '\n'),
      cert: process.env.SSL_CERT.replace(/\\n/g, '\n'),
      key: process.env.SSL_KEY.replace(/\\n/g, '\n'),
    };
  }

  return {};
}

Where I'm currently storing the SSL details I got from GCloud in my .env file for the time being.

And just for further context, a reduced version of my index file where I register my DB plugin is as follows:

//index.ts

import dbConnector from './plugins/PSQLDbConnector';

const server: FastifyInstance = fastify();
const port: number = parseInt(`${process.env.PORT}`, 10) || 8080;

server.register(dbConnector);

server.listen({ port: port }, (err, address) => {
  if (err) {
    console.error(err);
    process.exit(1);
  }
  console.log(`Server listening at ${address}`);
});

I've read through various other postings but can't find any solution that applies to my issue here. Is there something that I should be doing revolving around TypeORM to alter the Host? Or could it be something related to Node itself? Just to try and deduce the issue, I've added 0.0.0.0/0 to my authorized networks on the google cloud side, but that's also done nothing. What am I missing?

The error looks like you have `process.env.DB_HOST` set to localhost to connect to the remote DB, but it is connecting to the remote DB. Are you mapping a port to the gcloud db somehow? — Matt, Sep 13 '22 at 05:08
@Matt sorry I should clarify, the DB_HOST env variable is the public IP address of the sql cloud instance. — Musilix, Sep 13 '22 at 05:32

Matt · Accepted Answer · 2023-07-20T03:39:49.407

This appears to be an open issue in pg. A value for the host is not passed through to the node TLS connect options when using an IP. If you can connect with a resolvable host name that appears on the cert, then use that host name.

Otherwise, rather than changing the sslmode to verify-ca or lower, you may be able to implement a custom checkServerIdentity as suggested in this comment. It's probably safer to rely on the original checkServerIdentity rather than rolling your own so this is just a wrapper to inject the postgres CN/hostname from your config.

import tls from 'node:tls'

function getSSLConfig() {
  if (process.env.SSL_CA && process.env.SSL_CERT && process.env.SSL_KEY) {
    const pg_cn = process.env.DB_CN
    return {
      checkServerIdentity: (nohost, cert) => {
        return tls.checkServerIdentity(pg_cn, cert)
      },
      sslmode: 'verify-full',
      ca: process.env.SSL_CA.replace(/\\n/g, '\n'),
      cert: process.env.SSL_CERT.replace(/\\n/g, '\n'),
      key: process.env.SSL_KEY.replace(/\\n/g, '\n'),
    };

Setting a fixed host name here could have cause issues, or worst case verify an incorrect host, if PG was to reuse this helper for another connection to another host.

The original cert validation error message including the name localhost threw me, but that is a node default when no name is passed through to verify. That seems like a terrible default!

score 0 · Answer 2 · answered Sep 13 '22 at 06:38

The error says that host param provided in ormconfig.ts fetched from the env variable process.env.DB_HOST doesn't match the CN against which the certificate is issued.

Check the domain name of the SSL cert either in the GCloud or if you have cert file with you, you can get the same using openssl (if you have it installed in your system).

openssl x509 -noout -subject -in server.pem

More examples here.

Make sure that the CN that you get from above is pointing to the correct IP address of the database and in this case use this endpoint to connect to the database setting it in process.env.DB_HOST.

If the CN from the cert comes out to be a malformed domain like locahost, you need to generate a fresh certificate in the GCloud console. I'm not a GCloud expert but there should be an option to tell which hostname you want to generate the SSL cert for. You can specify the public address that you have or the FQDN url pointing to that ip address.

Why is TypeORM DataSource initialization seemingly setting my host to localhost instead of my public IP?

2 Answers2