"Database error granting user" on sign up/sign in

[nstrelow]

I have been very inconsistently getting a this error "Database error granting user" with a 500 HTTP status code.

Sometimes I try it a bit more and then it starts working again.

Has anyone had that before?

[kiwicopple]

Hey @nstrelow - i know you said that it's inconsistent, but is there any way that you think we can reproduce this? Is it happening with one of the examples that we recommend in our documentation?

[nstrelow]

Sorry, I have no way of reproducing, I will try and write down what happened, the next time I have it.

Feel free to close this for more clarity and I'll reopen if I come across it again.

Left this here to gather reports of similar problems, so we can gather more info.

[kiwicopple]

OK sure! I'll close but feel free to drop comments and I can reopen to debug once we have more info

[nstrelow]

It is happening again and it is happening on every auth actions, such as sign up/in with provider or not.

Any idea what could cause this and how I can fix it?

Also happening from different domains.

Could I have unwillingly messed up something in the database?

[nstrelow]

OMG, it was not going away until I dropped the auth.email() function I created (copied from supabase/supabase#1857).
But when I recreated it, the bug does not reappear. This is so weird.

Commands

create or replace function auth.email() returns text as $$
  select nullif(current_setting('request.jwt.claim.email', true), '')::text;
$$ language sql;

drop function auth.email

[kiwicopple]

Hey @nstrelow - is it possible you're modifying this function somehow? Or do you have any triggers on your tables which could be blocking the function from working correctly?

[nstrelow]

Hmm it is still happening.

Just now it failed with said error. After trying to login (via Gitlab) 5 times it worked.

I still had the auth.email function, but was not using it. Will leave it deleted and see if it has to do with that function or if it's something else.

[nstrelow]

This has now costed me a reject in the apple app store 😢

They said login is not working, but it was that error again.

I still have this 1-2 times a day, where I need to logging around 5 times until it finally accepts my login

Any idea?
Still using the dart SDK in a Flutter Web application.
It is happening for normal and provider logins.

Something with the JTW token or so?
Or some race condition?

[kiwicopple]

Hi @nstrelow - I will reopen this, but we'll need more help to debug this with you. We aren't seeing this issue with other projects (as far as we can determine).

• "some race condition." Is there something in your code that is logging the client out before it has a chance to log in?

• "using the dart SDK in a Flutter Web application." Are you seeing the same thing when you call the URLs directly, or using the supabase-js library?

• "Database error granting user". Have you modified the auth schema? Are you using any triggers?

• If you run this on a brand-new project, do you get the same error?
• can you share your full schema?

It's very hard to determine where the error is without a way to replicate the error on our side - I suspect it's a code issue (the dart SDK, your application code, schema changes) rather than an infra issue, but that's just a hunch since we aren't seeing the problem occur with other users.

If we can narrow-down the problem landscape, we can dig a lot deeper.

[nstrelow]

I migrated to a new database yesterday and hope that fixed it.
I am currently short on time so if that fixed it I will not pursue this issue anymore.

If it reoccurs, I'll try some additional steps.

I have a trigger function, which creates an entry inside a public.users schema, when a new user is created. That should be the only thing running, when I sign in.
Codewise I am just using client.auth.signIn(...), so I don't know where something could go wrong here.

I just published the schema sql to Github under https://github.com/hpi-studyu/studyu (the cleaned one for the new database).

Otherwise, a while ago, I accidentally restored a backup of Supabase back to Supabase using pgAdmin (and user postgres). This might have caused the problem.

@kiwicopple I really appreciate your help, but I would agree, that a single user having some problem is probably the users problem and not one of supabase. So please focus on the more generally helpful features and issues 😁

E.g. I am very interested in a complete and easy self hosted setup, since my app handles medical data and a lot of institutions want to self host.
Would also offer my help, but I am sandwiched between finishing my master thesis and my job start in August 🙈

[sturatcliffe]

Hi @kiwicopple, @nstrelow - your issue could well be down to the trigger you have on the auth.users table, or more specifically a privileges issue on that trigger being able to fire.

I've started seeing similar errors, initially it was the same as you - Database error granting user - but since I've tried to rule out issues with my supabase project (I've been messing around with privileges while trying out the alpha release of function hooks), I've moved to a fresh project with no existing users, and I now can't register. The error being thrown is Database error saving new user

It's definitely the hook I have cloning users from the auth to public schema, as if I disable it everything works perfectly. I suspect my issue is that I'm creating my database structure using the node-pg-migrate tool, including the trigger on the auth schema. The function the trigger invokes is created using SECURITY DEFINER, which in this case is the postgres role that the migrations tool uses to authenticate, and I guess although that role can create the trigger, there's an issue when it comes to running it. I also can't delete the trigger in the down method of my migration, as when I try to run it I get a permissions error that postgres isn't the owner of the table.

@kiwicopple any ideas on how to resolve? Should I / we be using a different user for running migrations? I guess it would be best to use the same user the dashboard uses, but I can't find the password for it anywhere.

Cheers.

[sturatcliffe]

Some further info on my issue:

I've stripped my migration right back to the bare minimum to get my auth flow working, and generated a SQL script that I can run from the dashboard to rule out the postgres role being the issue.

This is the script I'm running:

CREATE TABLE public.audits
(
    id SERIAL PRIMARY KEY,
    tbl text COLLATE pg_catalog."default" NOT NULL,
    row_id text COLLATE pg_catalog."default" NOT NULL,
    before jsonb,
    after jsonb,
    "timestamp" timestamp with time zone DEFAULT now(),
    user_id uuid
);

CREATE TABLE public.users
(
    id uuid NOT NULL,
    email_verified boolean NOT NULL DEFAULT false,
    email_verification_code text COLLATE pg_catalog."default",
    phone_verified boolean NOT NULL DEFAULT false,
    phone_verification_code text COLLATE pg_catalog."default",
    id_verified boolean NOT NULL DEFAULT false,
    qualification_verified boolean NOT NULL DEFAULT false,
    role text COLLATE pg_catalog."default" DEFAULT 'user'::text,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_id_fkey FOREIGN KEY (id)
        REFERENCES auth.users (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE CASCADE
);


CREATE FUNCTION public.clone_user()
    RETURNS trigger
AS $$
    begin
        insert into public.users (id)
        values (new.id);
        return new;
    end;
    $$ language plpgsql security definer;
	
	
CREATE FUNCTION public.audit()
    RETURNS trigger
AS $$
    begin
        insert into audits (tbl, row_id, before, after, user_id)
        values (TG_TABLE_NAME, new.id, row_to_json(old), row_to_json(new), auth.uid());
        return new;
    end;
    $$ language plpgsql security definer;


CREATE TRIGGER audit
    AFTER INSERT OR DELETE OR UPDATE 
    ON public.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.audit();


CREATE TRIGGER clone_user
    AFTER INSERT
    ON auth.users
    FOR EACH ROW
    EXECUTE PROCEDURE public.clone_user();

However, it still doesn't work even running from the dashboard. I've narrowed it down to the audit trigger, as when I disable/remove it, I can register new users again.

So my flow is: User arrives in auth.users and a trigger clones them to public.users. A trigger should then log that change to the audits table, but as that's somehow failing it's blocking the entire flow and throwing the Database error saving new user message out.

If I disable the audit trigger to allow a user to sign up, then delete the corresponding cloned row from my public.users table, enable the audit trigger again and manually clone the auth user as a public user, the audit logic works perfectly. So this must be something permissions related?

[sturatcliffe]

Sorry @nstrelow, @kiwicopple,

I've just found the logs section at the bottom of the authentication settings screen, and this has highlighted what my issue with the audit trigger outlined above is - I wasn't specifying the schema in which the audits table belonged. Somehow when the user is first cloned from auth.users to public.users, it can't find the audits table as it must only be looking in the auth schema. From that point forwards the triggers are all invoked from within the public schema so there's no issue finding where to write the audit rows to.

However, this may help you diagnose your issue @nstrelow. Especially if you can get it to fail more reliably, you can check the logs here: https://app.supabase.io/project/{YOUR_APP}/auth/settings and you'll see a more details explanation of why the operation is failing. I'm not sure how long the logs are visible for though, so you may need to check pretty soon after a failure which may prove difficult if it's still only failing very infrequently for you.

[NixBiks]

I have the same issue running locally with supabase cli. Let me know what I can do to provide sufficient information. My issue happens when I click a magic link, e.g. http://localhost:54321/auth/v1/verify?redirect_to=http%3A%2F%2Flocalhost%3A3000&token=gUY2YV-ZNUPJ5XQoPrRnUg&type=invite

How to fix it

1. Stop local supabase
2. Delete supabase/.branches and supabase/.temp
3. Run supabase start
4. Run my seed script that sends so invites to my inbucket service

If I run supabase db reset and seed again then the error is back and I have to do the steps above

[mclean25]

I have the same issue running locally with supabase cli. Let me know what I can do to provide sufficient information. My issue happens when I click a magic link, e.g. http://localhost:54321/auth/v1/verify?redirect_to=http%3A%2F%2Flocalhost%3A3000&token=gUY2YV-ZNUPJ5XQoPrRnUg&type=invite

How to fix it

1. Stop local supabase
2. Delete supabase/.branches and supabase/.temp
3. Run supabase start
4. Run my seed script that sends so invites to my inbucket service

If I run supabase db reset and seed again then the error is back and I have to do the steps above

Not sure what #4 means here (the 'sends so invites' part), but I was similarly able to resolve this issue by repeating steps 1-3 here and then running my own seed script for my local instance.

[NixBiks]

@mclean25 just a typo so it's hard to read the sentence. But the step is running a seed script that sends invites (all invites are captured with the inbucket service)

[J0]

Hey all, transferring this to our Auth repo so we can best track this

[kangmingtay]

Hey everyone, these errors are usually returned when there is a failing trigger created on one of the tables in the auth schema. If anyone's still facing this issue and require assistance, please contact Supabase at our support link (https://app.supabase.com/support/new)

[saltcod]

Super old, but ran into this tonight. It was an issue with a trigger function having a misnamed param. To debug, start with a basic trigger like this and work back:

create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
begin
  insert into public.profiles (id)
  values (new.id);
  return new;
end;
$$;

-- trigger the function every time a user is created
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

[kangmingtay]

@saltcod yeah, this error is returned if you have a trigger on the auth schema that fails - it causes the entire transaction to be rolled back

[TomasSestak]

For me it was because i was hashing the password by myself first - then i inspected that supabase makes that for ya

[TomasHubelbauer]

Happened to me when running my project locally and I had to manually remove all Docker Supabase volumes in addition to supabase stop --no-backup. I also deleted all images for good measure and made sure I was starting absolutely everything from zero, but I think the image refetch step is not necessary.