PostgresError: password authentication failed for user.
I am getting error while trying to insert a new user to the DB.
scheme.ts file,db.ts,migrate.ts,drizzle.config.ts,.env file in which I have this,postgresql in a docker container, here is the docker-compose,system user..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.process.env.DB_URL is loaded correctly?drizzle.config.ts and see if it prints the desired valueDB_URL its DATABASE_URL, I have no idea how it got changed..drizzle.config.ts?process.env is available. Vite has a bit of it's own logic when handling environment variablesdrizzle.config.ts,migrate.ts file inherit this from drizzle.config.ts?defineConfig but seems that dbCredentials are not exposedVITE_ and see if it helps when running with Vite.env file and I use the dotenv package I think..env file where you define the DATABASE_URL and change in the db.ts where you use it, and change it to:DATABASE_URL to VITE_DATABASE_URL in .env file and use import.meta.env.VITE_DATABASE_URL in db.ts instead?drizzle.config.ts as well I guess? otherwise how would it pick it up?.env file,db.ts I did,VITE_ stay in client-side codeVITE_ prefix can be removed thenscheme.tsmigrate.tsmigrate.tsDATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzlepostgresqldocker-compose npm run dev
> sonora@0.0.1 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", "foo1@bar.com", "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.0systempsql postgres://drizzle:drizzle@localhost/drizzledocker exec -it drizzle-db psql -U drizzle -d drizzlepsqldrizzle=# 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=# process.env.DB_URLDB_URL npm run db:generate
> sonora@0.0.1 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 🚀
~/repos/sonora via v18.19.0
npm run db:migrate
> sonora@0.0.1 db:migrate
> tsx src/lib/database/migrate.ts npm run dev
> sonora@0.0.1 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", "foo1@bar.com", "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.0defineConfigdbCredentialsVITE_VITE_VITE_dotenvVITE_DATABASE_URLimport.meta.env.VITE_DATABASE_URLDATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle
VITE_DATABASE_URL=postgres://drizzle:drizzle@localhost:5432/drizzle npm run dev
> sonora@0.0.1 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", "foo1@bar.com", "foo1bar1", "asdads"]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 | foo1@bar.com | | foo1bar1 | asdads | USER | | 2024-10-01 17:55:55.456252+00
(1 row)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 { 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 "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 { 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
});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:rwimport { 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 { create_user } from "$lib/database/queries/users";
import type { PageLoad } from "./$types";
export const load = (() => {
let user = create_user({
username: "foo1",
email: "foo1@bar.com",
password: "foo1bar1",
role: ["USER"],
});
return {
user
}
}) satisfies PageLoad;const migrationClient = postgres(process.env.DATABASE_URL as string, { max: 1 });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 { default as defaultConfig } from "./drizzle.config";
defaultConfig.dbCredentials.url // Unresolved "dotenv": "^16.4.5",import.meta.env.VITE_DATABASE_URLimport { 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 });