Is it possible to use dynamic credentials coming from a global object?

[kaiossoares]

I need to use hashicorp's Vault for the database, and this way, the Vault makes the database user's credentials dynamic.

This way I want to avoid using the 'msg.pgConfig' message, which is not efficient.

So somehow, within a 'function' node, it is possible to disconnect from the connection, then connect again by passing the new password?

I'm reading the node-postgres documentation and examples of this, like below:

import { Client } from 'pg'
const client = new Client()
 
await client.connect()
 
const result = await client.query('SELECT NOW()')
console.log(result)
 
await client.end()

Now what I'm trying is to make a function node able to influence the 'node-red-contrib-postgresql' connection. I know it's possible to import modules in the 'function' node, I'm trying that route too. I've already tried connecting to a global object, but when the password changes, the implementation is lost.

Any help or tips would be appreciated.

[Alkarex]

At the moment, the only built-in option is msg.pgConfig (dynamic per request, but with reconnection at each message). It is also possible to use environment variables, but that requires a restart of the flow.
It could be possible to update the function handling msg.pgConfig, with one of the following approaches:

1. only reconnect when there is a change in the configuration
2. make a new parameter to override the current pgPool settings and reconnect

In particular option 1 should not be too difficult to code (keep the client instance in local node variable, re-use if identical msg.pgConfig, kill otherwise). A variant is to use a pgPool local to the node. PR welcome

[joaoroscoe]

I just created a PR trying to address this. It seems to work, but I have performed only very basic, go/no go testing, and I am concerned about how it will behave if connection pool is reset by one node while another one is trying to use it.

[Alkarex]

#66

[kaiossoares]

Thank you very much for your answer, I will try to follow the options you recommended.