DT
Drizzle Team•3mo ago
CyberCipher

PostgresError: password authentication failed for user.

I am getting error while trying to insert a new user to the DB.
27 Replies
CyberCipher
CyberCipherOP•3mo ago
Here is my scheme.ts file, 
import { relations } from "drizzle-orm";
import { date, integer, pgSchema, primaryKey, text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";

export const schema = pgSchema("core");

export const UserRole = schema.enum("user_role", ["ADMIN", "USER"]);

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username").notNull().unique(),
    email: varchar("email").notNull(),
    token: varchar("token").unique(),
    hashed_password: varchar("hashed_password").notNull().unique(),
    avatar_url: varchar("avatar_url"),
    role: UserRole("user_role").notNull().default("USER"),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        email_index: uniqueIndex("email_index").on(table.email)
    }
});
import { relations } from "drizzle-orm";
import { date, integer, pgSchema, primaryKey, text, timestamp, uniqueIndex, uuid, varchar } from "drizzle-orm/pg-core";

export const schema = pgSchema("core");

export const UserRole = schema.enum("user_role", ["ADMIN", "USER"]);

export const User = schema.table("user", {
    id: uuid("id").primaryKey().notNull().unique().defaultRandom(),
    username: varchar("username").notNull().unique(),
    email: varchar("email").notNull(),
    token: varchar("token").unique(),
    hashed_password: varchar("hashed_password").notNull().unique(),
    avatar_url: varchar("avatar_url"),
    role: UserRole("user_role").notNull().default("USER"),
    last_login: timestamp("last_login", { withTimezone: true }),
    created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
}, (table) => {
    return {
        email_index: uniqueIndex("email_index").on(table.email)
    }
});
here is db.ts, 
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(process.env.DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });
here is migrate.ts, 
import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()
import "dotenv/config";
import { drizzle } from 'drizzle-orm/postgres-js';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import postgres from 'postgres';

const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });

async function main() {
    await migrate(drizzle(migrationClient), {
        migrationsFolder: "./src/lib/database/migrations",
    })

    await migrationClient.end()
}

main()
here is my drizzle.config.ts, 
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DB_URL as string,
    },
    verbose: true,
    strict: true
});
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DB_URL as string,
    },
    verbose: true,
    strict: true
});
I have a .env file in which I have this, 
DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
I run postgresql in a docker container, here is the docker-compose, 
volumes:
  drizzle-db:

services:
  drizzle-db:
    container_name: drizzle-db
    image: postgres:alpine
    environment:
      POSTGRES_DB: drizzle
      POSTGRES_PASSWORD: drizzle
      POSTGRES_USER: drizzle
    ports:
      - "5432:5432"
    volumes:
      - drizzle-db:/var/lib/postgresql/data:rw
volumes:
  drizzle-db:

services:
  drizzle-db:
    container_name: drizzle-db
    image: postgres:alpine
    environment:
      POSTGRES_DB: drizzle
      POSTGRES_PASSWORD: drizzle
      POSTGRES_USER: drizzle
    ports:
      - "5432:5432"
    volumes:
      - drizzle-db:/var/lib/postgresql/data:rw
now I wrote a simple query for inserting a new user, 
import { db } from "../db"
import { User } from "../schema"

interface NewUser {
    username: string,
    email: string,
    password: string,
    role: Array<string | null>,
    avatar_url: string | null,
}

export async function create_user(new_user: NewUser) {
    await db.insert(User).values({
        username: new_user.username,
        email: new_user.email,
        hashed_password: new_user.password,
        // avatar_url: new_user.avatar_url
    }).returning()
}
import { db } from "../db"
import { User } from "../schema"

interface NewUser {
    username: string,
    email: string,
    password: string,
    role: Array<string | null>,
    avatar_url: string | null,
}

export async function create_user(new_user: NewUser) {
    await db.insert(User).values({
        username: new_user.username,
        email: new_user.email,
        hashed_password: new_user.password,
        // avatar_url: new_user.avatar_url
    }).returning()
}
and I have this, 
import { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";

export const load = (() => {
    let user = create_user({
        username: "foo1",
        password: "foo1bar1",
        role: ["USER"],
    });

    return {
        user
    }
}) satisfies PageLoad;
import { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";

export const load = (() => {
    let user = create_user({
        username: "foo1",
        password: "foo1bar1",
        role: ["USER"],
    });

    return {
        user
    }
}) satisfies PageLoad;
when I run my project, it starts fine but as soon as I try to go the URL the server crashes and I get this error, 
  npm run dev

> vite dev


  VITE v5.4.8  ready in 915 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, default, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "[email protected]", "foo1bar1"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0
  npm run dev

> vite dev


  VITE v5.4.8  ready in 915 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, default, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "[email protected]", "foo1bar1"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0
Am I doing something wrong? Not sure why its trying to authenticate using my system user.. I can do psql postgres://drizzle:drizzle@localhost/drizzle or docker exec -it drizzle-db psql -U drizzle -d drizzle both works, I get dropped in the psql shell. 
drizzle=# select * from core.
core."user"                core.artist                core.artist_genre          core.playlist              core.record_label
core.album                 core.artist_collaboration  core.genre                 core.playlist_track        core.track
drizzle=# select * from core.user;
 id | username | email | token | hashed_password | avatar_url | user_role | last_login | created_at 
----+----------+-------+-------+-----------------+------------+-----------+------------+------------
(0 rows)

drizzle=# 
drizzle=# select * from core.
core."user"                core.artist                core.artist_genre          core.playlist              core.record_label
core.album                 core.artist_collaboration  core.genre                 core.playlist_track        core.track
drizzle=# select * from core.user;
 id | username | email | token | hashed_password | avatar_url | user_role | last_login | created_at 
----+----------+-------+-------+-----------------+------------+-----------+------------+------------
(0 rows)

drizzle=#
migration ran successfully, so I'm guessing the problem is not with connection to DB?
Kuba
Kuba•3mo ago
Are you sure process.env.DB_URL is loaded correctly? Add a console log to drizzle.config.ts and see if it prints the desired value
Kuba
Kuba•3mo ago
If not, read on how env vars are handled by Vite: https://vitejs.dev/guide/env-and-mode.html
vitejs
Env Variables and Modes
Next Generation Frontend Tooling
CyberCipher
CyberCipherOP•3mo ago
If it wouldn't, how would the migration happen? But I'll double check. Oh! wait.. I just noticed.. its not DB_URL its DATABASE_URL, I have no idea how it got changed.. in that case, I guess the migration one doesn't gets the URL from drizzle.config.ts?
Kuba
Kuba•3mo ago
The migration runs with Node.js, where process.env is available. Vite has a bit of it's own logic when handling environment variables Well, it doesn't seem to as you've defined the client explicitly with: 
const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });
const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });
CyberCipher
CyberCipherOP•3mo ago
drizzle.config.ts, 
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL as string,
    },
    verbose: true,
    strict: true
});

console.log(`DATABASE_URL: ${process.env.DATABASE_URL as string}`);
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
    schema: "./src/lib/database/schema.ts",
    out: "./src/lib/database/migrations",
    dialect: "postgresql",
    dbCredentials: {
        url: process.env.DATABASE_URL as string,
    },
    verbose: true,
    strict: true
});

console.log(`DATABASE_URL: ${process.env.DATABASE_URL as string}`);
  npm run db:generate

> [email protected] db:generate
> drizzle-kit generate --name=drizzle

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/apoorv/repos/sonora/drizzle.config.ts'
DATABASE_URL: postgres://drizzle:drizzle@localhost:5432/drizzle
10 tables
album 6 columns 2 indexes 2 fks
artist 3 columns 0 indexes 0 fks
artist_collaboration 2 columns 0 indexes 2 fks
artist_genre 2 columns 0 indexes 2 fks
genre 3 columns 0 indexes 0 fks
playlist 4 columns 1 indexes 1 fks
playlist_track 3 columns 2 indexes 2 fks
record_label 3 columns 0 indexes 0 fks
track 6 columns 2 indexes 2 fks
user 9 columns 1 indexes 0 fks

