Supabase: hash passwords when adding to auth.users table to enable logging in.

[lockykeaney-convincely]

Feature request

Is your feature request related to a problem? Please describe.

When adding users to Supabase, the password is hashed on the database side as part of the createUser function. When seeding, that encryption can't be matched by bcrypt, resulting in an invalid password. Seeded users can't be used for manual testing.

Describe the solution you'd like

A way to access the postgres functions on the db instance, or ability to execute sql with the seed function:

const result = await seed.$db.query(`
       INSERT INTO auth.users (
            aud,
            email,
            encrypted_password,
            role,
            raw_app_meta_data
       ) VALUES (
            $1,
            $2,
            crypt($3, gen_salt('bf')),
            $4,
            $5
       ) RETURNING *
        `, [
          'Seed Test User',
          'seed-file@test.app',
          'SeedTest1234',
          'authenticated',
          {
            provider: 'email',
            providers: ['email'],
            claims_admin: true,
          }
        ]);

    return result.rows;
});

or

await seed.users([
    {
      aud: 'Seed Test User',
      email:  'seed-file@test.app',
      encrypted_password: seed.sql`crypt('SeedTest1234', gen_salt('bf'))`,
      role: 'authenticated',
      raw_app_meta_data: {
        provider: 'email',
        providers: ['email'],
        claims_admin: true,
      },
    },
  ]);

Describe alternatives you've considered

• Passing an sql file to the defineConfig function with some default users created with raw sql and make them available to the seed context
• Adding a migration to the db that checks if the password is encrypted, to try and mock the functionality of supabase directly on the db
• Creating a supabase client in the seed function, and adding the return values to the seed ctx somehow

[richardfwashington]

Hey. There is a way to do this. I've just had to grind through it myself and feel your pain!

The below can be used to generate a hash from a password that is compatible with supabase Auth.

async function hashPassword(password: string): Promise<string> {
  const saltRounds = 6;
  const salt = await bcrypt.genSalt(saltRounds);
  const hashedPassword = await bcrypt.hash(password, salt);
  return hashedPassword;
}

.....

 password: await hashPassword('password'),

Then using snaplet seed to generate the SQL file. We split ours up, so we have one we hand-crafted, followed by the one that gets spit out by snaplet.

The real problem you are going to hit on is that snaplet when it generates the SQL and tests to see if anything violates constraints. Sounds helpful of course. But does not work with Supabase auth.

In the 'users' table, there are multiple places where the expected value is an empty string OR a unique value. However, snaplet sees this as they must all be unique, so you can only seed a single user with, for example, no recovery_token.

See this thread: #191

I've put in a PR to fix it. Heard nothing from the maintainers https://github.com/supabase-community/seed/issues/191

I've put in a support request from Supabase who are now looking after the project.

Hopefully this is helpful!

We are pretty new to Supabase and are finding this area a real grind to be honest. I'm hopeful they can provide assistance/a better path.

[lockykeaney-convincely]

Thank you for the detailed help! Very close with the bcrypt idea.

We are also new to Supabase, so I have a feeling we might hit the same roadblocks as you. I will check out your PR and add any comments if I come across a similar situation. This library, and Supabase, has a lot of potential.

[ChuckJonas]

@richardfwashington do you have a full working example of seeding users.auth? Supabase really needs to at least update there docs.