password_auth.sql

-- This is an effective but naive approach to selecting an authenticated user from a users table.
-- The problem is that the query is (intentionally) slow if it has to check a digest,
-- but finishes very quickly if the email doesn't match, and so provides a mechanism for user enumeration.
select *
  from users
 where email = 'daniel@fone.net.nz'
   and password_digest = crypt('password', password_digest);

-- The key goal is to provide a roughly constant time query whether the email or the password is wrong
with
  -- Supply the id and digest of any user matching the email along with
  -- a null id and a dummy password to compare
  -- (the plaintext for the digest is irrelevant since the id will never match) 
  user_with_fallback as (
    select id, password_digest from users where email = 'daniel@fone.net.nz'
    union
    select null, '$2a$10$hyyEri8US5gHpMv9XddSYe9pfzdBXsIlCxpfHYmbr5GYYHxDVgiMa'
  ),
    
  -- Return the first (user id, password digest) tuple
  user_or_fallback as (
    select * from user_with_fallback order by id nulls last limit 1
  ),
  
  -- select the id supplied above where the digest matches the supplied password
  -- this is where the bulk of the query time is spent
  matching_auth as (
    select id from user_or_fallback where password_digest = crypt('password', password_digest)
  )

-- Query the user table based on the id which matches both the email and password
select * from users natural join matching_auth;