Supabase
-
|
Discovering supabase at the moment, really liking all the work! In the CLI there's mention of a |
Beta Was this translation helpful? Give feedback.
Replies: 16 comments 21 replies
-
|
+1! Currently we opted for writing the seed script in node and creating the users with the admin client, but |
Beta Was this translation helpful? Give feedback.
-
|
So I've found the CREATE TABLE auth.users (
instance_id uuid,
id uuid NOT NULL,
aud character varying(255),
role character varying(255),
email character varying(255),
encrypted_password character varying(255),
email_confirmed_at timestamp with time zone,
invited_at timestamp with time zone,
confirmation_token character varying(255),
confirmation_sent_at timestamp with time zone,
recovery_token character varying(255),
recovery_sent_at timestamp with time zone,
email_change_token_new character varying(255),
email_change character varying(255),
email_change_sent_at timestamp with time zone,
last_sign_in_at timestamp with time zone,
raw_app_meta_data jsonb,
raw_user_meta_data jsonb,
is_super_admin boolean,
created_at timestamp with time zone,
updated_at timestamp with time zone,
phone character varying(15) DEFAULT NULL::character varying,
phone_confirmed_at timestamp with time zone,
phone_change character varying(15) DEFAULT ''::character varying,
phone_change_token character varying(255) DEFAULT ''::character varying,
phone_change_sent_at timestamp with time zone,
confirmed_at timestamp with time zone GENERATED ALWAYS AS (LEAST(email_confirmed_at, phone_confirmed_at)) STORED,
email_change_token_current character varying(255) DEFAULT ''::character varying,
email_change_confirm_status smallint DEFAULT 0,
CONSTRAINT users_email_change_confirm_status_check CHECK (((email_change_confirm_status >= 0) AND (email_change_confirm_status <= 2)))
); |
Beta Was this translation helpful? Give feedback.
-
|
I'm really interested to understand the right way to do this as well. We've got an app with custom authentication already built, and I'd like to connect our concept of a user with a self-hosted Supbase. I'm digging through docs, issues, and discussions, and I'm not 100% sure how to go about this. I don't want to involve email signup, I just need a way to create a user programmatically, and have my existing backend services figure out how to sign this user in. Are there any good options for this that I'm missing? |
Beta Was this translation helpful? Give feedback.
-
|
The easiest way is to use the GUI to create a bunch of new users, then use a tool like pg_dump to export all those created users as an SQL file. From there it's a simple matter of copying them into the seed file. The big trick is that there is See below for a representative sample of my test data: 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;If you want to copy the code above, the password for both accounts there is "password". It should go without saying: don't use that in a production environment! Once you've got a bunch of test accounts created using the GUI, the structure of each user becomes fairly obvious and it's much easier to see what needs to be inputted into a mock data generator to get a large quantity of users for testing. |
Beta Was this translation helpful? Give feedback.
-
|
Applying your solution, I can insert users and identities in the auth tables, but I can t use the passwords to login. I tried to copy your code as well as export my user and identities table to csv and insert it after. Any ideas about what I do wrong? I feel that the encrypted password is not readable after "supabase db reset" anymore. |
Beta Was this translation helpful? Give feedback.
-
|
Yeah, it stopped working for me after I update the CLI tool two weeks ago. Not sure what changed but the solution for me was to change "aud" from an empty string to My best guess is this is related to Supabase's recent deprecation of the |
Beta Was this translation helpful? Give feedback.
-
|
That works for me! Thank you very much! |
Beta Was this translation helpful? Give feedback.
-
|
Do you also know how to programmatically create a bucket, upload an img and store the public url in a seed.sql file? |
Beta Was this translation helpful? Give feedback.
-
|
This was so helpful, thank you @MrAxle !! |
Beta Was this translation helpful? Give feedback.
-
|
As someone dealing with making two different auth systems play nice with each other, this particular issue is a big deal for me. I find it weird there's a lot of emphasis on "everything is just a database" and yet there's no Postgres functions for manipulating users. |
Beta Was this translation helpful? Give feedback.
-
|
Here's the command to dump the Then in your terminal you should see statements similar to the SQL statements two comments above this. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks, very helpful |
Beta Was this translation helpful? Give feedback.
-
|
As of Feb 2023, this is working for me: |
Beta Was this translation helpful? Give feedback.
-
|
@jziggas It's using whatever Supabase is using under the hood. For example, if you create a new user using either the supabase-js client or the Supabase Studio interface, the password is not actually stored, a hashed version of it is (in Note: If you try this yourself using the same password, you will get a different hash value. This is expected. Having many different hashes that all turn back into the same value (in this case, "password") means that it is much harder for malicious actors to reverse engineer the hash function. |
Beta Was this translation helpful? Give feedback.
-
|
Eh, I meant I want to take my password "qwerty" and convert it to a hash to seed into the database programmatically, like the OP. I don't want to manually enter passwords into Studio. I think that's what most people in here are looking for. So we need to know the algorithm or how to create a function that does this. |
Beta Was this translation helpful? Give feedback.
-
|
I'm not sure how to manually hash the strings, but for simply seeding your database you would only have to put "qwerty" into Studio once and use the same encrypted_password for all the users in your seed script. If you want the same password for all users but a unique hash value, the easiest thing to do may just be to use the Supabase Js client in a script to to bulk sign up a bunch of users with "qwerty" as the password. |
Beta Was this translation helpful? Give feedback.
-
|
Fine, I'll do it myself. With blackjack and hookers. 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;
|
Beta Was this translation helpful? Give feedback.
-
|
Thank you @jziggas, that's a lot less clunky. For others, if you need the uuid of the created user: CREATE OR REPLACE FUNCTION public.create_user(
email text,
password text
) RETURNS uuid 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');
RETURN user_id;
END;
$$ LANGUAGE plpgsql;And use it like this: DO $$
DECLARE
user_id uuid;
BEGIN
user_id := public.create_user('my@email.com', '1BadPassword');
INSERT INTO another_table (user_id, some_other_column)
VALUES (user_id, 'some value for the other column');
END $$; |
Beta Was this translation helpful? Give feedback.
-
|
When I try to use it via API, it says "permission denied for table users". How can I fix it? |
Beta Was this translation helpful? Give feedback.
-
|
solved ? |
Beta Was this translation helpful? Give feedback.
-
|
As a slight change, I would also use CREATE OR REPLACE FUNCTION public.create_user(
email text,
password text
) RETURNS uuid AS $$
declare
user_id uuid;
encrypted_pw text;
BEGIN
user_id := gen_random_uuid();
encrypted_pw := crypt(password, gen_salt('bf', 12));
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, now() at time zone 'utc', now() at time zone 'utc', now() at time zone 'utc', '{"provider":"email","providers":["email"]}', '{}', now() at time zone 'utc', now() at time zone 'utc', '', '', '', '');
INSERT INTO auth.identities (id, user_id, provider_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
VALUES
(gen_random_uuid(), user_id, user_id, format('{"sub":"%s","email":"%s"}', user_id::text, email)::jsonb, 'email', now() at time zone 'utc', now() at time zone 'utc', now() at time zone 'utc');
RETURN user_id;
END;
$$ LANGUAGE plpgsql;
SELECT public.create_user('nkegkj@example.com', '123ABC'); |
Beta Was this translation helpful? Give feedback.
-
|
Any news on an official solution for this? |
Beta Was this translation helpful? Give feedback.
-
Beta Was this translation helpful? Give feedback.
-
|
Programmatic solution posted above in a nested comment. Remix into your own function or extract the logic into your |
Beta Was this translation helpful? Give feedback.
-
|
I recently faced an issue where I needed to populate my auth.users table with a bunch of emails having a default password: so I wrote a simple python script to hit the "https://{projectId}.supabase.co/auth/v1/admin/users/" endpoint. Step 1) was to inspect element in your favourite browser, then I proceeded to manually add a user while observing the packets in the network segment of dev tools. Note this solution uses only the emails as a unique value for the "Magic link" under the email auth provider signup. "was for a simple voting app". |
Beta Was this translation helpful? Give feedback.
-
|
My set of functions to create a user: create_user_metadata(email text, password text, user_meta_data jsonb) returns uuid, security: definer CREATE OR REPLACE FUNCTION public.create_user_metadata(email text, password text, user_meta_data jsonb)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
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, now(), now(), now(), '{"provider":"email","providers":["email"]}', user_meta_data, now(), now(), '', '', '', '');
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', now(), now(), now());
RETURN user_id;
END;
$function$
create_user(email text, password text) returns uuid, security: definer CREATE OR REPLACE FUNCTION public.create_user(email text, password text)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
declare
user_id uuid;
encrypted_pw text;
-- now_date text;
user_meta_data jsonb;
BEGIN
user_id := gen_random_uuid();
encrypted_pw := crypt(password, gen_salt('bf'));
-- now_date := to_char(now(), 'YYYY-MM-DD');
user_meta_data := jsonb_build_object(
'name', email,
'nickname', '19',
'birthdate', to_char(now(), 'YYYY-MM-DD'),
'given_name', email,
'family_name', email,
'middle_name', email
);
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, now(), now(), now(), '{"provider":"email","providers":["email"]}', user_meta_data, now(), now(), '', '', '', '');
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', now(), now(), now());
RETURN user_id;
END;
$function$
create_random_user CREATE OR REPLACE FUNCTION public.create_random_user()
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
random_user jsonb;
email text;
password text;
user_metadata jsonb;
agent_nickname text;
BEGIN
-- Get random user data from get_random_user function
random_user := get_random_user();
-- Extract email from random user data
email := random_user->>'email';
-- Generate a random password (8 to 14 characters alphanumeric)
password := generate_random_password((floor(random() * 7) + 8)::integer);
-- Extract the agent's nickname from the public.customers table
SELECT agent_code INTO agent_nickname
FROM public.customers
WHERE isagent = true AND isactive = true
ORDER BY random()
LIMIT 1;
-- Extract relevant fields to create user metadata
user_metadata := jsonb_build_object(
'name', concat(random_user->'name'->>'first', ' ', random_user->'name'->>'last'),
'nickname', agent_nickname,
'birthdate', to_char((random_user->'dob'->>'date')::timestamp, 'YYYY-MM-DD'),
'given_name', random_user->'name'->>'first',
'family_name', random_user->'name'->>'last',
'middle_name', '',
'gender',random_user->>'gender'
);
-- Call create_user_metadata to create the user
PERFORM create_user_metadata(email, password, user_metadata);
-- Return the user information in the specified format using concat()
RETURN concat(user_metadata->>'name', ' <', email, '>: ', password);
END;
$function$
get_random_user returns jsonb CREATE OR REPLACE FUNCTION public.get_random_user()
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
response jsonb;
BEGIN
select content::json->'results'->0
into response
from http_get('https://randomuser.me/api/');
return response::jsonb;
END;
$function$
generate_random_password(int) CREATE OR REPLACE FUNCTION public.generate_random_password(length integer)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
characters text;
password text := '';
i integer;
BEGIN
characters := 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
FOR i IN 1..length LOOP
password := password || substr(characters, (random() * length(characters) + 1)::integer, 1);
END LOOP;
RETURN password;
END;
$function$
|
Beta Was this translation helpful? Give feedback.
-
|
PostgrestException(message: function gen_salt(unknown) does not exist, code: 42883, details: any clue ? |
Beta Was this translation helpful? Give feedback.
-
|
Hello everyone, I also constantly get the following error: I tried to set search_path, definer, .. no result. P.S. If I run |
Beta Was this translation helpful? Give feedback.
-
|
Ok! Seems now it works if cast the results like below: |
Beta Was this translation helpful? Give feedback.
-
|
Looks like you haven't added extensions schema to exposed vars and you have to call the function with the schema name |
Beta Was this translation helpful? Give feedback.
-
|
Seeding users stopped working after upgrading to latest CLI Had to update the create_user to. Found the fix on #19620 (comment) |
Beta Was this translation helpful? Give feedback.
-
|
This works for me! But I run into an issue if I try to create users using the function within the seed.sql file. Say I add this after the function above: When running But if I run the same code against the database directly after running the seed.sql, then it works fine. Any idea why it's not possible to use the function within seed.sql? Does it behave the same way for the rest of you? |
Beta Was this translation helpful? Give feedback.
-
|
I had a need to create many users, and I didn't want to use the API. Here's my solution: DROP FUNCTION IF EXISTS generate_password;
CREATE OR REPLACE FUNCTION generate_password() RETURNS TEXT AS $$
BEGIN
RETURN trim(both from (encode(decode(md5(random()::text || current_timestamp || random()),'hex'),'base64')), '=');
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION generate_password IS 'returns a pseudo-secure password. Do not use for important stuff';
-- Email was not confirmed, this function can confirm users
DROP FUNCTION IF EXISTS auth.create_user;
CREATE OR REPLACE FUNCTION auth.create_user (
email TEXT,
password TEXT = null,
metadata JSONB = '{}'::JSONB,
confirm BOOL = true
) RETURNS UUID AS $$
declare
user_id uuid;
encrypted_pw text;
app_metadata jsonb;
confirmation timestamp;
BEGIN
user_id := gen_random_uuid();
encrypted_pw := crypt(password, gen_salt('bf'));
app_metadata := '{"provider":"email","providers":["email"]}'::jsonb || metadata::jsonb;
CASE
WHEN confirm THEN
confirmation := now();
ELSE
confirmation := null;
END CASE;
INSERT INTO auth.users
(instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, recovery_sent_at, last_sign_in_at, confirmation_sent_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, confirmation, confirmation, confirmation, confirmation, app_metadata, '{}', now(), now(), '', '', '', '');
INSERT INTO auth.identities
(provider_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', now(), now(), now());
RETURN user_id;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION auth.create_user IS 'Adds a user. This is NOT supposed to be used in production, only for testing';
DROP FUNCTION IF EXISTS auth.assign_pass_to_user;
CREATE OR REPLACE FUNCTION auth.assign_pass_to_user(
user_id UUID,
password TEXT
)RETURNS BOOL AS $$
declare
encrypted_pw text;
BEGIN
UPDATE auth.users SET encrypted_password = crypt(password, gen_salt('bf')) WHERE auth.users.id = user_id;
RETURN true;
END;
$$ LANGUAGE plpgsql;
COMMENT ON FUNCTION auth.create_user IS 'Changes a user''s password.';I can now create users in bulk this way: SELECT
fn.user_uid AS user_uid,
fn.user_email AS user_email,
fn.user_password AS user_password
FROM
(
SELECT
inputs.user_password AS user_password,
inputs.user_email AS user_email,
auth.create_user (
email => user_email,
password => user_password,
metadata => '{"some_prop":"something"}'
) AS user_uid
FROM
(
SELECT
user_email,
generate_password () as user_password
FROM
unnest(ARRAY[ 'test_1@test.com', 'test_2@test.com' ]) as user_email
) AS inputs
) as fn;Not sure if this is idiomatic, but I wasn't managing to run the |
Beta Was this translation helpful? Give feedback.
-
|
I ended up exporting the data from a development session after manually logging in and creating the entries pg_dump \
-h localhost \
-p 54322 \
-U postgres -W \
--table="auth.users" \
--data-only \
--column-inserts
pg_dump \
-h localhost \
-p 54322 \
-U postgres -W \
--table="auth.identities" \
--data-only \
--column-insertsIf you are using different config from the default dev, you can get those via |
Beta Was this translation helpful? Give feedback.
and others
Fine, I'll do it myself. With blackjack and hookers.