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

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 4 months ago
Reactions: 3
Comments: 16 (4 by maintainers)

Most upvoted comments

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}

encima on Mar 4, 2024

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

vicvinc on Mar 11, 2024

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);

martinacostadev on Mar 12, 2024

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

binury on Mar 10, 2024

OK, to summarise this:

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

encima on Mar 11, 2024

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:

Trying @binury connection with psql, it works correctly.

noook on Mar 10, 2024

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

encima on Mar 10, 2024