Configuring PostgreSQL as Auth0 Custom Database

You can use Auth0 to create and maintain your own user store while also providing a secure authentication system on top of it. Auth0 lets you store users in your own database. Furthermore, Auth0 uses OAuth 2.0 as an authorization framework with which you can be confident in terms of security. Using a custom database as a user store in Auth0 facilitates user migration and adds an OAuth and OpenID Connect layer to your existing user database. You can configure any database or web service, including ASP.NET Membership Provider, MongoDB, MySQL, PostgreSQL, as an Auth0 custom database. In this article, you will learn how to set up PostgreSQL as a custom database for Auth0.

Limited Access: Some features are available depending on your Auth0 subscription. For more information, see Auth0 pricing plans.

Enabling Custom Databases

Don't already have an Auth0 account yet? Sign up for free right now!

Once you log in, navigate to the Database connections page (Auth0 Dashboard > Authentication > Database). Click on the Create DB Connection button, give your database a nice name, and click the Create button. Go to the Custom Database tab and enable Use my own database to configure the custom database.

Auth0 uses Node.js scripts, called Action Scripts, for connecting and accessing your database. These scripts include JavaScript functions that allow you to execute a variety of tasks such as adding a new user, logging in a user, and so on. You should define Custom Database Action Scripts to perform the following actions:

• Create: The Create script inserts a new user with

  email

  and

  password

  into your PostgreSQL database.
• Login: The Login script verifies the identity of the user in your PostgreSQL database whenever a user needs to authenticate.
• Verify: The Verify script updates the verification status of a user's email address in your database.
• Change Password: The Change Password script modifies the password for the user's email address. It changes the user's password in the database for future logins (forgot password functionality).
• Get User: The Get User script retrieves a specified user's information from your database. It returns

  undefined

  if the user is not present in your database.
• Delete: The Delete script removes the specified user identity from your database.

Fortunately, Auth0 provides templates for these custom database scripts. You can use the template and update the connection string with your database URL. Refer to the official documentation for additional information on Auth0 custom database action scripts. Let's start by creating a PostgreSQL database to connect with Auth0.

Setting up a PostgreSQL Database

You cannot use the database in your local environment. You should make your database accessible via the internet or use tools like Database as A Service. In this tutorial, I'll use ElephantSQL, which offers a fully configured and cloud-hosted PostgreSQL database in few minutes. However, it does not display any data inside your database; instead, you can run SQL commands to show your data. You can design your own database and host it on any cloud provider like AWS, Google Cloud or Heroku.

Head over to https://www.elephantsql.com/ and create an account for yourself or log in if you already have one. It takes you to the dashboard page; Click on the Create New Instance to create a new database instance. Give a suitable name to your database instance and choose a plan and a region. I'll use the free plan for demonstration purposes. Configure the database to meet your requirements, then click Create instance.

If you encounter any error in creating an instance, refer to the ElephantSQL documentation for troubleshooting.

Now, navigate to the freshly created database instance and copy the URL from the details page. You'll need this to connect your Auth0 application to this database. Anyone can access your database with this connection string. It would help if you were to keep it very secret for security purposes.

PS: Auth0 provides several IP addresses to configure your database network connections if you're using a firewall. You can find them under the action scripts.

Note: Using your own database leads to several security concerns. Allowing your database accessible over the internet without any firewalls or security measures may result in data leakage.

Creating a Table for Storing Users

Switch to the Browser tab and execute the following command.

CREATE TABLE users(
    id UUID DEFAULT uuid_generate_v4(),
    nickname VARCHAR(255),
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email_Verified BOOLEAN DEFAULT FALSE
);

The above command creates a new table called

users

to store the user information from Auth0. You need at least the above five columns to save and validate the user identity.

Defining the Auth0 Custom Database Scripts

Open the custom database tab in the Auth0 interface and navigate to the Database Action Scripts section. Select a script (say Login) to edit and choose PostgreSQL from the templates drop-down. To connect your database to Auth0, you should add the connection string (

conString

) of your database in the action scripts.

Scroll down a little to the Database Settings under the Action scripts section. Add the database URL you copied from your ElephantSQL as an environment variable (to keep it as a secret).

Now, update the

conString

in your action scripts with this value.

- const conString = 'postgres://user:pass@localhost/mydb';
+ const conString = configuration.POSTGRES_URL;

Remember to add the connection string in every database action script, and that's all! Now you can click on Save and Try button to test your database.

Note: You can also link an existing user database to Auth0. You should modify some code in the action scripts to match your database configuration.

Testing the Database

First, try creating a user in the database using Create script.

You can find the user in your database. Open the SQL Browser in ElephantSQL and run the following command. It retrieves the first 100 records from the database containing information about the users.

SELECT * FROM "public"."users" LIMIT 100

Similarly, login, validate your email and delete the user. For deleting a user, you should use the unique id of the specific user. Test all the scripts and use this database in applications that you create on the Auth0 dashboard. You can select to use this database on Connections tab in your application. If you experience any problem through the whole process, reach out to the Auth0 community for assistance.

Conclusion

In this article, you learned how to set up your PostgreSQL database as an Auth0 custom database. Even if you have a different type of database, you can easily connect to Auth0 using the templates provided. In this tutorial, you merely changed the Auth0 templates. Modify the action scripts accordingly if you need to keep more information about the user or want to configure the already existing user store as Auth0 custom database.

Using a custom database introduces several security issues as well as additional configuration work. You should keep your database in a secure cloud to prevent data leakage. You can rely on Auth0 if you don't want to put in any extra effort. It saves your users' information in very secure databases (see how Auth0 stores data?).