Vault-Authenticated Databases

[thoward27]

Hey there folks!

I'm working on a small Loco project and am using credentials generated by vault to communicate with my database.

The issue I have is that all of the objects created by the migrator are owned by this temporary credential set, instead of the inherited role.

With Django, this problem is solved by https://github.com/jdelic/django-postgresql-setrole

I'm hoping to figure out how to do something similar with Loco.

My ideal outcome would be a setting that allows a user to specify a role. When the migrator runs, it would pick up this role and execute SET ROLE {role}.

This doesn't look possible in Loco right now. Is there a path forward? I can't figure out how to even plumb the config object through to the migrator from my project.

[jondot]

Thanks for the Django reference, it helps thinking about a solution!
Loco has a concept of "hooks" which means, you can "plug into" the Loco boot process if you want.

For this case, we want to run a raw SQL statement, a moment after the connection has been created.
Now, look into src/app.rs, where you have a trait for the various hooks you can use. The best place for this would be the after_context hook.

Example:

    async fn after_context(ctx: AppContext) -> Result<AppContext> {
         // .. use ctx.db here ..

        // remember to return ctx when you finish
         Ok(ctx)
    }

You can experiment with the various ways to run a raw statement here: https://www.sea-ql.org/SeaORM/docs/basic-crud/raw-sql/#use-raw-query--execute-interface

[thoward27]

I've finally had a chance to test this solution and unfortunately, it does not work. After adding the SET ROLE snippet in this function and running migrations, I am still left with objects owned by my token user. I will try the Migrator solution next.

[jondot]

For the migrator part, note that migrator also has a trait and functions you can override, the default in your project may look like this, in migration/src/lib.rs:

pub struct Migrator;

#[async_trait::async_trait]
impl MigratorTrait for Migrator {
    fn migrations() -> Vec<Box<dyn MigrationTrait>> {
        vec![
            Box::new(m20220101_000001_users::Migration),
            Box::new(m20231103_114510_notes::Migration),
        ]
    }
}

You can override Self::install(db), where you can get an instance of the db, do your raw SQL, and then call the original Self::install. All migrator functions call install before they continue.

[jondot]

(Note that only try this if you don't get the first solution in the comment working)

[jondot]

@thoward27 did you manage to get it resolved?