Replies: 1 comment
|
Good list — the ordering point (auth.users before public) is the one that saves people. Four additions from restores I've run on customer projects, all in the "silent" category: 1. Schema drift in select column_name, data_type, is_nullable, column_default
from information_schema.columns
where table_schema = 'auth' and table_name in ('users','identities')
order by table_name, ordinal_position;When they differ, dump with 2. Keep RLS off select relrowsecurity from pg_class where oid = 'auth.users'::regclass;Same outage pattern, different trigger — I wrote up the mechanism here: https://github.com/orgs/supabase/discussions/53118 3. Identities: compare per provider, not just row counts. Two projects can both report 8,431 identities and have quietly lost every Microsoft/GitHub one, because those providers' identity rows carry different 4. SAML/SSO and MFA are two different recovery costs. MFA re-enrollment is tickets. SAML is worse: Your post-restore check is the right instinct. Adding the per-provider identity diff and the |
Uh oh!
There was an error while loading. Please reload this page.
If you keep your own backups of a Supabase project, sooner or later the question is whether your users can still sign in after a restore. We've spent a while on this while building restores into a fresh project, and the answer is "mostly, with a list of exceptions". Writing the list down here, since I couldn't find it in one place.
What has to be in the backup
A
pg_dumpofpublicdoesn't include your users. They live in two tables in theauthschema:auth.users: one row per user, including the email, phone, metadata and the bcrypt hash of the passwordauth.identities: one row per sign-in method (email, Google, GitHub and so on), linked to the userCopy both and your users exist in the new project. The
postgresrole can usually read them, so something like this works while the old project is still up (Session pooler strings for both projects, passwords typed at hidden prompts):Do this before you restore
public. If you have aprofilestable whoseidreferencesauth.users(id), restoring it into a project with an emptyauth.usersfails on that foreign key.What comes back
auth.users, so users sign in with their old password.raw_user_meta_dataandraw_app_meta_data, including any roles you keep in app metadata.What doesn't
auth.mfa_factors, which the copy above leaves out. Users who had MFA sign in with one factor until they enroll again. Copying that table as well might work, but I haven't checked whether the old factors still verify in a new project, so plan for users enrolling again.auth.users, such as the usualhandle_new_userthat inserts aprofilesrow on sign-up. They belong to anauthtable, so a dump ofpublicdoesn't have them. Recreate them, then sign up a test user in the new project and check that the profile row appears.A quick check afterwards
After a restore, compare
select count(*) from auth.usersandfrom auth.identitieswith the old project, sign in once with a password account and once with an OAuth account, and sign up a new user to see the triggers fire. Do this before you point your app at the new project.I work on BackupDrill, and its open-source CLI (backupdrill/cli, MIT) backs up
auth.usersandauth.identitiesnext to the database dump when the database role can read both tables, and restores them in the same transaction as the rest of the data. Nothing above needs it, though; plainpg_dumpandpsqldo the job.Has anyone moved Auth users between projects and hit something that isn't on this list? I'm curious about phone sign-in and SAML users in particular.
All reactions