taylor.town about now spam rss

Easy Email Tokens in Pure Postgres

  1. Generate a secret.
  2. Add the token function.
  3. Create/send tokens.
  4. Verify tokens.

1. Generate a secret.

Generate a random string using Bash…

openssl rand -base64 32

…or Postgres:

select string_agg(substr(c, (random() * length(c) + 1)::integer, 1), '')
from (values('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789')) as x(c)
, generate_series(1, 32)

2. Add the token function.

create function email_token (ts timestamptz, email text) returns text
  language sql
  immutable
  returns null on null input
  return ''
    || extract(epoch from ts)::bigint
    || ':'
    || encode(
         sha256(( email || 'SECRET' || extract(epoch from ts)::bigint )::bytea),
         'base64'
       )
    ;

Make sure to replace SECRET with your secret from step 1!

With the code above each token will look something like this:

1695683829:Kr/BYcOyBifA6RvSp+EoK0isN7XkKk/dGkFHr27JKG8=

Feel free to use hex instead of base64, or sha512 instead of sha256. Be wary of md5.

3. Create/send tokens.

Generate email tokens when you register new users…

with u as (
  insert into usr (email, password)
  values ($1, crypt($2, gen_salt('bf', 8)))
  returning *
)
select usr_id, email_token(now(), email) from u;

…or use the function ad-hoc for any flow you need:

select email_token(now(), '[email protected]');

You'll need to send your user a link like this at some point:

<a href="/verify-token?token=TOKEN">verify email</a>

4. Verify tokens.

Use the following logic on an endpoint like /verify-token:

const token = request.url.searchParams.get("token");
const [usr] = await sql<{ usr_id: string }[]>`
  update usr
  set email_verified_at = now()
  where email_verified_at is null
    and to_timestamp(split_part(${token},':',1)::bigint) > now() - interval '2 days'
    and ${email} = email
    and ${token} = email_token(
      to_timestamp(split_part(${token},':',1)::bigint),
      email
    )
  returning usr_id
`;

There are multiple ways to pass ${email} into this query: