Options to securely connect to a database without credentials?

[deostroll]

Is there a way to connect to postgres using this module which obviates the need for storing credentials in the application or providing it via environment variables?

[charmander]

Does a private key file count as “storing credentials in the application”? Or, to answer many other questions along with that one: why?

[deostroll]

Just inquiring. Oracle has user ssl certificate based authentication. So I was wondering what is equivalent for postgres db? If there is such an auth mechanism, how do we use this library to connect to the database?

[charmander]

Yes, PostgreSQL supports client certificate authentication too. There’s some information about it in the documentation, e.g. https://www.postgresql.org/docs/12/auth-cert.html, and probably tutorials if you look around.

[deostroll]

I tried this article. And I observed we have to pass a "user" field. (Password isn't required). Here is an example snippet showing how I set my pool options:

const pg = require('pg');
const CERT_PATH = process.env.CERT_PATH || "d:\\docs\\postgres\\certs";
const path = require('path');
const fs = require('fs')
const _get = file => fs.readFileSync(file).toString();

var options = {
  database:'postgres',
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  ssl: {
    ca: _get(path.join(CERT_PATH, 'root.crt')),
    key: _get(path.join(CERT_PATH, 'postgresql.key')),
    cert: _get(path.join(CERT_PATH, 'postgresql.crt'))
  }
};

This is relatively ok. However, is there an option where I don't require to pass both user and password?

[charmander]

Like reading the username from the certificate? I’m not aware of one, but it might exist. Note that there is a default username corresponding to the current OS user, and the PGUSER environment variable can be used too. (libpq also has service files, but pg doesn’t support them yet.)