Connecting with pg.Pool and GCP IAM-based database authentication

Question

Currently, our api (deployed on cloudRun) connects to our Postgres database by passing in a pgConfig with a db configuration and a db user and password.

For example:

const configObject = {
  host: cloudRunHost,
  user: dbUser,
  password: dbPassword,
  database: dbName
}

async function connect() {
  if(!client) {
    const pgPool = new pg.Pool(configObject);
    await pgPool.connect()
      .then((result) => {
        logger.info('Connected to DB')
        client = result;
      }).catch((err) => {
        logger.error(err);
      });
  }
} 

We want the app itself to connect to the database by using Cloud SQL IAM database authentication

So far:

1. The api cloudRun instance has a service account
2. The database and CloudSQL has been configured for IAM-based access (we can access with our machine service accounts)
3. The api service account has access to the DB via IAM, and permissions granted to said user on the Postgres db itself
4. When the above code runs, it logs error: empty password returned by client

We've tried removing the password line entirely from the configObject but it hasn't helped. Any thoughts on why my service account can access the db directly but the api's can't I suspect we need to indicate to pgPool that we're trying to connect via IAM instead of via user/password.

Answer 1

While @kurtisvg answer was correct at that point in time. I'd like to call attention to anyone ending up in this question that there is now a Cloud SQL Node.js Connector available that will enable IAM authentication while also removing the need for an extra proxy process.

Answer 2

From your question I believe you are using NodeJS. But currently IAM database authentication is supported by Python Connector, Java Connector and Cloud SQL proxy which is mentioned here. Also to use Automatic IAM database authentication it is required to use a Cloud SQL connector.

IAM database authentication is currently supported by the Cloud SQL Auth proxy, the Java connector, and the Python connector.

Automatic IAM database authentication requires the use of a Cloud SQL connector.

As you are using NodeJS which is not supported, that seems to be the reason why you are getting an error: empty password returned by client.

Answer 3

Unfortunately, there isn't a good way to use "automatic" IAM DB AuthN on Cloud Run - while Cloud Run does use the proxy, there is no way for users to flip on the "-enable-iam-login" flag.

Go, Python, and Java users can use the language-specific connectors, but there isn't one for Node.js.

It looks like node-postgres does have support for dynamic passwords, so you might be able to do something like this:

const {GoogleAuth} = require('google-auth-library');

async function main() {
  const auth = new GoogleAuth();
  const pool = new pg.Pool({
    connectionString: 'postgresql://[email protected]:5432/my-db',
    password: async () => auth.getAccessToken(),
  })
}