Support a password function for the client configuration

[benkeil]

If you use e.g. AWS Aurora Postgres with IAM authentication it is hard to deal with it in the code.

It would be nice that I can use a function to return the password, like this:

const signer: RDS.Signer = ...
const params: RDS.Signer.SignerOptions = ...

return new pg.Pool({
  max: 1,
  host: databaseConnection.clusterEndpoint,
  port: databaseConnection.port,
  database: databaseConnection.database,
  user: databaseConnection.username,
  password: async () => signer.getAuthToken(params),
  ssl: true,
});

[sehrope]

You can do exactly that. This was added in #1926.

Check your pg version as you may be on an older version that does not support that feature. It was added v7.12.0.

[vitaly-t]

This feature has been available for a while. Here's proper TypeScript declaration from pg-promise:

type DynamicPassword = string | (() => string) | (() => Promise<string>);

and the connection object:

const cn = {
    //...
    password?: DynamicPassword
    //...
};

Copied from here.

[benkeil]

Then the types are still wrong...

// Type definitions for pg 7.14
...
export interface ClientConfig {
    user?: string;
    database?: string;
    password?: string;
    port?: number;
    host?: string;
    connectionString?: string;
    keepAlive?: boolean;
    stream?: stream.Duplex;
    statement_timeout?: false | number;
    parseInputDatesAsUTC?: boolean;
    ssl?: boolean | ConnectionOptions;
    query_timeout?: number;
    keepAliveInitialDelayMillis?: number;
    idle_in_transaction_session_timeout?: number;
    application_name?: string;
    connectionTimeoutMillis?: number;
}

[vitaly-t]

@benkeil

Oh yeah, you betcha! They have always been wrong, that's why I had to write my own declaration for this library, one that I could update properly, in a timely manner (it is only a sub-set that I need).

[mikicho]

Hey,
I'm trying to connect to Postgres Aurora with IAM Authentication same as @benkeil and it seems like my password function never get called and I get:

error: pg_hba.conf rejects connection for host "my-ip", user "db_user", database "db_name", SSL off

I'm using pg 8.5.1.

[sehrope]

@mikicho HBA (Host Base Access control) rules get applied by the server before requesting client authentication. So if your connection is rejected for HBA reasons then it will not invoke your client side password function. Check the pg_hba.conf file of your server and ensure that it has an entry allowing you to connect from the machine where you client is running.

[mikicho]

@sehrope Thanks. I succeed to connect to the DB with the token via psql.
Isn't it mean that my machine is allowed to connect to the database?

[sehrope]

Make sure to enable SSL in your pg client config. RDS does not let you connect with auth tokens over plaintext connections. That's the "... SSL off" part that error message.

It works from psql because psql defaults to trying with SSL first.

[mikicho]

@sehrope Thanks.
I set ssl: true and now I get:

Error: unable to get local issuer certificate

Does psql use some default certificate?
(I'm running in a docker container and the psql from my machine (no container))

[sehrope]

psql defaults to enabling SSL but not checking the server's certificate: https://www.postgresql.org/docs/current/libpq-ssl.html ("prefer" mode)

This driver was changed a few versions back to be more secure by default so the default is to reject untrusted certificates. To mimic the "prefer" behavior, set the rejectUnauthorized to true. Or, even better, specify the CA certificates for your database so the server's certificate is validated.

[mikicho]

set the rejectUnauthorized to true

I guess you mean false (it's working! thanks!)
I'll check what we need to do to validate the certificate for better security.