Timing-safe SQL authentication in postgresql

authenticate.sql

-- This is a constant time query for authenticating password-based credentials
-- The key is that the bcrypt work is done whether or not the user key (email)
-- matches so the query takes the same time whether or not there was a matching
-- record. In this example, the supplied credentials are:
-- `[email protected]` / `password`

-- This was written for postgres with pgcrypto

with

-- select either the id and password digest matching the email, or a fake row
target_user as (
 	select * from (
 		select id, password_digest from users where email = '[email protected]'
 		union all
 		-- this plaintext of this password is irrelevant, since it has a null user id
 		-- it won't match a real user record in the last query
 		select null, '$2a$10$ptpyYs8PlMelqmI4UHdb4e8EF4LQrKp5231X6g3/uOY4ckcFxnHk.'
 	) users limit 1
),

-- perform bcrypt matching on the guaranteed single row from target_user
valid_user as (
	select id from target_user where password_digest = crypt('password', password_digest)
)

-- select the row from the users table matching the authenticated id
select * from users natural join valid_user limit 1