How to efficiently combine user authentication with token authentication?

Question

What is the most efficient approach of authentication, considering an user based authentication system combined with a token based authentication system?

For this question please consider the following tables:

create table users (
 id int generated always as identity primary key,
 username text not null unique,
 password text not null
);
create table tokens (
 id int generated always as identity primary key,
 user_id int not null references users(id),
 access_token text unique default generate_random_token(),
 refresh_token text default generate_random_token(),
 updated_on timestamp with time zone default current_timestamp,
 created_on timestamp with time zone default current_timestamp
);

As far as I know to create an user and allow it to authenticate the following steps are mandatory (explained with query statements):

1. Optional: Create an user record.

   insert into users (username, password) values ('foo', create_password_hash('bar'));
   

2. Check if the user can authenticate itself and remember the user's ID:

   select * from users where username = 'foo' and password = validate_password_hash('bar', password);
   

3. Check if the user has an existing token and hand over the access_token:

   select access_token from tokens where user_id = 123;
   

4. Optional: Create a token if the user doesn't have one yet:

   insert into tokens (user_id) values (123) returning access_token;
   

If my assumption for the steps to be taken is right, then I wonder how to minimize these four statements into a single one or have at least some guidance how this could be done more efficient. Also I could imagine that my approach for this use case incorrect and that there is an other more efficient approach, please let me know.

---

BONUS

Perhaps your approach could also cover the process of refreshing a token. Considering the approach explained above the refresh should only occur when a token is found at the third step and according to the updated_on timestamp the token is "expired".

• jjanes: You want to create an account, or login to an account, without knowing ahead of time which you are doing? That sounds like a disaster. Really, how efficient does this need to be?
• luukvhoudt: @jjanes I guess step one can also be optional, would that make more sense?

Answer 1

Apparently this use case is fairly specific and thus it is quite a challenge to write an "upsert" statement what covers all the steps as described in the question. Therefor I see the following procedure as the only way out.

create or replace function user_auth(p_user text, p_pass text)
returns token_response as
$$
declare
 v_user_id bigint;
 v_token_id bigint;
 v_updated_on timestamp with time zone;
 v_access_token text;
 v_refresh_token text;
begin
 select id
 into v_user_id
 from users
 where username = p_user
 and password = validate_password_hash(p_pass, password)
 limit 1;
 if (v_user_id is not null) then
 select id, updated_on, access_token, refresh_token
 into v_token_id, v_updated_on, v_access_token, v_refresh_token
 from tokens
 where user_id = v_user_id
 order by updated_on desc
 limit 1;
 if (v_token_id is null) then
 insert into tokens (user_id)
 values (v_user_id)
 returning access_token, refresh_token
 into v_access_token, v_refresh_token;
 end if;
 if (v_updated_on < current_timestamp - interval '1 day') then
 update tokens
 set access_token = generate_random_token(),
 refresh_token = generate_random_token(),
 updated_on = current_timestamp
 where id = v_token_id
 returning access_token, refresh_token
 into v_access_token, v_refresh_token;
 end if;
 end if;
 return (v_access_token, v_refresh_token)::token_response;
end;
$$ language plpgsql;

Here is a working DB fiddle