Can't seed or signup auth users locally

[madeleineostoja]

I'm trying to get my local Supabase dev environment working, and I'm stuck getting Supabase auth working in local dev.

I've tried seeding a dummy user in seed.sql, eg:

insert into auth.users (instance_id, id, aud, role, email, encrypted_password, raw_app_meta_data, raw_user_meta_data, email_confirmed_at, created_at)
  values ('00000000-0000-0000-0000-000000000000', '185f2f83-d63a-4c9b-b4a0-7e4a885799e2', 'authenticated', 'authenticated', 'madi@bokeh.photo', '$2a$10$6gPtvpqCAiwavx1EOnjIgOykKMgzRdiBuejUQGIRRjvUi/ZgMh.9C', '{"provider":"email","providers":["email"]}', '{}', timezone('utc'::text, now()), timezone('utc'::text, now()));

insert into auth.identities (id, user_id, identity_data, provider, created_at)
  values ('185f2f83-d63a-4c9b-b4a0-7e4a885799e2', '185f2f83-d63a-4c9b-b4a0-7e4a885799e2',	'{"sub": "185f2f83-d63a-4c9b-b4a0-7e4a885799e2"}', 'email', timezone('utc'::text, now()));

But when trying to login Supabase throws invalid login credentials errors (the encrypted password is taken from a pg_dump of a user I know the password to in my prod db)

I also tried signing up when running a local DB, but Supabase throws a cryptic Database error saving new user error, which also happens if I try inviting a user through the local Studio dashboard.

Any ideas?

[madeleineostoja]

Anyone?

[5hee75]

I followed essentially the same process as you and I was able to log in using the Supabase Auth UI. It did complain the first try about invalid credentials, but after the first try it seemed to work. I had forgotten to "confirm" the emails, but I could fetch a token after I updated that field.

Here's my seed data, if it helps. It looks pretty much the same as yours, though.

INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, invited_at, confirmation_token, confirmation_sent_at, recovery_token, recovery_sent_at, email_change_token_new, email_change, email_change_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, phone, phone_confirmed_at, phone_change, phone_change_token, phone_change_sent_at, email_change_token_current, email_change_confirm_status, banned_until, reauthentication_token, reauthentication_sent_at)
VALUES 
  ('00000000-0000-0000-0000-000000000000', '5899f99d-a449-4bfa-8769-19c097aaf1f5', 'authenticated', 'authenticated', 'admin@email.com', '$2a$10$7HloIuRU8136LMux12Vceu8x57OuqrRDpUsIlHLjM4I85LwzUiHRm', '2022-10-04 03:41:27.39308+00', NULL, '', NULL, '', NULL, '', '', NULL, NULL, '{"provider": "email", "providers": ["email"]}', '{}', NULL, '2022-10-04 03:41:27.391146+00', '2022-10-04 03:41:27.39308+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL);

INSERT INTO auth.identities (id,user_id,identity_data,provider,last_sign_in_at,created_at,updated_at)
VALUES 
  ('5899f99d-a449-4bfa-8769-19c097aaf1f5', '5899f99d-a449-4bfa-8769-19c097aaf1f5'::uuid, '{"sub": "5899f99d-a449-4bfa-8769-19c097aaf1f5"}', 'email', '2022-10-04 04:45:00.000+00', '2022-10-04 03:41:27.391146+00', '2022-10-04 03:41:27.39308+00');

[madeleineostoja]

Weird! From more digging it sounds like there's something wrong with my local DB, but I can't get the logs to check that. Even trying to invite a user in the local studio UI gives me "database error creating user"

[madeleineostoja]

Found it! For anyone else coming across this, in my case it was accessing a function in an extension (eg: gen_random_uuid from pgcrypto). In my live Supabase account that's in the search path so I can just call it normally, in my local dev environment it's not for whatever reason, so I have to call extensions.gen_random_uuid() explicitly.

[komagata]

I can not seed too. (used to extensions.gen_random_uuid())
What SQL do I need to write?

[ParishKhan]

Where did you use extensions.gen_random_uuid() ?
Can you please share your seed.sql file?

[WilderDev]

I'm getting the same errors on local.

I can sign up for an account and that works... but the seed script user get a 500 server error.

When I compare the created user and the seed script user, they are identical in the users table, identity table, and profiles table... Very odd.

Here is my seed script if anyone is wondering:

`-- Default User
INSERT INTO
auth.users
(instance_id, id, aud, role, email, encrypted_password, raw_app_meta_data, raw_user_meta_data, email_confirmed_at, created_at, recovery_sent_at)
VALUES
('00000000-0000-0000-0000-000000000000', '185f2f83-d63a-4c9b-b4a0-7e4a885799e2', 'authenticated', 'authenticated', 'test@gmail.com', '$2a$10$6gPtvpqCAiwavx1EOnjIgOykKMgzRdiBuejUQGIRRjvUi/ZgMh.9C', '{"provider":"email","providers":["email"]}', '{}', now(), now(), now());

INSERT INTO
auth.identities (id, user_id, identity_data, provider, created_at, last_sign_in_at)
VALUES
('185f2f83-d63a-4c9b-b4a0-7e4a885799e2', '185f2f83-d63a-4c9b-b4a0-7e4a885799e2'::uuid, '{"sub": "185f2f83-d63a-4c9b-b4a0-7e4a885799e2","email":"test@gmail.com"}', 'email', now(), now());
`

[franciscolourenco]

@jjmax75 I get the following error when trying to send the magic link:

{"error":{"code":500,"message":"Database error finding user","requestId":"2fcc0983-5775-4de8-a166-a5532ae7c611"}}

[WilderDev]

@franciscolourenco That's the same one I get. So far there is no solution other then using supabase.auth.admin.createUser()... in which case you don't get to choose the users id.

[jjmax75]

I get the can't find user error on the first attempt to send the magic link. Subsequent attempts work though. I'll post my seed code in here later for the Auth user generation.

[jjmax75]

INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, invited_at, confirmation_token, confirmation_sent_at, recovery_token, recovery_sent_at, email_change_token_new, email_change, email_change_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, phone, phone_confirmed_at, phone_change, phone_change_token, phone_change_sent_at, email_change_token_current, email_change_confirm_status, banned_until, reauthentication_token, reauthentication_sent_at)
VALUES 
  ('00000000-0000-0000-0000-000000000000', '<uuid>, 'authenticated', 'authenticated', '<email-used-in-initial-setup>', '<pw-retrieved-from-initial-setup-of-user>.', '2023-04-22 13:10:31.463703+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{"provider": "email", "providers": ["email"]}', '{}', NULL, '2022-10-04 03:41:27.391146+00', '2023-04-22 13:10:31.463703+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL);

INSERT INTO auth.identities (id,user_id,identity_data,provider,last_sign_in_at,created_at,updated_at)
VALUES 
  ('<uuid>', '<uuid>'::uuid, '{"sub": "<uuid>"}', 'email', '2023-04-22 13:10:31.458239+00', '2022-10-04 03:41:27.391146+00', '2023-04-22 13:10:31.463703+00');

I use the same uuid to replace all instances of <uuid>

[WilderDev]

INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, invited_at, confirmation_token, confirmation_sent_at, recovery_token, recovery_sent_at, email_change_token_new, email_change, email_change_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, phone, phone_confirmed_at, phone_change, phone_change_token, phone_change_sent_at, email_change_token_current, email_change_confirm_status, banned_until, reauthentication_token, reauthentication_sent_at)
VALUES 
  ('00000000-0000-0000-0000-000000000000', '<uuid>, 'authenticated', 'authenticated', '<email-used-in-initial-setup>', '<pw-retrieved-from-initial-setup-of-user>.', '2023-04-22 13:10:31.463703+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{"provider": "email", "providers": ["email"]}', '{}', NULL, '2022-10-04 03:41:27.391146+00', '2023-04-22 13:10:31.463703+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL);

