Database

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:


_10
create extension if not exists wrappers with schema extensions;

Enable the Auth0 Wrapper

Enable the auth0_wrapper FDW:


_10
create foreign data wrapper auth0_wrapper
_10
handler auth0_fdw_handler
_10
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.


_10
-- Save your Auth0 API key in Vault and retrieve the `key_id`
_10
insert into vault.secrets (name, secret)
_10
values (
_10
'auth0',
_10
'<Auth0 API Key or PAT>' -- Auth0 API key or Personal Access Token (PAT)
_10
)
_10
returning key_id;

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:


_10
create server auth0_server
_10
foreign data wrapper auth0_wrapper
_10
options (
_10
api_key_id '<key_ID>' -- The Key ID from above.
_10
);

Create a schema

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


_10
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

ObjectSelectInsertUpdateDeleteTruncate
Users✅❌❌❌❌

Usage


_10
create foreign table auth0.my_foreign_table (
_10
name text
_10
-- other fields
_10
)
_10
server auth0_server
_10
options (
_10
object 'users'
_10
);

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.


_10
create foreign table auth0.auth0_table (
_10
created_at text,
_10
email text,
_10
email_verified bool,
_10
identities jsonb
_10
)
_10
server auth0_server
_10
options (
_10
object 'users'
_10
);

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


_10
select * from auth0.auth0_table;