[✓] Your SQL migration file ➜ src/lib/database/migrations/0000_drizzle.sql :rocket:

~/repos/sonora via  v18.19.0 
  npm run db:migrate 

> [email protected] db:migrate
> tsx src/lib/database/migrate.ts
  npm run db:generate

> [email protected] db:generate
> drizzle-kit generate --name=drizzle

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/apoorv/repos/sonora/drizzle.config.ts'
DATABASE_URL: postgres://drizzle:drizzle@localhost:5432/drizzle
10 tables
album 6 columns 2 indexes 2 fks
artist 3 columns 0 indexes 0 fks
artist_collaboration 2 columns 0 indexes 2 fks
artist_genre 2 columns 0 indexes 2 fks
genre 3 columns 0 indexes 0 fks
playlist 4 columns 1 indexes 1 fks
playlist_track 3 columns 2 indexes 2 fks
record_label 3 columns 0 indexes 0 fks
track 6 columns 2 indexes 2 fks
user 9 columns 1 indexes 0 fks

[✓] Your SQL migration file ➜ src/lib/database/migrations/0000_drizzle.sql :rocket:

~/repos/sonora via  v18.19.0 
  npm run db:migrate 

> [email protected] db:migrate
> tsx src/lib/database/migrate.ts
but once again it crashes, 
  npm run dev       

> vite dev


  VITE v5.4.8  ready in 891 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "[email protected]", "foo1bar1", "asdads"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0
  npm run dev       

