Support for Secret Provider.

[elliotcourant]

I'd like to use Vault or some other secret provider in order to authenticate an application to PostgreSQL.

https://www.vaultproject.io/docs/secrets/databases/postgresql

This would allow my application to use rotating credentials to improve security.

I'm curious how this might be implemented with something like go-pg? As it stands right now the config object only supports providing a username and password once, and any hooks that are available or callbacks are after a connection has been made to the database. I could implement some code in my application that rotates the *pg.DB object that it is using periodically, swapping it out for one with a new config.

But I'd like to know if you'd be open to something like a BeforeConnect callback method that could be passed via the config that would allow credentials to be replaced or provided there as well. This way I could simply retrieve my rotating credentials right before a new database connection is established.

@vmihailenco thoughts?

[vmihailenco]

I believe the only practical way is to write an utility that restarts your services when password in Vault is changed. Go supports graceful restarts so the disruptance is minimal even if you rotate your passwords hourly.

I have not tried, but https://github.com/coreos/go-systemd can be helpful.

I could implement some code in my application that rotates the *pg.DB object that it is using periodically, swapping it out for one with a new config.

This is a possibility too as long as you don't use Listener. But sooner or later you will need to use it or some other package that can't be rotated this way.

But I'd like to know if you'd be open to something like a BeforeConnect callback method that could be passed via the config that would allow credentials to be replaced or provided there as well.

That is a lot of synchronization code that must close existing connections and replace them with new ones. Even just closing the connection pool and creating a new one is a lot of work. But in practice it is more than that (e.g. what to do with running queries). I would not go this way.

[nick567187]

Commenting here in support of pursuing a feature like this as I think many applications will start using dynamic credentials as the default. My use case is also with Vault and specifically the dynamic database credential generation feature it provides in the database secrets engine. We use agent injection to inject the secret as a json file in the container. As of now, have yet to find any research on people successfully doing this in a reliable way outside of literally re-deploying their service (old pod handles existing requests before shutdown and new pod handles incoming requests), looping to replace the *pg.DB object, or adding the beforeConnect callback.

You mention that rotating the *pg.DB object may work but I am having a hard time understanding how. Would the current object be placed in a temp variable so it is not garbage collected and we wait for idle connections to fizzle out or do we just replace it entirely and assume the queries go through successfully?

[vmihailenco]

do we just replace it entirely and assume the queries go through successfully?

You can just replace it and let garbage collector do the rest. It should not be a problem.

literally re-deploying their service (old pod handles existing requests before shutdown and new pod handles incoming requests),

Such approach takes few hours to setup, has no potential bugs, and will continue working no matter what libs you are using. Looks like an easy choice for me...

[nick567187]

Thanks for the quick response @vmihailenco I appreciate it. I will probably go with just using a cronJob to re-deploy the service with new creds before the existing creds expire

[justcompile]

I've just posted in the discord about this very thing, including the BeforeConnect terminology :) ! I'll be looking at implementing this and whilst it's not a guarantee, I'm thinking something long the lines of:

type Options struct {
    ...
    BeforeConnect func(context.Context, *Options) error
}

// Application logic

opts, err := pg.ParseURL(myConnectionString)
opts.BeforeConnect = func(ctx context.Context, o *pg.Options) error {
    token, err := mySecretProvider.GetAThing(...)
    if err != nil {
        return err
    }

    o.Password = token
    return nil
}

db, err := pg.Connect(opts)

[vmihailenco]

Sounds good. For the sake of others this API will not close existing connections after credentials are changed. Turns out it is enough for some use cases.

[justcompile]

Whilst a connection is open if the password / token / secret is changed, commands should still be successful (as is my understanding - I may be wrong). So the updated token would only be relevant for the next connection.

Though in order to maintain a bit of consistency one could set pg.Options.MaxConnAge to be inline with the tokens validity duration...with a bit of buffer

[elliotcourant]

This looks very cool, looking forward to seeing what you come up with.

[nick567187]

Nice! Looking forward to this as well! I'm curious what your use case is for implementing this via code as opposed to letting the infrastructure restart the service? Will have to expect every DB driver your company uses to be able to implement this feature. I went with the CronJob restarting the service route and it works fine for now but...I suspect there is occasionally a bug

[justcompile]

The use case I have is using IAM Authentication with Amazon AWS. As the tokens are only valid for 15 mins and I don't really want to be restarting pods every 15 mins 😄.

The CronJob approach is definitely a viable one, but for a number of internal reasons it would be a little too unreliable for us.