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.


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".

  • 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?
    – jjanes
    Jul 23, 2020 at 12:15
  • @jjanes I guess step one can also be optional, would that make more sense?
    – luukvhoudt
    Jul 24, 2020 at 10:11

1 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
    v_user_id bigint;
    v_token_id bigint;
    v_updated_on timestamp with time zone;
    v_access_token text;
    v_refresh_token text;
    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;
$$ language plpgsql;

Here is a working DB fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.