🧩 Configuring PostgreSQL as Auth0 Custom Database
Tags: database, datascience, security
Published: 2021-10-14 14:21:30

#### 📓 Connect Auth0 to PostgreSQL to create and maintain your own user store.

### <br>

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](https://oauth.net/2/) 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](https://auth0.com/docs/users/import-and-export-users) and adds an OAuth and [OpenID Connect](https://openid.net/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](https://auth0.com/pricing).

## Enabling Custom Databases

Don't already have an Auth0 account yet? [Sign up for free right now!](https://auth0.com/signup/)

Once you [log in](https://auth0.com/auth/login), navigate to the [Database connections page](https://manage.auth0.com/#/connections/database) (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.

![Enabling 'use my own database' on custom database tab of Auth0 interface](https://images.ctfassets.net/23aumh6u8s0i/OGyNXnaAOAwmDmJeSS29o/f2462f845f94aba70a41caac89f0d495/01_8enable-custom-database.png)

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](https://auth0.com/docs/connections/database/custom-db/templates) 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](https://www.ibm.com/cloud/learn/dbaas). In this tutorial, I'll use [ElephantSQL](https://www.elephantsql.com/), 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](https://aws.amazon.com/rds/postgresql/), [Google Cloud](https://cloud.google.com/sql/docs/postgres/quickstart) or [Heroku](https://devcenter.heroku.com/articles/heroku-postgresql).

![Elephant sql home page](https://images.ctfassets.net/23aumh6u8s0i/67kI5tanFdPenloG7UPWmo/a9901332759fdf407562eedfefce6f0a/02_5elephant-sql.png)

Head over to [https://www.elephantsql.com/](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**.

![Elephantsql create instance confirmation page](https://images.ctfassets.net/23aumh6u8s0i/2KypO18fJdOwAUAUzAvEaI/f0637c4914da6ed6c106f203bf1651a2/03_0create-new-instance.png)

If you encounter any error in creating an instance, refer to the [ElephantSQL documentation](https://www.elephantsql.com/docs/) 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.

![Click on show button and copy the 'URL' on the ElephantSQL instance details page](https://images.ctfassets.net/23aumh6u8s0i/6ygHuLT6dz2nL3E7rLGprV/5ac2748f274fec16755aa97402506126/04_9instance-details.png)

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

![List of IP addresses under action scripts](https://images.ctfassets.net/23aumh6u8s0i/7z4pzq4dETiiHaHk3hUY1T/8e22ded36e15b4eaffa7588d65ed30a3/05_3database-ips.png)

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

```sql
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
);
```

![creating a new table using SQL command in SQL Browser](https://images.ctfassets.net/23aumh6u8s0i/57cmWLADlzyKQcMgIj7hVk/f4758d8ccdc9bcc214b9b30da842b2fe/06_1create-table.png)

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.

[Read more...](https://auth0.com/blog/configuring-postgresql-as-auth0-custom-database/?utm_source=content_synd&utm_medium=sc&utm_campaign=database)