Auth0

Auth0 is a flexible, drop-in solution to add authentication and authorization services to your applications

The Auth0 Wrapper allows you to read data from your Auth0 tenant for use within your Postgres database.

Preparation#

Before you can query Auth0, you need to enable the Wrappers extension and store your credentials in Postgres.

Enable Wrappers#

Make sure the wrappers extension is installed on your database:

1
create extension if not exists wrappers with schema extensions;

Enable the Auth0 Wrapper#

Enable the auth0_wrapper FDW:

1
create foreign data wrapper auth0_wrapper
2
  handler auth0_fdw_handler
3
  validator auth0_fdw_validator;

Store your credentials (optional)#

By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.

1
-- Save your Auth0 API key in Vault and retrieve the created `key_id`
2
select vault.create_secret(
3
  '<Auth0 API Key or PAT>', -- Auth0 API key or Personal Access Token (PAT)
4
  'auth0',
5
  'Auth0 API key for Wrappers'
6
);

Connecting to Auth0#

We need to provide Postgres with the credentials to connect to Auth0, and any additional options. We can do this using the create server command:

1
create server auth0_server
2
  foreign data wrapper auth0_wrapper
3
  options (
4
    url 'https://dev-<tenant-id>.us.auth0.com/api/v2/users',
5
    api_key_id '<key_ID>' -- The Key ID from above.
6
  );

Create a schema#

We recommend creating a schema to hold all the foreign tables:

1
create schema if not exists auth0;

Entities#

The Auth0 Wrapper supports data reads from Auth0 API.

Users#

The Auth0 Wrapper supports data reads from Auth0's Management API List users endpoint endpoint (read only).

Operations#

Usage#

1
create foreign table auth0.my_foreign_table (
2
  name text
3
  -- other fields
4
)
5
server auth0_server
6
options (
7
  object 'users'
8
);

Notes#

• Currently only supports the users object

Query Pushdown Support#

This FDW doesn't support query pushdown.

Limitations#

This section describes important limitations and considerations when using this FDW:

• No query pushdown support, all filtering must be done locally
• Large result sets may experience slower performance due to full data transfer requirement
• Only supports the users object from Auth0 Management API
• Cannot modify Auth0 user properties via FDW
• Materialized views using these foreign tables may fail during logical backups

Examples#

Basic Auth0 Users Query#

This example demonstrates querying Auth0 users data.

1
create foreign table auth0.auth0_table (
2
  created_at text,
3
  email text,
4
  email_verified bool,
5
  identities jsonb
6
)
7
  server auth0_server
8
  options (
9
    object 'users'
10
  );

You can now fetch your Auth0 data from within your Postgres database:

1
select * from auth0.auth0_table;