Multi-Tenant Analytics with Auth0 and Cube

In this tutorial we'll learn how to secure web applications with industry-standard and proven authentication mechanisms such as JSON Web Tokens, JSON Web Keys, OAuth 2.0 protocol.

Security... Why bother?

TL;DR: In this guide, we'll learn how to secure web applications with industry-standard and proven authentication mechanisms such as JSON Web Tokens, JSON Web Keys, OAuth 2.0 protocol.

We'll start with an openly accessible, insecure analytical app and walk through a series of steps to turn it into a secure, multi-tenant app with role-based access control and an external authentication provider. We'll use Cube to build an analytical app and Auth0 to authenticate users.

"Why bother with security", that's a fair question! As a renowned security practitioner George Orwell coined, "All users are equal, but some users are more equal than others."

Usually, the need to secure an application is rooted in a premise that some users should be allowed to do more things than others: access an app, read or update data, invite other users, etc. To satisfy this need, an app should implement IAAA, i.e., it should be able to perform:

• Identification. Ask users "Who are you?"
• Authentication. Check that users really are who they claim to be
• Authorization. Let users perform certain actions based on who they are
• Accountability. Keep records of users' actions for future review

In this guide, we'll go through a series of simple, comprehensible steps to secure a web app, implement IAAA, and user industry-standard mechanisms:

• Step 0. Bootstrap an openly accessible analytical app with Cube
• Step 1. Add authentication with signed and encrypted JSON Web Tokens
• Step 2. Add authorization, multi-tenancy, and role-based access control with security claims which are stored in JSON Web Tokens
• Step 3. Add identification via an external provider with Auth0 and use JSON Web Keys to validate JSON Web Tokens
• Step 4. Add accountability with audit logs
• Step 5. Feel great about building a secure app 😎

Also, here's the live demo you can try right away. It looks and feels exactly like the app we're going to build., i.e., it lets you authenticate with Auth0 and query an analytical API. And as you expected, the source code is on GitHub.

Okay, let's dive in — and don't forget to wear a mask! 🤿

Step 0. Openly accessible analytical app

To secure a web application, we need one. So, we'll use Cube to create an analytical API as well as a front-end app that talks to API and allows users to access e-commerce data stored in a database.

Cube is an open-source analytical API platform that allows you to create an API over any database and provides tools to explore the data, help build a data visualization, and tune the performance. Let's see how it works.

The first step is to create a new Cube project. Here I assume that you already have Node.js installed on your machine. Note that you can also use Docker with Cube. Run in your console:

npx cubejs-cli create multi-tenant-analytics -d postgres

Now you have your new Cube project in the multi-tenant-analytics folder which contains a few files. Let's navigate to this folder.

The second step is to add database credentials to the .env file. Cube will pick up its configuration options from this file. Let's put the credentials of a demo e-commerce dataset hosted in a cloud-based Postgres database. Make sure your .env file looks like this, or specify your own credentials:

# Cube environment variables: https://cube.dev/docs/reference/environment-variables

CUBEJS_DB_TYPE=postgres
CUBEJS_DB_HOST=demo-db.cube.dev
CUBEJS_DB_PORT=5432
CUBEJS_DB_SSL=true
CUBEJS_DB_USER=cube
CUBEJS_DB_PASS=12345
CUBEJS_DB_NAME=ecom

CUBEJS_DEV_MODE=true
CUBEJS_WEB_SOCKETS=false
CUBEJS_API_SECRET=SECRET

The third step is to start Cube API. Run in your console:

npm run dev

So, our analytical API is ready! Here's what you should see in the console:

Please note it says that currently the API is running in development mode, so authentication checks are disabled. It means that it's openly accessible to anyone. We'll fix that soon.

The fourth step is to check that authentication is disabled. Open http://localhost:4000 in your browser to access Developer Playground. It's a part of Cube that helps to explore the data, create front-end apps from templates, etc.

Please go to the "Schema" tab, tick public tables in the sidebar, and click Generate Schema. Cube will generate a data schema which is a high-level description of the data in the database. It allows you to send domain-specific requests to the API without writing lengthy SQL queries.