INSERT INTO auth.identities (id,user_id,identity_data,provider,last_sign_in_at,created_at,updated_at)
VALUES 
  ('<uuid>', '<uuid>'::uuid, '{"sub": "<uuid>"}', 'email', '2023-04-22 13:10:31.458239+00', '2022-10-04 03:41:27.391146+00', '2023-04-22 13:10:31.463703+00');

I use the same uuid to replace all instances of <uuid>

I do notice that on your first VALUES uuid you are missing an end quotation.

So I actually got it working, just replace the UUID with a uuid and the EMAIL with the email you want to use:

-- Default User
INSERT INTO auth.users
(instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, invited_at, confirmation_token, confirmation_sent_at, recovery_token, recovery_sent_at, email_change_token_new, email_change, email_change_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, phone, phone_confirmed_at, phone_change, phone_change_token, phone_change_sent_at, email_change_token_current, email_change_confirm_status, banned_until, reauthentication_token, reauthentication_sent_at)
VALUES
('00000000-0000-0000-0000-000000000000', 'UUID', 'authenticated', 'authenticated', 'EMAIL', 'password', '2023-04-22 13:10:31.463703+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{"provider": "email", "providers": ["email"]}', '{}', NULL, '2022-10-04 03:41:27.391146+00', '2023-04-22 13:10:31.463703+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL);

INSERT INTO auth.identities
(id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
VALUES
('UUID', 'UUID'::uuid, '{"sub": "UUID", "email": "EMAIL"}', 'email', '2023-04-22 13:10:31.458239+00', '2022-10-04 03:41:27.391146+00', '2023-04-22 13:10:31.463703+00');

[jjmax75]

you're welcome

[ghost]

Wrote this python script to automatically generate a bunch of users, with correctly hashing different passwords (dropping below for anyone who stumbles across this thread).

Install Dependencies

python3 -m venv venv
source ./venv/bin/activate

Then in the venv:

pip3 install --upgrade pip
pip3 install Faker 
pip3 install bcrypt 

Faker is to generate random but valid-syntax email addresses
bcrypt is to hash passwords in the same way supabase does

Script

Run the below script and then just copy the output into seed.sql

from uuid import uuid4 # built-in / std lib include
from faker import Faker
import bcrypt

fake = Faker()


class User:

    def __init__(self):
        self.id_ = str(uuid4())
        self.email = fake.email()
        self.password = fake.password()
        self.hashed_password = self.hash_password(self.password)

    @staticmethod
    def hash_password(pwd: str):
        bytes_ = pwd.encode('utf-8')
        salt = bcrypt.gensalt(rounds=10, prefix=b"2a")
        hash = bcrypt.hashpw(bytes_, salt)
        return hash.decode()

    def auth_user(self) -> str:
        s = f"""('00000000-0000-0000-0000-000000000000', '{self.id_}', 'authenticated', 'authenticated', '{self.email}', '{self.hashed_password}', '2023-05-03 19:41:43.585805+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{{"provider":"email","providers":["email"]}}', '{{}}', NULL, '2023-05-03 19:41:43.580424+00', '2023-05-03 19:41:43.585948+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL)"""
        return s

    def auth_identity(self) -> str:
        s = f"""('{self.id_}', '{self.id_}'::uuid, '{{"sub":"{self.id_}","email":"{self.email}"}}', 'email', '2023-05-03 19:41:43.582456+00', '2023-05-03 19:41:43.582497+00', '2023-05-03 19:41:43.582497+00')"""
        return s

    def __repr__(self):
        return self.single_row()


if __name__ == "__main__":

    users: list[User] = [User() for _ in range(15)]

    print("""INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, invited_at, confirmation_token, confirmation_sent_at, recovery_token, recovery_sent_at, email_change_token_new, email_change, email_change_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, phone, phone_confirmed_at, phone_change, phone_change_token, phone_change_sent_at, email_change_token_current, email_change_confirm_status, banned_until, reauthentication_token, reauthentication_sent_at)\n  VALUES""")
    for index, user in enumerate(users):
        line_terminator = ("," if index is not (len(users)-1) else ";")
        print(f"    {user.auth_user()}{line_terminator}")

    print("""INSERT INTO auth.identities (id,user_id,identity_data,provider,last_sign_in_at,created_at,updated_at)\n  VALUES""")
    for index, user in enumerate(users):
        line_terminator = ("," if index is not (len(users) - 1) else ";")
        print(f"    {user.auth_identity()}{line_terminator}")

Output from the above script will look like:

INSERT INTO auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, invited_at, confirmation_token, confirmation_sent_at, recovery_token, recovery_sent_at, email_change_token_new, email_change, email_change_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, is_super_admin, created_at, updated_at, phone, phone_confirmed_at, phone_change, phone_change_token, phone_change_sent_at, email_change_token_current, email_change_confirm_status, banned_until, reauthentication_token, reauthentication_sent_at)
  VALUES
    ('00000000-0000-0000-0000-000000000000', '7de838dc-1857-4fa1-a9fb-ed172eec2251', 'authenticated', 'authenticated', 'qmoreno@example.org', '$2a$10$9yvdOdNaIZ4HuGWFdRSw/uJPksCWmiHEBhqu630ubu/MkwdK2I4dO', '2023-05-03 19:41:43.585805+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{"provider":"email","providers":["email"]}', '{}', NULL, '2023-05-03 19:41:43.580424+00', '2023-05-03 19:41:43.585948+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL),
    ('00000000-0000-0000-0000-000000000000', 'a058a0ca-ef0c-44d8-927f-c6ad4a3689b8', 'authenticated', 'authenticated', 'mclaughlinfrank@example.org', '$2a$10$DJoLJ82XGH2a.agT7YUEFubkbDXtyu5WdLJ7rEyICEhzDUbEoaedm', '2023-05-03 19:41:43.585805+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{"provider":"email","providers":["email"]}', '{}', NULL, '2023-05-03 19:41:43.580424+00', '2023-05-03 19:41:43.585948+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL),
    ('00000000-0000-0000-0000-000000000000', 'cb28fdbf-0990-4d65-967e-567add504943', 'authenticated', 'authenticated', 'tina70@example.org', '$2a$10$wfkb8QZBb73tmNoWfvRXje/Tx07cyG8YL7RoXXLq0dDx/MbkXbuTq', '2023-05-03 19:41:43.585805+00', NULL, '', NULL, '', '2023-04-22 13:10:03.275387+00', '', '', NULL, '2023-04-22 13:10:31.458239+00', '{"provider":"email","providers":["email"]}', '{}', NULL, '2023-05-03 19:41:43.580424+00', '2023-05-03 19:41:43.585948+00', NULL, NULL, '', '', NULL, '', 0, NULL, '', NULL);
INSERT INTO auth.identities (id,user_id,identity_data,provider,last_sign_in_at,created_at,updated_at)
  VALUES
    ('7de838dc-1857-4fa1-a9fb-ed172eec2251', '7de838dc-1857-4fa1-a9fb-ed172eec2251'::uuid, '{"sub":"7de838dc-1857-4fa1-a9fb-ed172eec2251","email":"qmoreno@example.org"}', 'email', '2023-05-03 19:41:43.582456+00', '2023-05-03 19:41:43.582497+00', '2023-05-03 19:41:43.582497+00'),
    ('a058a0ca-ef0c-44d8-927f-c6ad4a3689b8', 'a058a0ca-ef0c-44d8-927f-c6ad4a3689b8'::uuid, '{"sub":"a058a0ca-ef0c-44d8-927f-c6ad4a3689b8","email":"mclaughlinfrank@example.org"}', 'email', '2023-05-03 19:41:43.582456+00', '2023-05-03 19:41:43.582497+00', '2023-05-03 19:41:43.582497+00'),
    ('cb28fdbf-0990-4d65-967e-567add504943', 'cb28fdbf-0990-4d65-967e-567add504943'::uuid, '{"sub":"cb28fdbf-0990-4d65-967e-567add504943","email":"tina70@example.org"}', 'email', '2023-05-03 19:41:43.582456+00', '2023-05-03 19:41:43.582497+00', '2023-05-03 19:41:43.582497+00');

[ghost]

If you need a fixed number of users, just change the first line in the if __name__ == "__main__" block at the bottom from:
users: list[User] = [User() for _ in range(15)]
to
users: list[User] = [User() for _ in range(NUMBER_OF_USERS_NEEDED)]

[jziggas]

Traceback (most recent call last):
File "utils/create_user.py", line 37, in
users: list[User] = [User() for _ in range(5)]
TypeError: 'type' object is not subscriptable

[barrownicholas]

Traceback (most recent call last):
File "utils/create_user.py", line 37, in
users: list[User] = [User() for _ in range(5)]
TypeError: 'type' object is not subscriptable

I just re-ran my original code in python 3.10 and did not encounter this issue. Can you share or email me (nbarrow@inspiretmstech.com) your full utils/create_user.py file and I'll take a look? It could be my type annotations are not compatible with the version of python you're on, but I won't know without that info and seeing the full implementation of the file on your end.

[Asaraspo]

I am also getting an error when trying to seed users locally. I am having an Error: ERROR: relation "auth.identities" does not exist (SQLSTATE 42P01) but also an ERROR: column "email_confirmed_at" of relation "users" does not exist (SQLSTATE 42703) (amongst other columns that do not exist) when deleting the insert into auth.identities statement.

Here is my codesnippet:

INSERT INTO auth.users (instance_id,id,aud,"role",email,encrypted_password,email_confirmed_at,last_sign_in_at,raw_app_meta_data,raw_user_meta_data,is_super_admin,created_at,updated_at,phone,phone_confirmed_at,confirmation_token,email_change,email_change_token_new,recovery_token) VALUES
	('00000000-0000-0000-0000-000000000000'::uuid,'f76629c5-a070-4bbc-9918-64beaea48848'::uuid,'authenticated','authenticated','test@example.com','$2a$10$PznXR5VSgzjnAp7T/X7PCu6vtlgzdFt1zIr41IqP0CmVHQtShiXxS','2022-02-11 21:02:04.547','2022-02-11 22:53:12.520','{"provider": "email", "providers": ["email"]}','{}',FALSE,'2022-02-11 21:02:04.542','2022-02-11 21:02:04.542',NULL,NULL,'','','',''),
	('00000000-0000-0000-0000-000000000000'::uuid,'d9064bb5-1501-4ec9-bfee-21ab74d645b8'::uuid,'authenticated','authenticated','demo@example.com','$2a$10$mOJUAphJbZR4CdM38.bgOeyySurPeFHoH/T1s7HuGdpRb7JgatF7K','2022-02-12 07:40:23.616','2022-02-12 07:40:23.621','{"provider": "email", "providers": ["email"]}','{}',FALSE,'2022-02-12 07:40:23.612','2022-02-12 07:40:23.613',NULL,NULL,'','','','')
ON CONFLICT (id) DO NOTHING;

INSERT INTO auth.identities (id,user_id,identity_data,provider,last_sign_in_at,created_at,updated_at) VALUES
	('f76629c5-a070-4bbc-9918-64beaea48848','f76629c5-a070-4bbc-9918-64beaea48848'::uuid,'{"sub": "f76629c5-a070-4bbc-9918-64beaea48848"}','email','2022-02-11 21:02:04.545','2022-02-11 21:02:04.545','2022-02-11 21:02:04.545'),
	('d9064bb5-1501-4ec9-bfee-21ab74d645b8','d9064bb5-1501-4ec9-bfee-21ab74d645b8'::uuid,'{"sub": "d9064bb5-1501-4ec9-bfee-21ab74d645b8"}','email','2022-02-12 07:40:23.615','2022-02-12 07:40:23.615','2022-02-12 07:40:23.615')
ON CONFLICT (id, provider) DO NOTHING;

Can you please help me, why my seeding is failing?

[Asaraspo]

Thanks. I am still experiencing some issues when seeding the database with supabase start. As a workaround i am deleting the content of seed.sql and afterwards running supabase db reset.

[NiklasPor]

I am experiencing exactly the same issues after a CLI upgrade and I'm really lost regarding the user seeding. In the past the simple column insert dump from the auth table worked 😵

The weirdest thing with this is – with an empty seed.sql the tables include all columns like they're supposed to if I check them manually.

[NiklasPor]

This might be connected to supabase/cli#1277, sadly I can't upgrade to the latest CLI version and test because of supabase/cli#1083. If you can test with the latest CLI version that might be enough.

[dhruvik7]

I'm having this issue as well now, were you able to resolve it?

[NiklasPor]

I think upgrading did it in the past for me. Also take care to regenerate the user seeding when upgrading the Supabase version. Sometimes the auth schema changes (I think?)

[jziggas]

I posted this in another issue as well. Here is some code to seed users programmatically:

CREATE OR REPLACE FUNCTION public.create_user(
    email text,
    password text
) RETURNS void AS $$
  declare
  user_id uuid;
  encrypted_pw text;
BEGIN
  user_id := gen_random_uuid();
  encrypted_pw := crypt(password, gen_salt('bf'));
  
  INSERT INTO auth.users
    (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, recovery_sent_at, last_sign_in_at, raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token, email_change, email_change_token_new, recovery_token)
  VALUES
    ('00000000-0000-0000-0000-000000000000', user_id, 'authenticated', 'authenticated', email, encrypted_pw, '2023-05-03 19:41:43.585805+00', '2023-04-22 13:10:03.275387+00', '2023-04-22 13:10:31.458239+00', '{"provider":"email","providers":["email"]}', '{}', '2023-05-03 19:41:43.580424+00', '2023-05-03 19:41:43.585948+00', '', '', '', '');
  
  INSERT INTO auth.identities (id, user_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
  VALUES
    (gen_random_uuid(), user_id, format('{"sub":"%s","email":"%s"}', user_id::text, email)::jsonb, 'email', '2023-05-03 19:41:43.582456+00', '2023-05-03 19:41:43.582497+00', '2023-05-03 19:41:43.582497+00');
END;
$$ LANGUAGE plpgsql;

Usage:
select public.create_user('test@example.com', 'password') or extract the logic to put into your seed.sql file.

Edit: I corrected a couple errors that can cause FK problems. Tested and working now.

[ghost]

Found another solution:
Use pg_dump in insert mode (defaults to a COPY mode that won't help) and then just copy the relevant lines:

1. start a local supabase instance
2. add test users via the Supabase Dashboard (localhost:54323)
3. use pg_dump: pg_dump postgresql://postgres:postgres@localhost:54322/postgres --data-only --inserts --column-inserts -n public -n auth > backup.sql
4. copy the lines for auth.users and auth.identities(in this order) from the output file (in my case, backup.sql) into your seed.sql

[jdgamble555]

Hmmm... I'm using Windows 11 (with postgres 15.4), not sure if the command would be different.

[jdgamble555]

@nbarrow-inspire-labs - What version of pg_dump are you using?

pg_dump --version

[ghost]

@jdgamble555 pg_dump (PostgreSQL) 15.3 (Homebrew) (not sure if there was some major change between 15.3 and 15.4, but it could also be because I'm on Mac and you're on Windows, so the way the flags work may be different)

[jdgamble555]

Ok, got it working by putting arguments first:

pg_dump --data-only --inserts --column-inserts -n public -n auth postgresql://postgres:postgres@localhost:54322/postgres > backup.sql

Not sure if it is a Windows or version thing. If you upgrade, let me know. Thanks!

J

[ghost]

Awesome, glad you got it working! Thanks for sharing the solution @jdgamble555 , if I run into the same I'll give your change a shot.

[ngamsomset]

for future reference if anyone want to know how to run pg_dump command on MacOs. You need to install libpq. ref here https://stackoverflow.com/questions/44654216/correct-way-to-install-psql-without-full-postgres-on-macos