> vite dev


  VITE v5.4.8  ready in 891 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values (default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at" -- params: ["foo1", "[email protected]", "foo1bar1", "asdads"]
node:internal/process/promises:288
            triggerUncaughtException(err, true /* fromPromise */);
            ^

PostgresError: password authentication failed for user "apoorv"
    at ErrorResponse (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:788:26)
    at handle (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:474:6)
    at Socket.data (file:///home/apoorv/repos/sonora/node_modules/postgres/src/connection.js:315:9)
    at Socket.emit (node:events:517:28)
    at addChunk (node:internal/streams/readable:368:12)
    at readableAddChunk (node:internal/streams/readable:341:9)
    at Readable.push (node:internal/streams/readable:278:10)
    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
    at TCP.callbackTrampoline (node:internal/async_hooks:128:17) {
  severity_local: 'FATAL',
  severity: 'FATAL',
  code: '28P01',
  file: 'auth.c',
  line: '321',
  routine: 'auth_failed'
}

Node.js v18.19.0
I see. Can this migrate.ts file inherit this from drizzle.config.ts?
Kuba
Kuba•3mo ago
I don't know to be honest. rn I tried to import the result of defineConfig but seems that dbCredentials are not exposed 
import { default as defaultConfig } from "./drizzle.config";

defaultConfig.dbCredentials.url  // Unresolved
import { default as defaultConfig } from "./drizzle.config";

defaultConfig.dbCredentials.url  // Unresolved
I didn't run it though I have it defined separately also, and I don't think these will change anytime in the project lifespan tbh Sometimes it's good to settle on "good enough" Back to the issue, how do you load environment variables? Do you have an env file with this var defined? Try prefixing it with VITE_ and see if it helps when running with Vite
CyberCipher
CyberCipherOP•3mo ago
Yes, a .env file and I use the dotenv package I think. 
        "dotenv": "^16.4.5",
        "dotenv": "^16.4.5",
in which file? everywhere?
Kuba
Kuba•3mo ago
Nope, just in .env file where you define the DATABASE_URL and change in the db.ts where you use it, and change it to: 
import.meta.env.VITE_DATABASE_URL
import.meta.env.VITE_DATABASE_URL
CyberCipher
CyberCipherOP•3mo ago
you mean rename the DATABASE_URL to VITE_DATABASE_URL in .env file and use import.meta.env.VITE_DATABASE_URL in db.ts instead? I would also have to change the same in drizzle.config.ts as well I guess? otherwise how would it pick it up? so I have now these 2 fields in .env file, 
DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
Kuba
Kuba•3mo ago
yes For now, let's just test out if it works for Vite
CyberCipher
CyberCipherOP•3mo ago
interesting, in db.ts I did, 
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(import.meta.env.VITE_DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "./schema";

const client = postgres(import.meta.env.VITE_DATABASE_URL as string);

export const db = drizzle(client, { schema, logger: true });
and it looks like its not crashing anymore.. 
  npm run dev

> vite dev


  VITE v5.4.8  ready in 942 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", last_login", "created_at" -- params: ["foo1", "[email protected]", "foo1bar1", "asdads"]
  npm run dev

> vite dev


  VITE v5.4.8  ready in 942 ms

  ➜  Local:   http://localhost:5173/
  ➜  Network: use --host to expose
  ➜  press h + enter to show help
Query: insert into "core"."user" ("id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", "last_login", "created_at") values default, $1, $2, default, $3, $4, default, default, default) returning "id", "username", "email", "token", "hashed_password", "avatar_url", "user_role", last_login", "created_at" -- params: ["foo1", "[email protected]", "foo1bar1", "asdads"]
Kuba
Kuba•3mo ago
Soo as I suspected, I recommend giving thorough read to the linked docs
CyberCipher
CyberCipherOP•3mo ago
and I do infact have a new user, 
drizzle=# select * from core.user;
                  id                  | username |    email     | token | hashed_password | avatar_url | user_role | last_login |          created_at           
--------------------------------------+----------+--------------+-------+-----------------+------------+-----------+------------+-------------------------------
 e7552979-fc72-406b-8158-da06afd7e5c1 | foo1     | [email protected] |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)
drizzle=# select * from core.user;
                  id                  | username |    email     | token | hashed_password | avatar_url | user_role | last_login |          created_at           
--------------------------------------+----------+--------------+-------+-----------------+------------+-----------+------------+-------------------------------
 e7552979-fc72-406b-8158-da06afd7e5c1 | foo1     | [email protected] |       | foo1bar1        | asdads     | USER      |            | 2024-10-01 17:55:55.456252+00
(1 row)
Kuba
Kuba•3mo ago
Just be aware that there's a security implication, as if you bundle/build your application, all variables exposed with VITE_ stay in client-side code Which means that every browser can inspect the connection string
CyberCipher
CyberCipherOP•3mo ago
How can I handle this better?
Kuba
Kuba•3mo ago
Build an API for that 😛 Do you use some frontend framework?
CyberCipher
CyberCipherOP•3mo ago
I am using sveltekit.
Kuba
Kuba•3mo ago
Hmm, I'm not that familiar with sveltekit, but I assume that there's a way to run this code server-side
CyberCipher
CyberCipherOP•3mo ago
Ah! I see what you mean. I'll ask in svelte discord channel for this.
Kuba
Kuba•3mo ago
essentialy you don't won't to expose any confidential secret to be exposed to the client So the drizzle client should be initialized on the server-side, and then the VITE_ prefix can be removed then
CyberCipher
CyberCipherOP•3mo ago
I see. Perhaps I should move the drizzle client config to svelte's "backend" thing..
Kuba
Kuba•3mo ago
Definitely
CyberCipher
CyberCipherOP•3mo ago
I'm also learning sveltekit BTW. I chose this framework as I wanted to learn it.
Kuba
Kuba•3mo ago
Heard good things about Svelte. I'm a React/Next.js guy myself, but a lot of principles stay the same Good luck with the project, I think you'll handle it from there
CyberCipher
CyberCipherOP•3mo ago
Thanks! @Kuba
Kuba
Kuba•3mo ago
np, happy to help
Want results from more Discord servers?
Add your server