connection param in PostgreConfig doesn't accept a function

[TheAdamGalloway]

Package version

21.8.2

Describe the bug

The documentation states "The value of the connection property is same as the configuration object accepted by Knex.", however the knex library allows you to set the connection property to a function that returns config.

The use case for this is to be able to set the Postgres password dynamically to a token, to reduce credential use. I can't see another way of doing this that isn't hacky (it must handle refreshing once tokens are expired).

Reproduction repo

No response

[thetutlage]

Can you please share an example of what setting a dynamic password looks like? I want to make sure once we support it that use-case is covered properly and tested.

[TheAdamGalloway]

Hi @thetutlage - sure, below is a simple code example using Azure AD for auth. Theoretically this would work if the connection param was passed to knex, but in practice the function isn't awaited so I think the server sends a promise instead of the returned string as the password.

This use case is covered in the Knex docs here (where it talks about tokens): https://knexjs.org/guide/#configuration-options

One thing I've tried that works but is a bit hacky is using a provider to fetch a token and update the PG_PASSWORD environment variable every 30 mins or so, and set the pool to close connections every so often. I haven't had a chance to try this outside of dev yet though, so I'm not sure if it'll work in practice.

import` env from '#start/env'

import { defineConfig } from '@adonisjs/lucid'
import { DefaultAzureCredential } from '@azure/identity'

const credential = new DefaultAzureCredential()

// Function to get Azure access token for PostgreSQL
// This will be called automatically by Knex when establishing connections
// and will refresh the token when it expires
const getAzureToken = async (): Promise<string> => {
  const token = await credential.getToken('https://ossrdbms-aad.database.windows.net/.default')
  return token.token
}

const dbConfig = defineConfig({
  connection: 'postgres',
  connections: {
    postgres: {
      client: 'pg',
      connection: {
        host: env.get('DB_HOST'),
        port: env.get('DB_PORT'),
        user: env.get('DB_USER'),
        // @ts-expect-error - Knex supports a function for password (Azure token refresh),
        // but Lucid types expect an object.
        password: getAzureToken,
        database: env.get('DB_DATABASE'),
        ssl: env.get('PG_SSL', false),
      },
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})

export default dbConfig

ETA: Knex also supports an expirationChecker param that is called on each query to check that the token is still valid.