supabase: SASL_SIGNATURE_MISMATCH: The server did not return the correct signature

Bug report

  • I confirm this is a bug with Supabase, not with my own application.
  • I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I recently switched from a local postgres database to supabase and using drizzle as the ORM.

This is the drizzle configuration (the app is a discord bot, so long-running)

import * as schema from '../schema';
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from "postgres";

export const sql = postgres({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  max: 1,
})

export const db = drizzle(sql, { schema });

EDIT: All these throw a connection error, whether it is pg or postgres

const client = postgres(import.meta.env.DATABASE_URL, {
  ssl: {
    rejectUnauthorized: false,
   },
})
// SASL_SIGNATURE_MISMATCH

const client = postgres(import.meta.env.DATABASE_URL, {
  ssl: {
    rejectUnauthorized: false,
    cert,
   },
})
// SASL_SIGNATURE_MISMATCH

const client = postgres(import.meta.env.DATABASE_URL, {
  ssl: { cert },
})
// SELF_SIGNED_CERT_IN_CHAIN

const client = postgres(import.meta.env.DATABASE_URL, {
  ssl: {
    rejectUnauthorized: false,
    ca: cert,
   },
})
// SASL: SCRAM-SERVER-FINAL-MESSAGE: server signature is missing

On the first request to the database I get the following:

