get_auth function missing from pgbouncer schema

[goldhaxx]

I can't access my project database via psql or by using the CLI.

I first discovered this when trying to link a local db instance to a hosted db instance. It failed. See detailed error output below.

supabase link --project-ref PROJECT_REF
No seed files matched pattern: supabase/seed.sql
Enter your database password (or leave blank to skip): 
Connecting to remote database...
failed to connect to postgres: failed to connect to `host=aws-0-us-west-1.pooler.supabase.com user=postgres.PROJECT_REF database=postgres`: server error (FATAL: Authentication error, reason: "Authentication query failed: %Postgrex.Error{message: nil, postgres: %{code: :undefined_function, file: \"parse_func.c\", hint: \"No function matches 
the given name and argument types. You might need to add explicit type casts.\", line: \"629\", message: \"function pgbouncer.get_auth(unknown) does not exist\", pg_code: \"42883\", position: \"15\", routine: \"ParseFuncOrColumn\", severity: \"ERROR\", unknown: \"ERROR\"}, connection_id: 277030, query: \"SELECT * FROM pgbouncer.get_auth($1)\"}" (SQLSTATE XX000))

After some research I discovered that the get_auth function of the pgbouncer schema is missing which is preventing me from being able to authenticate against the database.

Attached is a screenshot from Supabase Studio showing the pgbouncer schema on the broken database with no functions created.

Also attached is a screenshot from another database in another hosted project where the get_auth function exists.

I attempted to create this function using the web app SQL Editor, but got a response that my permission to do this is restricted.

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(username TEXT) RETURNS TABLE(username TEXT, password TEXT) AS $$
BEGIN
  RETURN QUERY SELECT usename::TEXT AS username, passwd::TEXT AS password FROM pg_catalog.pg_user WHERE usename = username;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

I need that function added back to the pgbouncer schema. would love to avoid having to migrate the tables, functions, data to a new db if possible 🙂

[voltuer]

same here, i used to be able to connect to my DB, but no i can't anymore because the pgbouncer.get_auth function apparently no longer exists

i guess the good fellas from supabase messed something up, need more testing before deploying i think

[goldhaxx]

I reached out to support via email and they resolved the issue for me.

Here is the information I entered in my ticket:

`Ticket name: get_auth function missing from pgbouncer schema
Project reference: YOUR_PROJECT_REFERENCE
Organization slug: YOUR_ORGANIZATION_SLUG
Allow support access: true
Severity: Normal
Type: Database unresponsive
Ticket description: I first discovered this when trying to link a local db instance to a hosted db instance. It failed. See detailed error output below.

After some research I discovered that the get_auth function of the pgbouncer schema is missing which is preventing me from being able to authenticate against the database.

Attached is a screenshot from Supabase Studio showing the pgbouncer schema on the broken database with no functions created.

Also attached is a screenshot from another database in another hosted project where the get_auth function exists.

supabase link --project-ref PROJECT_REF
No seed files matched pattern: supabase/seed.sql
Enter your database password (or leave blank to skip):
Connecting to remote database...
failed to connect to postgres: failed to connect to host=[aws-0-us-west-1.pooler.supabase.com](http://aws-0-us-west-1.pooler.supabase.com/) user=postgres.PROJECT_REF database=postgres: server error (FATAL: Authentication error, reason: "Authentication query failed: %Postgrex.Error{message: nil, postgres: %{code: :undefined_function, file: "parse_func.c", hint: "No function matches
the given name and argument types. You might need to add explicit type casts.", line: "629", message: "function pgbouncer.get_auth(unknown) does not exist", pg_code: "42883", position: "15", routine: "ParseFuncOrColumn", severity: "ERROR", unknown: "ERROR"}, connection_id: 277030, query: "SELECT * FROM pgbouncer.get_auth($1)"}" (SQLSTATE XX000))

I attempted to create this function using the web app SQL Editor, but got a response that my permission to do this is restricted.

CREATE OR REPLACE FUNCTION pgbouncer.get_auth(username TEXT) RETURNS TABLE(username TEXT, password TEXT) AS $$
BEGIN
RETURN QUERY SELECT usename::TEXT AS username, passwd::TEXT AS password FROM pg_catalog.pg_user WHERE usename = username;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

I need that function added back to the pgbouncer schema.`

[sabatage3]

I'm having this same issue, but since i have a free plan I'm not sure if they will even answer me, is there something I can do?, can't even connect to the database to generate a backup, please I need help, my project is already in production and I can't connect to the storage because of this get_auth function missing

[goldhaxx]

Reach out to support they'll fix it

[sabatage3]

Yes! they did! I thought I wasn't going to get an answer at all, thank you!