How to link auth-status to app database (postgres)

[arn]

Hi,

This has probably been asked thousands of times, but after multiple attempts last year(s) and a lot of reading I still can’t find a proper solution. Maybe someone can help me.

What is the ‘best’ way to handle API calls to my own API to make sql-queries?

My setup (let’s say it is a todo-app):

• 1 auth0 application with database connection
• App-backend: nodejs API built with express and postgresql database.
• App-database: postgresql database with 2 tables: user (id, auth0) and todo (id, title, status, user_id).

Questions:

1. When to create the user in the local db? Now I use a rule to create new user using a post-request (api.todo.com/user) with a secret in the authorization header. I store the postgres.user.id in the user’s app_metadata. This seems to work (except that error handling should be improved).

   // An auth0 Rule to create a new local accout when the Auth0 acount is not yet connected.
   function (user, context, callback) {
   
     // Fetch app metadata
     user.app_metadata = user.app_metadata || {};
   
     // If local_id exists, retusn
     if (user.app_metadata.local_id) {
       return callback(null,user,context);
     }
   
     // otherwise create a local account 
     var request = require('request@2.56.0');
   
     // Create authorization header
     var secret = configuration.LOCAL_DB_SECRET;
     var authorizationHeader = 'Basic: ' + secret; 
     var url = configuration.LOCAL_DB_URL; 
         
     var payload = {
       'user_id': user.user_id,
       'email': user.email
     };
     var options = {
       method: 'POST',
       url: url + '/user',
       headers: {
         'content-type': 'application/json',
         'accept': 'application/json',
         'Authorization': authorizationHeader
       },
       body: JSON.stringify(payload)
     };
   
     request(options, function(err, response, body) {
       if (err) return callback(err);
       if (response.statusCode !== 200) return callback(new Error(body));
       // Parse the incoming data.
       var data = JSON.parse(body);
       var local_id = data.id;
       if (!local_id) { return callback(); }
   
       user.app_metadata.local_id = local_id;
       auth0.users.updateAppMetadata(user.user_id, user.app_metadata);
   
       return callback(null, user, context);
     });
   }
   ```
   

2. How to grant access to the local API? I read that this has to be done with the access_token. I also want to send the local_id with the request in the token, because I want to query by primary key (dbuser.id) instead of the auth0 value (db.user.auth0);
   Right now I add the app_metadata to the id_token with a rule:

   function (user, context, callback) {
      const namespace = configuration.METADATA_NAMESPACE;
      if (context.idToken && user.user_metadata) {
        context.idToken[namespace + '/user_metadata'] = user.user_metadata;
      }
      if (context.idToken && user.app_metadata) {
        context.idToken[namespace + '/app_metadata'] = user.app_metadata;
      }
      callback(null, user, context);
    }
   

I am using Ionic 4 for the frontend, a native app, and a PWA in the browser. My guess is that it should be possible that the frontend makes a request to the backend-api (api.todo.com/item), with a jwt that can be validated by the api, and the user.id. So that only a single db-query is needed: SELECT * FROM items WHERE user.id=token.user_id.

Somehow I can’t seem to find out how (or if) this is possible. Any advice is welcome!

[arn]

To follow up on my own questions:
This answer pointed me in the right direction: Instead of
audience: 'https://tentant.eu.auth0.com/userinfo' I added audience: 'api' (which is the name of the custom API I added in the Auth0 console.
This results in receiving a proper JWT for the accessToken, instead of a short string.

I updated the rule to:

function (user, context, callback) {
  
  const namespace = configuration.METADATA_NAMESPACE;

  context.idToken = context.idToken || {}; 
  context.accessToken = context.accessToken || {}; 
  user.app_metadata = user.app_metadata || {}; 
  user.user_metadata = user.user_metadata || {}; 
  
  context.idToken[ namespace + '/user_metadata'] = user.user_metadata;
  context.idToken[ namespace + '/app_metadata'] = user.app_metadata;
  
  context.accessToken[ namespace + '/local_id'] = user.app_metadata.local_id;
  
  callback(null, user, context);
}

This seems to work properly. The local_id is added to the access_token.

[nicolas_sabena]

There’s of course no “right” answer when it comes to system design, but I think I would flip the responsibilities around here. Particularly, updating the application’s data store from a rule has a certain code smell.

Auth0, as the identity provider and OAuth2 authorization server will authenticate the user and issue access tokens to the application, that represent the permission that the user gives to the application to access the user’s resources (the To-Do list, in this case). But the chores of keeping the backend’s database in shape would be better done at the backend (the API), maybe driven by the application using the API.

A new user lands in the app after authenticating with the identity provider. You get the user id (the sub) from Auth0. But the access token won’t have a “local user id”, because it’s a new user.

Now, does the to-do list need any kind of initialization (e.g. do you need any specific data or does the provisioning take some time)? Then you might provide an API for that:

POST /api/initialize

This would be the only endpoint available at this point. All the other endpoints for the API would do this check: does a local user exist for the sub in the access token? If not, deny access.

The handler for /initialize might create the local user, associate it with the Auth0’s user id, and maybe update the user profile on the Auth0 side, so that the next time you can include it in the access token. Your client app could re-authenticate the user at this point if the other API endpoints rely on the local user id being available on the token.

But note that having the local user id is just an optimization, you don’t really need it because you can simply use the relationship that you have in the data store, and fetch the local user id based on the sub provided in the token (and you can optimize your system to be good at this).

If you don’t want/need an initialization API, then you can probably do the initialization on the first access. E.g. on the handler for GET /api/todo you can have a logic like this:

• Do I have a local user with a matching Auth0’s user id?
  • Yes? then get the local user id and return the list.
  • No? then create a new local user and return an empty list.

Hope that makes some sense.

[arn]

Hi Nicolas! Thanks for taking the time to answer my questions. It is very helpful.
Updating the app-DB with an auth0-rule indeed doesn’t feel like the way to go, but I couldn’t figure out what was a better solution.

Based on your info, what I’ve done now:

• Updated my app-API code to query based on the sub, and not relying on the ‘local-id’. This is indeed an optimization and (at least at this point) not required. (instead of a WHERE user_id={id} now I have a JOIN user ON user.auth0={sub}, but then using sequelize)
• I rely on the fact that the app will always start with 1 specific request (e.g. /api/todo). This logic will create a new entry to the local user-table if needed. Like you explained in the last paragraph.

Thanks! Because this makes the overall design a lot less complex. This, combined with the fact that I am finally able to receive the proper accessToken in my app-API, seem to make it work!

[nicolas_sabena]

Great! Thanks for updating on the results, and glad it helped!