Let's say that we know that e-commerce orders in our dataset might be in different statuses (processing, shipped, etc.) and we want to know how many orders belong to each status. You can select these measures and dimensions on the "Build" tab and instantly see the result. Here's how it looks after the Orders.count measure and the Orders.status dimension are selected:

It works because Developer Playground sends requests to the API. So, you can get the same result by running the following command in the console:

curl http://localhost:4000/cubejs-api/v1/load \
  -G -s --data-urlencode 'query={"measures": ["Orders.count"], "dimensions": ["Orders.status"]}' \
  | jq '.data'

Please note that it employs the jq utility, a command-line JSON processor, to beautify the output. You can install jq or just remove the last line from the command. Anyway, you'll get the result you're already familiar with:

‼️ We were able to retrieve the data without any authentication. No security headers were sent to the API, yet it returned the result. So, we've created an openly accessible analytical API.

The last step is to create a front-end app. Please get back to Developer Playground at http://localhost:4000, go to the "Dashboard App" tab, choose to "Create your Own" and accept the defaults by clicking "OK".

In just a few seconds you'll have a newly created front-end app in the dashboard-app folder. Click "Start dashboard app" to run it, or do the same by navigating to the dashboard-app folder and running in the console:

npm run start

You'll see a front-end app like this:

If you go to the "Explore" tab, select the Orders Count measure and the Orders Status dimension once again, you'll see:

That means that we've successfully created a front-end app that makes requests to our insecure API. You can also click the "Add to Dashboard" button to persist this query on the "Dashboard" tab.

Now, as we're navigating some dangerous waters, it's time to proceed to the next step and add authentication 🤿

Step 1. Authentication with JWTs

As we already know, the essence of authentication is making sure that our application is accessed by verified users, and not by anyone else. How do we achieve that?

We can ask users to pass a piece of information from the web application to the API. If we can verify that this piece of information is valid and it passes our checks, we'll allow that user to access our app. Such a piece of information is usually called a token.

JSON Web Tokens are an open, industry-standard method for representing such pieces of information with additional information (so-called claims). Cube, just like many other apps, uses JWTs to authenticate requests to the API.

Now, we're going to update the API to authenticate the requests and make sure the web application sends the correct JWTs.

First, let's update the Cube configuration. In the .env file, you can find the following options:

CUBEJS_DEV_MODE=true
CUBEJS_API_SECRET=SECRET

The first option controls if Cube should run in the development mode. In that mode, all authentication checks are disabled. The second option sets the key used to cryptographically sign JWTs. It means that, if we keep this key secret, only we'll be able to generate JWTs for our users.

Let's update these options (and add a new one, described in docs):

CUBEJS_DEV_MODE=false
CUBEJS_API_SECRET=NEW_SECRET
CUBEJS_CACHE_AND_QUEUE_DRIVER=memory

Instead of NEW_SECRET, you should generate and use a new pseudo-random string. One way to do that might be to use an online generator. Another option is to run this simple Python command in your console and copy-paste the result:

python -c 'import sys,uuid; sys.stdout.write(uuid.uuid4().hex)'

After that, save the updated .env file, stop Cube (by pressing CTRL+C), and run Cube again with npm run dev. You'll see a message without mentioning the Development Mode in the console and Developer Playground will no longer be present at localhost:4000.

Second, let's check that the web application is broken. 🙀 It should be because we've just changed the security key and didn't bother to provide a correct JWT. Here's what we'll see if we repeat the curl command in the console:

Looks legit. But what's that "Authorization header", exactly? It's an HTTP header called Authorization which is used by Cube to authenticate the requests. We didn't pass anything like that via the curl command, hence the result. And here's what we'll see if we reload our web application:

Indeed, it's broken as well. Great, we're going to fix it.

Finally, let's generate a new JWT and fix the web application. You can use lots of libraries to work with JWTs, but Cube provides a convenient way to generate tokens in the command line. Run the following command, substituting NEW_SECRET with your key generated on the first step:

npx cubejs-cli token --secret="NEW_SECRET" --payload="role=admin"

You'll see something like this:

The output provides the following insights:

• We've created a new JWT: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYWRtaW4iLCJ1Ijp7fSwiaWF0IjoxNjE1MTY1MDYwLCJleHAiOjE2MTc3NTcwNjB9.IWpKrqD71dkLxyJRuiii6YEfxGYU_xxXtL-l2zU_VPY (your token should be different because your key is different).
• It will expire in 30 days (we could control the expiration period with the --expiry option but 30 days are enough for our purposes).
• It contains additional information (role=admin) which we'll use later for authorization.

We can go to jwt.io, copy-paste our token, and check if it really contains the info above. Just paste your JWT in the giant text field on the left. You'll see something like this:

Did you miss those "30 days"? They are encoded in the exp property as a timestamp, and you surely can convert the value back to a human-readable date. You can also check the signature by pasting your key into the "Verify Signature" text input and re-pasting your JWT.

Now we're ready to fix the web application. Open the dashboard-app/src/App.js file. After a few imports, you'll see the lines like this:

const API_URL = "http://localhost:4000";
const CUBEJS_TOKEN = "SOME_TOKEN";
const cubejsApi = cubejs(CUBEJS_TOKEN, {
  apiUrl: `${API_URL}/cubejs-api/v1`
});

These lines configure the Cube client library to look for the API at localhost:4000 and pass a particular token. Change SOME_TOKEN to the JWT you've just generated and verified, then stop the web application (by pressing CTRL+C), and run it again with npm start. We'll see that the web application works again and passes the JWT that we've just added to the API with the Authorization header:

To double-check, we can run the same query with the same header in the console:

curl http://localhost:4000/cubejs-api/v1/load \
  -H 'Authorization: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYWRtaW4iLCJpYXQiOjE2MTUxNjUwNjAsImV4cCI6MTYxNzc1NzA2MH0.BNC8xlkB8vmuT0T6s1a5cZ3jXwhcHrAVNod8Th_Wzqw' \
  -G -s --data-urlencode 'query={"measures": ["Orders.count"], "dimensions": ["Orders.status"]}' \
  | jq '.data'

Make sure to check that if you remove the header or change just a single symbol of the token, the API returns an error, and never then result.

‼️ We were able to add authentication and secure the API with JSON Web Tokens. Now the API returns the result only if a valid JWT is passed. To generate such a JWT, one should know the key which is currently stored in the .env file.

Now, as we're becalmed, it's time to proceed to the next step and add authorization 🤿

Step 2. Authorization with JWTs

As we already know, the essence of authorization is letting users perform certain actions based on who they are. How do we achieve that?

We can make decisions about actions that users are permitted to perform based on the additional information (or claims) in their JWTs. Do you remember that, while generating the JWT, we've supplied the payload of role=admin? We're going to make the API use that payload to permit or restrict users' actions.

Cube allows you to access the payload of JWTs through the security context. You can use the security context to modify the data schema or support multi-tenancy.

First, let's update the data schema. In the schema/Orders.js file, you can find the following code:

cube(`Orders`, {
  sql: `SELECT * FROM public.orders`,

  // ...

This SQL statement says that any query to this cube operates with all rows in the public.orders table. Let's say that we want to change it as follows:

• "admin" users can access all data
• "non-admin" users can access only a subset of all data, e.g., just 10 %

To achieve that, let's update the schema/Orders.js file as follows:

cube(`Orders`, {
  sql: `SELECT * FROM public.orders ${SECURITY_CONTEXT.role.unsafeValue() !== 'admin' ? 'WHERE id % 10 = FLOOR(RANDOM() * 10)' : ''}`,

  // ...

What happens here? Let's break it down:

• SECURITY_CONTEXT.role allows us to access the value of the "role" field of the payload. With SECURITY_CONTEXT.role.unsafeValue() we can directly use the value in the JavaScript code and modify the SQL statement. In this snippet, we check that the role isn't equal to the "admin" value, meaning that a "non-admin" user sent a query.
• In this case, we're appending a new WHERE SQL statement where we compare the value of id % 10 (which is the remainder of the numeric id of the row divided by 10) and the value of FLOOR(RANDOM() * 10) (which is a pseudo-random number in the range of 0..9). Effectively, it means that a "non-admin" user will be able to query a 1/10 of all data, and as the value returned by RANDOM() changes, the subset will change as well.
• You can also directly check the values in the payload against columns in the table with filter and requiredFilter. See data schema documentation for details.

Second, let's check how the updated schema restricts certain actions. Guess what will happen if you update the schema, stop Cube (by pressing CTRL+C), run Cube again with npm run dev, then reload our web application.

Right, nothing! 🙀 We're still using the JWT with role=admin as the payload, so we can access all the data. So, how to test that the updated data schema works?

Let's generate a new token without the payload or with another role with npx cubejs-cli token --secret="NEW_SECRET" --payload="role=foobar", update the dashboard-app/src/App.js file, and reload our web application once again. Wow, now it's something... certainly less than before:

Third, let's check the same via the console. As before, we can run the following command with an updated JWT:

curl http://localhost:4000/cubejs-api/v1/load \
  -H 'Authorization: eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJyb2xlIjoiZm9vYmFyIiwiaWF0IjoxNjE1MTk0MTIwLCJleHAiOjE2MTUxOTc3NjEsImp0aSI6ImMxYTk2NTY1LTUzNzEtNDNlOS05MDg0LTk0NWY3ZTI3ZDJlZSJ9.FSdEweetjeT9GJsqRqEebHLtoa5dVkIgWX4T03Y7Azg' \
  -G -s --data-urlencode 'query={"measures": ["Orders.count"], "dimensions": ["Orders.status"]}' \
  | jq '.data'

Works like a charm:

Cube also provides convenient extension points to use security context for multi-tenancy support. In the most frequent scenario, you'll use the queryTransformer to add mandatory tenant-aware filters to every query. However, you also can switch databases, their schemas, and cache configuration based on the security context.

‼️ We were able to add authorization and use JWT claims to control the access to data. Now the API is aware of users' roles. However, right now the only JWT is hardcoded into the web application and shared between all users.

To automate the way JWTs are issued for each user, we'll need to use an external authentication provider. Let's proceed to the next step and add identification 🤿

Step 3. Identification via Auth0

As we already know, the essence of identification is asking users who they are. An external authentication provider can take care of this, allowing users to authenticate via various means (e.g., their Google accounts or social profiles) and providing complementary infrastructure and libraries to integrate with your app.

Auth0 is a leading identity management platform for developers, recently acquired by Okta, an even larger identity management platform. It securely stores all sensitive user data, has a convenient web admin panel, and provides front-end libraries for various frameworks. We'll use Auth0's integration with React but it's worth noting that Auth0 has integrations with all major front-end frameworks, just like Cube.

On top of that, Auth0 provides many advanced features:

• User roles — you can have admins, users, etc.
• Scopes — you can set special permissions per user or per role, e.g, to allow some users to change your app’s settings or perform particular Cube queries.
• Mailing — you can connect third-party systems, like SendGrid, to send emails: reset passwords, welcome, etc.
• Management — you can invite users, change their data, remove or block them, etc.
• Invites — you can allow users to log in only via invite emails sent from Auth0.

Auth0 allows you to implement an industry-standard OAuth 2.0 flow with ease. OAuth 2.0 is a proven protocol for external authentication. In principle, it works like this:

• Our application redirects an unauthenticated user to an external authentication provider.
• The provider asks the user for its identity, verifies it, generates additional information (JWT included), and redirects the user back to our application.
• Our application assumes that the user is now authenticated and uses their information. In our case, the user's JWT can be sent further to Cube API.

So, now it's time to use Auth0 to perform identification and issue different JWTs for each user.

First, let's set up an Auth0 account. You'll need to go to Auth0 website and sign up for a new account. After that, navigate to the "Applications" page of the admin panel. To create an application matching the one we're developing, click the "+ Create Application" button, select "Single Page Web Applications". Done!

Proceed to the "Settings" tab and take note of the following fields: "Domain", "Client ID", and "Client Secret". We'll need their values later.

Then scroll down to the "Allowed Callback URLs" field and add the following URL as its value: http://localhost:3000. Auth0 requires this URL as an additional security measure to make sure that users will be redirected to our very application.

"Save Changes" at the very bottom, and proceed to the "Rules" page of the admin panel. There, we'll need to create a rule to assign "roles" to users. Click the "+ Create Rule" button, choose an "Empty rule", and paste this script, and "Save Changes":

function (user, context, callback) {
  const namespace = "http://localhost:3000";
  context.accessToken[namespace] = {
    role: user.email.split('@')[1] === 'cube.dev' ? 'admin' : 'user',
  };
  callback(null, user, context);
}

This rule will check the domain in users' emails, and if that domain is equal to "cube.dev", the user will get the admin role. You can specify your company's domain or any other condition, e.g., user.email === 'YOUR_EMAIL' to assign the admin role only to yourself.

The last thing here will be to register a new Auth0 API. To do so, navigate to the "APIs" page, click "+ Create API", enter any name and cubejs as the "Identifier" (later we'll refer to this value as "audience").

That's all, now we're done with the Auth0 setup.

Second, let's update the web application. We'll need to add the integration with Auth0, use redirects, and consume the information after users are redirected back.

We'll need to add a few configuration options to the dashboard-app/.env file. Note that two values should be taken from our application's settings in the admin panel:

REACT_APP_AUTH0_AUDIENCE=cubejs
REACT_APP_AUTH0_DOMAIN=<VALUE_OF_DOMAIN_FROM_AUTH0>
REACT_APP_AUTH0_CLIENT_ID=<VALUE_OF_CLIENT_ID_FROM_AUTH0>

Also, we'll need to add Auth0 React library to the dashboard-app with this command:

npm install --save @auth0/auth0-react

Then, we'll need to wrap the React app with Auth0Provider, a companion component that provides Auth0 configuration to all React components down the tree. Update your dashboard-app/src/index.js file as follows:

  import React from 'react';
  import ReactDOM from 'react-dom';
  import { HashRouter as Router, Route } from 'react-router-dom';
  import ExplorePage from './pages/ExplorePage';
  import DashboardPage from './pages/DashboardPage';
  import App from './App';
+ import { Auth0Provider } from "@auth0/auth0-react";
  ReactDOM.render(
+   <Auth0Provider
+     audience={process.env.REACT_APP_AUTH0_AUDIENCE}
+     domain={process.env.REACT_APP_AUTH0_DOMAIN}
+     clientId={process.env.REACT_APP_AUTH0_CLIENT_ID}
+     scope={'openid profile email'}
+     redirectUri={process.env.REACT_APP_AUTH0_REDIRECT_URI || window.location.origin}
+     onRedirectCallback={() => {}}
+   >
      <Router>
        <App>
          <Route key="index" exact path="/" component={DashboardPage} />
          <Route key="explore" path="/explore" component={ExplorePage} />
        </App>
      </Router>
+   </Auth0Provider>,
  document.getElementById('root'));

The last change will be applied to the dashboard-app/src/App.js file where the Cube client library is instantiated. We'll update the App component to interact with Auth0 and re-instantiate the client library with appropriate JWTs when Auth0 returns them.

First, remove these lines from dashboard-app/src/App.js, we don't need them anymore:

- const API_URL = "http://localhost:4000";
- const CUBEJS_TOKEN = "<OLD_JWT>";
- const cubejsApi = cubejs(CUBEJS_TOKEN, {
-   apiUrl: `${API_URL}/cubejs-api/v1`
- });

After that, add the import of an Auth0 React hook:

+ import { useAuth0 } from '@auth0/auth0-react';

Finally, update the App functional component to match these code:

const App = ({ children }) => {
  const [ cubejsApi, setCubejsApi ] = useState(null);

  // Get all Auth0 data
  const {
    isLoading,
    error,
    isAuthenticated,
    loginWithRedirect,
    getAccessTokenSilently,
    user
  } = useAuth0();

  // Force to work only for logged in users
  useEffect(() => {
    if (!isLoading && !isAuthenticated) {
      // Redirect not logged users
      loginWithRedirect();
    }
  }, [ isAuthenticated, loginWithRedirect, isLoading ]);

  // Get Cube instance with accessToken
  const initCubejs = useCallback(async () => {
    const accessToken = await getAccessTokenSilently({
      audience: process.env.REACT_APP_AUTH0_AUDIENCE,
      scope: 'openid profile email',
    });

    setCubejsApi(cubejs({
      apiUrl: `http://localhost:4000/cubejs-api/v1`,
      headers: { Authorization: `${accessToken}` },
    }));
  }, [ getAccessTokenSilently ]);

  // Init Cube instance with accessToken
  useEffect(() => {
    if (!cubejsApi && !isLoading && isAuthenticated) {
      initCubejs();
    }
  }, [ cubejsApi, initCubejs, isAuthenticated, isLoading ]);

  if (error) {
    return <span>{error.message}</span>;
  }

  // Show indicator while loading
  if (isLoading || !isAuthenticated || !cubejsApi) {
    return <span>Loading</span>;
  }

  return <CubeProvider cubejsApi={cubejsApi}>
    <ApolloProvider client={client}>
      <AppLayout>{children}</AppLayout>
    </ApolloProvider>
  </CubeProvider>;
}

export default App;

Done! Now, you can stop the web application (by pressing CTRL+C), and run it again with npm start. You'll be redirected to Auth0 and invited to log in. Use any method you prefer (e.g., Google) and get back to your app. Here's what you'll see:

It appears that our application receives a JWT from Auth0, sends it to the API, and fails with "Invalid token". Why is that? Surely, because the API knows nothing about our decision to identify users and issue JWT via Auth0. We'll fix it now.

Third, let's configure Cube to use Auth0. Cube provides convenient built-in integrations with Auth0 and Cognito that can be configured solely through the .env file. Add these options to this file, substituting <VALUE_OF_DOMAIN_FROM_AUTH0> with an appropriate value from above:

CUBEJS_JWK_URL=https://<VALUE_OF_DOMAIN_FROM_AUTH0>/.well-known/jwks.json
CUBEJS_JWT_ISSUER=https://<VALUE_OF_DOMAIN_FROM_AUTH0>/
CUBEJS_JWT_AUDIENCE=cubejs
CUBEJS_JWT_ALGS=RS256
CUBEJS_JWT_CLAIMS_NAMESPACE=http://localhost:3000

After that, save the updated .env file, stop Cube (by pressing CTRL+C), and run Cube again with npm run dev. Now, if you refresh the web application, you should see the result from the API back, the full dataset or just 10 % of it depending on your user and the rule you've set up earlier:

‼️ We were able to integrate the web application and the API based on Cube with Auth0 as an external authentication provider. Auth0 identifies all users and generates JWTs for them. Now only logged-in users are able to access the app and perform queries to Cube. Huge success!

The only question remains: once we have users with different roles interacting with the API, how to make sure we can review their actions in the future? Let's see what Cube can offer 🤿

Step 4. Accountability with audit logs

As we know, the essence of accountability is being able to understand what actions were performed by different users.

Usually, logs are used for that purpose. When and where to write the logs? Obviously, we should do that for every (critical) access to the data. Cube provides the queryTransformer, a great extension point for that purpose. The code in the queryTransformer runs for every query before it's processed. It means that you can not only write logs but also modify the queries, e.g., add filters and implement multi-tenant access control.

To write logs for every query, update the cube.js file as follows:

// Cube configuration options: https://cube.dev/docs/config
module.exports = {
  queryTransformer: (query, { securityContext }) => {
    const { role, email } = securityContext;
    if (role === 'admin') {
      console.log(`User ${email} with role ${role} executed: ${JSON.stringify(query)}`);
    }
    return query;
  },
};

After that, stop Cube (by pressing CTRL+C), run it again with npm run dev, and refresh the web application. In the console, you'll see the output like this:

Surely you can use a more sophisticated logger, e.g., a cloud-based logging solution such as Datadog.

‼️ With minimal changes, we were able to add accountability to our app via a convenient Cube extension point. Moreover, now we have everything from IAAA implemented in our app: identification, authentication, authorization, accountability. JSON Web Tokens are generated and passed to the API, role-based access control is implemented, and an external authentication provider controls how users sign in. With all these, multi-tenancy is only one line of code away and can be implemented in minutes.

And that's all, friends! 🤿 I hope you liked this guide 🤗

Here are just a few things you can do in the end:

• go to the Cube repo on GitHub and give it a star ⭐️
• share a link to this guide on Twitter, Reddit, or with a friend 🙋‍♀️
• share your insights, feedback, and what you've learned about security, IAAA, Auth0, and Cube in the comments below ↓

P.S. I'd like to thank Aphyr for the inspiration for the fake "George Orwell" quote at the beginning of this guide.