Auth0 Home Blog Docs

Several question regarding custom databases and CI

signup
testing
deployment
custom-database
rails-api

#1

Hello! We’re considering to use Auth0 in our project but I’m hesitant about it. First, a little bit of a context: we’re building a Rails API backend with SPA applications, Android, and iOS clients. We’ll start with a monolith but have plans to evolve into micro-services.

We want to own all user account data in our PostgreSQL database and make it a single source of truth.

Auth0 provides an option to use own DB as an Identity Provider (IdP). The documentation (https://auth0.com/docs/connections/database/custom-db) states that we have to “Provide database action scripts to configure the database for use as an identity provider” of “Legacy DB” types. Here come the issues that worry me:

  1. The login script is mandatory and requires two parameters: email and password. PostgreSQL template has a function definition that expects “email” but what if I don’t want to use email, but a unique login/username field? Should I just substitute email in the SQL query with another field? How can we make sure that login will be passed into that function?

  2. The code of those scripts is out of version control system (git). It has a SQL query and some basic error handling both of which may change at some point in the future. How can we guarantee atomic deploys? What if we migrated database schema - shall we go to the dashboard and edit the script right after deployment?

  3. Let’s assume that we have a lot of business logic involved during the signup process. Shall we just provide our own API that will create a new record in the database? Or do we also need to call Auth0 authentication API (https://auth0.com/docs/api/authentication#signup) before/after that (again, email is a required field there but we need a login)?

  4. How we can setup a Continuous Integration pipeline with integration tests which involve authentication? Can we generate a valid token locally or should we always call Auth0 API?

  5. If we want to restrict an access to the database from the outside world shall we write our own API for that and use that API in “database action scripts” or maybe there is a quicker/easier solution?

Looking forward to your reply!


#3

Hi @dikond! This is a pretty hefty question and there’s only so much I can help with in terms of how you architect your app but I’ll do what I can to answer your questions.

  1. what if I don’t want to use email, but a unique login/username field?

The script calls it “email” in several examples but that’s just the login name that’s being used. If you turn on “Requires Username” in the database connection, you’re able to use either email or username and that value, during the login, will be passed along to that script for validation.

  1. Should I just substitute email in the SQL query with another field?

Up to you what you want to call it but you’ll want to make sure that the DB script is looking for the right thing there. If you allow both username and email and those are stored in different columns, you’ll need logic (in your app or in the script, likely the former in your case) that will return the data you expect in the script.

  1. How can we make sure that login will be passed into that function?

Whatever is input in the login form will be passed to that script. If you ask for a username, it will be that, if you ask for an email, it’s that, if you allow either then you’ll need to figure out what to do with it if those are in different columns.

  1. How can we guarantee atomic deploys? What if we migrated database schema - shall we go to the dashboard and edit the script right after deployment?

The Management API has an endpoint to update a Connection:

With the ID of your DB connection, you can update the DB script on deployment if needed. Here’s how the data is structured for one of my test connections:

You can see that customScripts.login and customScripts.get_user both contain JS for the DB scripts.

That said, pushing escaped JSON via API feels a bit … brittle to me so I think the solution here is to just make sure your DB script in Auth0 is as thin as possible. Here is an example script that hits an endpoint and returns JSON that’s used in the script:

You can see that as long as the object being returned keeps the same shape, your DB script won’t need to change if your database does. Keep all that logic in your endpoint and you should not have an issue. If you need more/different data stored in Auth0, then that will need to change but not if your database changes.

  1. Let’s assume that we have a lot of business logic involved during the signup process. Shall we just provide our own API that will create a new record in the database?

I’m not sure if I totally understand your question here … but you have a number of options on how to do this. The recommended/most secure way is to use the Universal Login Page to authenticate the user, then do everything after that in your app, assuming it succeeds.

  1. Or do we also need to call Auth0 authentication API (https://auth0.com/docs/api/authentication#signup) before/after that?

That’s another option … if the user does not exist in your database, call that endpoint to create the user. Next time around when they login, you can use the Resource Owner (RO) Password grant with this endpoint to retrieve tokens.

  1. How we can setup a Continuous Integration pipeline with integration tests which involve authentication? Can we generate a valid token locally or should we always call Auth0 API?

Good question. We have integration tests written for our SDKs that can test many different scenarios against our APIs. You’ll get rate limited if you’re running several tests per second but if you watch the headers or mock/record the calls (watch out for sensitive data being generated and stored), then you should be fine. We typically:

  1. Create a test user with the signup endpoint you mentioned before
  2. Use this user to test where possible (RO grant, for example)

How you do it depends on the tests you’re running. You should be validating the ID token and, if you’re using RS256 tokens in your app (which we recommend), there’s no way to generate usable tokens on your end so you’ll either need to stub the validation method or go another direction.

  1. If we want to restrict an access to the database from the outside world shall we write our own API for that and use that API in “database action scripts” or maybe there is a quicker/easier solution?

That’s what I would go with, yes, and that’s what the examples above cover.

Hope that helps!


#4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.