updating username/password after initialisation

[tobywilliams]

Hi,

I would like to apply a multi-user strategy for dealing with database secret rotation. At the moment, some period of time after the database secrets are rotated, the server stops working and I have to manually knock over the pods to get them started again.

My plan is to have the module that creates the database object setup a new user/password when it starts, then use that for the life of the pod. I've write a basic implementation below, however at the moment when I update the pool properties for the new creds, I get an error "too many connections for database". The docs describes the $pool as readonly, so I'm not really surprised it didn't work but haven't found any other clues about how I might mutate the auth after it's exported.

The complicating factor here, I think, is that the database object is exported immediately, so the timing is key if I want all the other places that database is used to have a different connection pool object.

  import pgp from 'pg-promise';
  import config from '../config';

  const pg = pgp();
  const database = pg(config.DATABASE_CONFIG);

  const createDbConnectionCreds = async () => {
    const user = 'u_username';
    const password = 'different everytime';
    await database.query(
        'CREATE USER ${user:raw} WITH PASSWORD ${password} NOCREATEDB;',
        { user, password }
    );
    database.$pool.options.user = user;
    database.$pool.options.password = password;
  };
  
  createDbConnectionCreds();
  
  export default database;

Is there a way to update the user/password? why is it me telling me too many connection (if I comment the function the application works normally)?

Any help greatly appreciated

[vitaly-t]

The functionality has always been there - see password definition:

https://github.com/vitaly-t/pg-promise/blob/master/typescript/pg-subset.d.ts#L84C27-L84C27.

The type already tells you that it can be: string, sync function or async function.

[tobywilliams]

password being a function I did find, however user is a string, so I can update the password of the connection but I don't think I can change over to a newly created user?

[tobywilliams]

I've updated to 11.5 and my proxy the db object strat. seems to working for the moment, I'll follow that for the moment

[vitaly-t]

@tobywilliams Is this resolved now?

[tobywilliams]

Thanks for the reminder, impressive how this repo is maintained!

The solution was a mix of small issues which I'll put here so it might help somebody.

1. My first solution was to update the username/password of the pool, updating the password is easy as it's a function but the username is just a string and it didn't really feel a proper solution to try and manually mutate the pool even if might have worked, (my issues might have confused things that would have worked).
2. For some reason my local postgres had a max connection limit of 2 on each individual database? so the errors about connections exceed would have probably been fixed if I hadn't already moved on.
3. I was trying to export a javascript proxy object so that I could pick out the updated connection once the new connection was established. Older versions of PgPromise didn't like having a Proxy and when I updated to the latest version that problem went away.
4. There was some RDS specific stuff I needed to include when running against RDS
5. I need to a wait before trying to use the connection to create the new user.

import crypto from 'crypto';
import pgp from 'pg-promise';

import config from '../config';

import CREATE_MULTI_USER from './create-multi-user.sql';
import log from '../logging';

let podSpecificConnection;

const isAWSDB = !['local', 'functional-tests', 'pipeline'].includes(
    process.env.ENVIRONMENT
);

const pg = pgp();

function wait(timeout) {
    return new Promise((resolve) => {
        setTimeout(() => {
            resolve();
        }, timeout);
    });
}

const temporaryDatabase = pg(config.DATABASE_CONFIG);

const createDbConnectionCreds = async () => {
    try {
        await wait(5000);

        const user = XXXXXX
        const password = XXXXXXXX

        await temporaryDatabase.multiResult(CREATE_MULTI_USER, {
            user,
            password,
        });

        podSpecificConnection = pg({
            ...config.DATABASE_CONFIG,
            user,
            password,
        });
    } catch (err) {
        log('ERROR', 'DB_USER_SETUP', null, {
            message: `error setting up user connection to DB.\n${err.message}`,
        });
        throw err;
    }
};

// Only create alternate credentials for environments where the db secret is automatically rotated
if (isAWSDB) {
    createDbConnectionCreds();
}

const database = new Proxy(temporaryDatabase, {
    get(_, prop, receiver) {
        if (Boolean(podSpecificConnection)) {
            return Reflect.get(podSpecificConnection, prop, receiver);
        }

        return Reflect.get(...arguments);
    },
});

export default database;

If I hadn't run into the connection limit, reassigning temporaryDatabase with a new connection object might have worked just fine.