app-1  | 33 |   return error
app-1  | 34 | }
app-1  | 35 | 
app-1  | 36 | function generic(code, message) {
app-1  | 37 |   const error = Object.assign(new Error(code + ': ' + message), { code })
app-1  | 38 |   Error.captureStackTrace(error, generic)
app-1  |              ^
app-1  | error: SASL_SIGNATURE_MISMATCH: The server did not return the correct signature
app-1  |  code: "SASL_SIGNATURE_MISMATCH"
app-1  | 
app-1  |       at generic (/usr/src/app/node_modules/postgres/src/errors.js:38:9)
app-1  |       at SASLFinal (/usr/src/app/node_modules/postgres/src/connection.js:721:19)
app-1  |       at /usr/src/app/node_modules/postgres/src/connection.js:14:16
app-1  |       at asyncFunctionResume (native:1:1)
app-1  |       at Authentication (/usr/src/app/node_modules/postgres/src/connection.js:9:1)
app-1  |       at handle (/usr/src/app/node_modules/postgres/src/connection.js:813:5)
app-1  |       at data (/usr/src/app/node_modules/postgres/src/connection.js:553:5)
app-1  |       at addChunk (native:1:1)
app-1  |       at readableAddChunk (native:1:1)
app-1  |       at data (native:1:1)
app-1  | error: script "start" exited with code 1
app-1 exited with code 1

However, if use a connection string with the exact same values (env variables joined in the format of the connection string) the connection works.

I’d prefer to use the object notation in the postgres() connection options

To Reproduce

Source code can be found here https://github.com/noook/discord-mbti

Expected behavior

Object notation should behave the same as the connection string

Screenshots

Database connection on supabase dashboard image

System information

  • OS: Linux (docker)
  • Version of supabase-js: not used
  • bun 1.0.29

Additional context

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 8
  • Comments: 47 (6 by maintainers)

Most upvoted comments

thank you all !!! sounds stupid! I corrected this by removing the brackets in the database link URL for [password].🤦🤦🤦

Hi there, if it is working with the connection string but not the individual parameters, then there is likely some issue with the library. I would recommend using the connection string when that works. If you do need to use the parameters, then you may need to add additional options, such as:

ssl: { rejectUnauthorized: false}

Confirming this. I had the same issue because my DB password contained $ and & in it. I reset it and it is working fine now.

I had the same issue because my password start with $. After change my password, works fine with this setup:

// .env.local
NEXT_PUBLIC_SUPABASE_URL=postgres://postgres.jkjkkjkjkjk:PASSWORD@aws-0-sa-east-1.pooler.supabase.com:6543/postgres
// db.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = process.env.NEXT_PUBLIC_SUPABASE_URL!;

export const client = postgres(connectionString, { prepare: false });
export const db = drizzle(client);

I had the same issue because my password start with $. After change my password, works fine with this setup:

// .env.local
NEXT_PUBLIC_SUPABASE_URL=postgres://postgres.jkjkkjkjkjk:PASSWORD@aws-0-sa-east-1.pooler.supabase.com:6543/postgres
// db.ts
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";

const connectionString = process.env.NEXT_PUBLIC_SUPABASE_URL!;

export const client = postgres(connectionString, { prepare: false });
export const db = drizzle(client);

Faced the same issue. Solved by using psql instead of postgres in prefix of connection string

seems like mine was fixed when i generated password through supabase’s “generated password” instead of custom password

sorry to necro your closed issue but since it was not really resolved with documentation i hope you wont mind.

for anyone else stumbling here during troubleshooting, I confirmed that this issue is likely caused by a version mismatch between the client’s pg version(I tested 14) and the remote (v15 as of now).

I’m not sure why it isn’t observed in psql (which will warn about the outdated client and does confirm TLS) or why so many people seem to be running into this at once. So, it does seem to be an issue with either Drizzle or possibly the database driver. You can, as @encima suggested, disable SSL as a workaround but in any case, it’s ideal to upgrade your client if its outdated. (and resolve the SSL issues as a win-win…)

# Homebrew upgrade

# brew uninstall postgresql
brew update && brew install postgresql@16
pushd /usr/local/opt/postgresql@16/bin

# If you'd prefer not to modify your $PATH
# for file in *; do ln -s "$(pwd)/$file" /usr/local/bin/; done
popd
psql --version

So learnings…never let your password manager generate the db password.

I have just been dealing with this issue. What was strange was I had Enforce SSL on incoming connections disabled. Enabling this and then disabling it fixed the issue for me. Very odd!

So to people reading this: tripple check your password. Also consider if you need to use port 5432 instead of the 6543 that was mentioned in the supabase ‘connect’ dialog.

Was struggling with drizzle-kit pull giving this error, got it fixed by changing the port from 6543 to 5432.

This should be mentioned in the “Connect to your project” > “ORMs” > “Drizzle” section, as it currently is on the “Prisma” section:

Drizzle section:

Image

Prisma section (notice the port change):

Image

One more thing guys, if you have @ symbol or #, watch out, @ symbol is what supabase/postgres uses to differentiate passwords and other info in the url. So in general I’d use only dashes or exclamation marks.

OK, to summarise this:

  1. When using postgres tools and libraries, ensure they are the same (or higher) than the version of your target database
  2. When using special characters, be sure to encode them

check if psql --version is >= 15

also, since you are getting password issues, try e.g.:

import "dotenv/config";
const {
  PGHOST: host,
  PGUSER: user,
  PGDATABASE: database,
  PGPASSWORD: password,
} = process.env;

export const connectionString = `postgresql://${user}:${encodeURIComponent(password)}@${host}`;

// if you need params later…
// const params = new URLSearchParams(Object.entries({}).map(([k, v]) => [k, v.toString()]));
// export const connectionString = `${url}?${params}`;

(this is a Drizzle issue I have noticed. Maybe you’ve run into it too if supabase happened to generate a password that has URI breaking characters which must be encoded.)

Still getting this issue. I’m connecting to Supabase via the pooler from a cloudflare worker. I’m using Drizzle with the postgres.js client.

Things I’ve tried:

  1. Changing the password and restarting the DB.
  2. Making sure the password doesn’t contain characters or the square brackets that might break a parser somewhere.
  3. Connecting from a local-machine node server (Works!).
  4. Connecting from a local-machine CF worker using wrangler dev (Doesn’t Work!).
  5. Connecting from an actual CF worker (doesn’t work).
  6. Tried enabling and disabling the Enforce SSL on incoming connections property. When enabled, the error changed to “SERVER_ERROR: PostgresError: SSL connection is required”. But when I disabled it again, it went back to “SERVER_ERROR: Error: SASL_SIGNATURE_MISMATCH: The server did not return the correct signature”. So that didn’t work either.

I ran into this problem, and the cause was a small typo in my password while changing the connection string after creating a new supabase project.

So to people reading this: tripple check your password. Also consider if you need to use port 5432 instead of the 6543 that was mentioned in the supabase ‘connect’ dialog.

off topic: I wish there was an easier way to change region of a project, that does not involve creating a new project and a bunch of hassle.

Unbelievably it worked for me by wrapping my environment variable in double quotes in my .env!! I’m using Strapi, there must be something wrong with their environment variable parsing function.

No need to apologise, this is useful troubleshooting advice! I’ll add it to the docs tomorrow

I had a similar problem and all I had to do is to change the password for my DB. I am using

    "pg": "^8.13.0",
    "typeorm": "^0.3.20"
    "@nestjs/cli": "^10.0.0",

In the pooler logs, I was getting "ClientHandler: Exchange error: \"Wrong password\" when method :auth_query"

I had the exact same issue so i did log into the postgres lib at this line: https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/crypto/sasl.js#L154

for me it said “e=Wrong password” So I changed the db password to a new one manually - did not help Then I changed the db password to a new one with the build in password generator - worked

Thanks, this worked for me too

I had the exact same issue so i did log into the postgres lib at this line: https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/crypto/sasl.js#L154

for me it said “e=Wrong password” So I changed the db password to a new one manually - did not help Then I changed the db password to a new one with the build in password generator - worked

Spent an hour debugging it, here is the result of my investigation

@encima Here is my project reference id: wiowhiztwaqjqzlfqqsh

I changed password a few times because when debugging I reached the pooler logs page and noticed logs such as: ClientHandler: Exchange error: "Wrong password" when method :auth_query immediately followed by ClientHandler: socket closed with reason {:shutdown, :exchange_error} I guess the error that I see in the terminal is only the last one, when the real error was maybe a password issue.

I changed it multiple time and made sure it was correctly set (console.log in node app) to see how it was read from the .env file.

I tried a few ones, and finally made it by using a password with no special character. -> Might this be the issue ?

Can you confirm the connection string is for your database and not for the connection pooler? I’m not sure I can tell the difference between the two. I’m using the connection string that I found under Project Settings > Database > Connection string.

It looks like this: postgres://postgres.wiowhiztwaqjqzlfqqsh:[YOUR-PASSWORD]@aws-0-eu-central-1.pooler.supabase.com:5432/postgres

The dashboard looks like this: image

Trying @binury connection with psql, it works correctly.