postgres pgcrypt - ghdrako/doc_snipets GitHub Wiki

• https://neon.tech/docs/extensions/pgcrypto
• pgcrypto is a set of hashing and encryption functions that allow you to do things like hash a password using blowfish (bcrypt)

CREATE EXTENSION pgcrypt;

select encode(digest('tajnytekst', 'sha256'), 'hex');
select encode(digest(nr_karty, 'sha256'), 'hex') CARD_HASH_ID from cards;

CREATE OR REPLACE FUNCTION register(login varchar(50), email varchar(50), password varchar(50), ip inet)
returns TABLE (
  new_id bigint,
  message varchar(255),
  email varchar(255),
  email_validation_token varchar(36)
)
AS
$$
DECLARE
  new_id bigint;
  message varchar(255);
  hashedpw varchar(255);
  validation_token varchar(36);
BEGIN

  --hash the password using pgcrypto
  SELECT crypt(password, gen_salt('bf', 10)) into hashedpw;

  --create a random string for the
  select substring(md5(random()::text),0, 36) into validation_token;

  --create the member. Email has a unique constraint so this will
  --throw. You could wrap this in an IF if you like too
  insert into members(email, created_at, email_validation_token)
  VALUES(email, now(), validation_token) returning id into new_id;

  --set the return message
  select 'Successfully registered' into message;

  --add login bits to logins
  insert into logins(member_id, provider, provider_key, provider_token)
  values(new_id, 'local',email,hashedpw);

  --add auth token to logins
  insert into logins(member_id, provider, provider_key, provider_token)
  values(new_id, 'token',null,validation_token);

  -- add them to the members role which is 99
  insert into members_roles(member_id, role_id)
  VALUES(new_id, 99);

  --add log entry
  insert into logs(subject,entry,member_id, ip, created_at)
  values('registration','Added to system, set role to User',new_id, ip, now());

  --return out what happened here with relevant data
  return query
  select new_id, message, new_email, success, validation_token;

END
$$ LANGUAGE plpgsql;