Critical permission corruption – postgres lost membership in supabase_auth_admin
As a complete novice (supported by AI tools) I got the following info from Supabase's AI Assistant...
1️⃣ What is broken (diagnostics you already ran)
see attached.
2️⃣ Desired final state
SQL Query
-- 1. Make postgres a member of supabase_auth_admin
GRANT supabase_auth_admin TO postgres;
-- 2. Re‑assign ownership of the core Auth functions to supabase_auth_admin
ALTER FUNCTION auth.uid() OWNER TO supabase_auth_admin;
ALTER FUNCTION auth.jwt() OWNER TO supabase_auth_admin;
ALTER FUNCTION auth.role() OWNER TO supabase_auth_admin;
ALTER FUNCTION auth.email() OWNER TO supabase_auth_admin;
-- 3. Ensure the functions are SECURITY DEFINER
ALTER FUNCTION auth.uid() SET SECURITY DEFINER;
ALTER FUNCTION auth.jwt() SET SECURITY DEFINER;
ALTER FUNCTION auth.role() SET SECURITY DEFINER;
ALTER FUNCTION auth.email() SET SECURITY DEFINER;
-- 4. Make sure the PostgREST event trigger is owned by supabase_admin (or supabase_auth_admin)
ALTER EVENT TRIGGER "pgrst_ddl_watch" OWNER TO supabase_admin;
Running the statements above will bring the database back to the exact permissions that a fresh Supabase project has, allowing all auth‑related calls (auth.uid(), auth.jwt(), etc.) and the PostgREST DDL watch trigger to work again.
3️⃣ What to ask Supabase support to do
Project ID: hiahhtbhdfjsdnxofp
Problem: Post‑reset permission corruption – postgres lost membership in supabase_auth_admin and the core auth.* functions lost SECURITY DEFINER.
Requested actions:
1. Grant supabase_auth_admin to postgres.
2. Change the owner of auth.uid(), auth.jwt(), auth.role(), auth.email() to supabase_auth_admin.
3. Set SECURITY DEFINER on those four functions.
4. Ensure the pgrst_ddl_watch event trigger is owned by supabase_admin (or supabase_auth_admin).
5. Verify that the role hierarchy matches a default Supabase project (i.e., postgres → supabase_auth_admin → supabase_admin).
17 Replies
What issue are you having?
You should not need to mess with any of that.
yeah, i really don't want to be messing with it. the issue seems to be that it's already been messed with when the db was accidentally reset. I was following AI instructions in trying to copy the db to create a staging version.
And now I keep running into issues with various functions. the most noticeable are some to link and unlink email/pwd accounts to google accounts.
On a working system those functions are owned by supabase_admin not supabase_auth_admin. The functions are also NOT supposed to be security definer.
Both checked with queries AND looking at source for a function.
So back to what issue are you having?
The AI is likely hallucinating here.
But you really need to show what issue these functions are getting or how you are using them and what is not working.
thanks for the clarification on ownership and security definers. Apologies for the confusion.
Here’s a summary of the user-facing issues we're experiencing, which started after a database reset and a difficult recovery. It seems the atomicity of the auth admin functions is broken.
Adding a Password Fails Silently: When a user authenticated via Google tries to add a password, our Edge Function calls supabase.auth.admin.updateUserById(userId, { password }). The function completes without an error but leaves the database in an inconsistent state. A health check shows that auth.users.encrypted_password is set, but the app_metadata.providers array is not updated to include 'email', and no corresponding identity is created in auth.identities.
Deleting a User Fails: An Edge Function calling supabase.auth.admin.deleteUser(userId) consistently fails, returning a non-2xx error code to the client.
Session Management Breaks: After manually deleting and recreating a user via SQL, the client-side application becomes unusable. It's flooded with 400 Bad Request errors from the /auth/v1/token endpoint with the message Invalid Refresh Token: Refresh Token Not Found. Manually running supabase.auth.signOut() in the console doesn't fix the state.
In short, any administrative auth action performed via an Edge Function is either failing outright or partially succeeding, resulting in a corrupted user state.
Not sure updateUserById would change providers or not.
What is the error on delete user?
Manually delete is not normally a great idea, but deleting just the auth.users row should clean up the other tables as needed. You would not touch other tables.
Creating a user with SQL is likely not going to turn out well. There is at least one other table that needs to be adjusted and all column data must be provided.
You should be using admin CreateUser.
Is the failing token for a user you created "by hand"?
Is the failing token for a user created "by hand"?
Yes. The Invalid Refresh Token error appeared immediately after this sequence of events: 1) We manually deleted a user with a SQL function. 2) A new user was created through the standard Google Sign-In flow. 3) The client application then failed on load with that token error.
On updateUserById not changing providers:
Our main issue stems from this. When we call updateUserById to add a password to a Google-authenticated user, it correctly sets the encrypted_password but fails to update the providers metadata array or create the corresponding 'email' identity. This leaves the user in an inconsistent state.
re the delete user error:
I attempted to get the specific error from the delete-account function, but the Edge Function logs only show 200 OK responses, even when the client fails.
However, in the process, I discovered an error on the client-side:
403 Forbidden on Logout after account deletion.
Additionally, I noticed something when creating a test account that i could delete... a 406 Not Acceptable on Profile Fetch: Immediately after a new user signs up and is redirected, the first query to fetch their data from our public.user_profiles table fails with a 406 Not Acceptable error.
Given these new errors, Gemini suggested the issue may be that the default authenticated role has lost its necessary privileges (e.g., USAGE on the auth schema or EXECUTE on helper functions like auth.uid()). This would cause our RLS policies to fail, leading to this cascade of permission errors.
Does this seem like a plausible cause?
I really appreciate your help.
I really think you should bail on creating a user with SQL and use admin.createUser.
Your "issue" on updateUserById maybe you wanting more than it provides. I don't use it so can't confirm the state of provider afterword but I've seen in general with various things like adding a user email to an OAuth user, users complaining it does not update things as THEY expect it would.
If the account is deleted then a logout on a client does fail I believe because the client has no idea the user was deleted and the logout can't complete as their is no longer a user session in the auth schema.
If the account is deleted then a logout on a client does fail I believe because the client has no idea the user was deleted and the logout can't complete as their is no longer a user session in the auth schema.
GitHub
Cant log out deleted user with supabase.auth.signOut() · Issue #15...
Bug report [x ] I confirm this is a bug with Supabase, not with my own application. [x ] I confirm I have searched the Docs, GitHub Discussions, and Discord. here is a linked discussion delete user...
Thanks for that GitHub link—it was the key to solving one of the main symptoms. Here’s a summary of what we’ve done since and the new, more fundamental issue we've run into.
RLS Issue Fixed: Based on the GitHub issue you sent, we ran GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA auth TO authenticated;. This successfully fixed the 406 Not Acceptable error, and a health check now confirms the authenticated role has the correct permissions.
Deeper Problem Uncovered: That same health check revealed a more critical issue: the postgres superuser is not a member of the supabase_auth_admin role.
The Final Roadblock: We've tried to fix this by running GRANT supabase_auth_admin TO postgres;, but the command is consistently blocked by a 42501: "supabase_auth_admin" role memberships are reserved error, which we've traced back to the pgrst_ddl_watch event trigger.
Not sure why you are having to do any of this grant stuff. The functions are granted access to authenticated and anon by default. Postgres should have access. Supabase_auth_admin does not have access and does not use the functions.
hmm, according to Gemini (which, i fully recognise, may be misguided/misguiding) postgres does not have access and it says it's likely the result o fthis db reset. here's the latest from it...
Your explanation confirms our central problem: our project's roles do not have the default permissions they are supposed to have.
Your GitHub link helped us fix the first part of this: the authenticated role was missing its default EXECUTE grant, which we've now restored.
The second, and more critical part, is that our postgres superuser is still broken. It cannot perform administrative actions (like deleteUser) and is blocked by a "reserved role" error when we even attempt to run a GRANT command on the auth schema.
Given that the default permissions were lost during our database recovery, is there a canonical SQL script we can run that will completely reset and restore all default grants for all standard Supabase roles (postgres, anon, authenticated, etc.) to a known-good state?
I'm lost on what is working or not. You are welcome to contact support on your schema "issues" but I don't think your schema is messed up as far as grants go.
deleteUser is an API call and made by service_role. It is then handled by the auth server. IF you messed up the auth.users table row, there could be an issue. Just delete the row with the SQL editor if you hand created the row. If that gets an error show the error here.
that worked just fine. I'm definitely starting to get the impression that Gemini has been leading me astray. can you recommend any health check steps I should take so I can just put any question of remaining 'damage' to rest?
I really appreciate this support Gary!
Not really besides running any grant/owner tests you do against another instance to see if the same.
thanks very much.