Issues when using RDS Proxy with IAM Authentication

[jordonias]

I was trying to replace pg with postgres.js and ran into issues when trying to use IAM Authentication.
I've code of a simple handler I have been testing with. Unfortunately to test this requires setting up some infrastructure on AWS, I could possibly create something with aws-cdk over the weekend to make this easier but don't currently have the time.

You can see below I have two boolean (usePostgresJs, useIAM) values I switch to test different scenarios.
usePostgresJS = true && useIAM = true HANGS
usePostgresJS = true && useIAM = false OK
usePostgresJS = false && useIAM = true OK
usePostgresJS = false && useIAM = false OK

The difference between using IAM Authentication and not is the use of a token to authenticate with the RDS proxy. This token is 1000+ characters. I though this might be a good place to start looking so I tried to do some digging around and comparing against how pg serializes and sends the password vs how it's done in postgres.js to no avail. I'm not even sure if this is the issue, but maybe this could be a lead for anyone trying to figure out what's wrong.

Let me know if there's any information I can provide to help with this. If I have time I will create something with aws-cdk so this could be tested more easily.

import postgres from 'postgres';
import { Client } from 'pg';
import AWS from 'aws-sdk';

const handler = async () => {
  const usePostgresJS = true;
  const useIAM = true; // Enable/disable IAM auth in RDS Proxy when changing this

  const options = { 
    user: process.env['DATABASE_USER'],
    host: process.env['DATABASE_HOST'],
    ssl: {
      rejectUnauthorized: false,
    },  
  };

  if(useIAM) {
    const signer = new AWS.RDS.Signer({
      region: process.env['AWS_REGION'],
      hostname: process.env['DATABASE_HOST'],
      port: 5432,
      username: process.env['DATABASE_USER'],
    }); 

    const token = signer.getAuthToken({
      username: process.env['DATABASE_USER'],
    }); 

    options.password = token;
  } else {
    options.password = process.env['DATABASE_PASSWORD'];
  }

  if(usePostgresJS) {
    const sql = postgres(options);
    await sql`SELECT 1`; // Hangs here when useIAM=true
  } else {
    const client = new Client(options);
    await client.connect();
    client.query('SELECT 1');
  }
};

[porsager]

Hi @jordonias .. I'm not an AWS user myself, but tried to setup something on AWS anyway to test this 😬 Well, two hours in now, and I give up, so if there's any chance you can make something reproducible it'd be great.. You can also PM me on https://gitter.im/porsager/postgres if you have a test system I can try to connect to to reproduce it.

[jordonias]

Hi @jordonias .. I'm not an AWS user myself, but tried to setup something on AWS anyway to test this 😬 Well, two hours in now, and I give up, so if there's any chance you can make something reproducible it'd be great.. You can also PM me on https://gitter.im/porsager/postgres if you have a test system I can try to connect to to reproduce it.

Hi @porsager thank you for looking into this. I will try to get something set up tomorrow.

[jordonias]

Hi @jordonias .. I'm not an AWS user myself, but tried to setup something on AWS anyway to test this 😬 Well, two hours in now, and I give up, so if there's any chance you can make something reproducible it'd be great.. You can also PM me on https://gitter.im/porsager/postgres if you have a test system I can try to connect to to reproduce it.

Hi @porsager thank you for looking into this. I will try to get something set up tomorrow.

Haven't forgot about this. Going to need another day at least to get this set up. I'll have something this week though.

[porsager]

Thanks for the update @jordonias ..

[jordonias]

@porsager I've created a CloudFormation stack you can test this issue with. Let me know if you have any questions about it or how to set it up. I included some details about how to deploy and test the functions in the top level README.md.
https://github.com/jordonias/test-postgresjs

[porsager]

@jordonias Would it be possible for you to expose an endpoint and send IAM credentials in private I can test with?

[porsager]

The culprit here was the RDS Proxy not accepting the 'utf-8' client_encoding value (which is fine for PostgreSQL), but instead requiring it to be UTF8 strictly. I've changed the default in Postgres.js to UTF8 which is probably for the better anyway, so everything should be good now.

[bestickley]

@jordonias, were you able to get this to work even without requiring SSL? AWS docs show here using SSL sslmode=verify-full mode

[hshoja]

@porsager, if the token has an expiration time, Is there a mechanism to automatically refresh the token before it expires ?

const token = signer.getAuthToken({
      username: process.env['DATABASE_USER'],
    }); 

cc @jordonias

[Manouchehri]

@hshoja Did you find a solution?