Clerk

Clerk is a complete suite of embeddable UIs, flexible APIs, and admin dashboards to authenticate and manage users.

The Clerk Wrapper is a WebAssembly(Wasm) foreign data wrapper which allows you to read data from Clerk for use within your Postgres database.

Available Versions#

Preparation#

Before you can query Clerk, 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 Clerk Wrapper#

Enable the Wasm foreign data wrapper:

1
2
3
create foreign data wrapper wasm_wrapper  handler wasm_fdw_handler  validator wasm_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
2
3
4
5
6
7
-- Save your Clerk API key in Vault and retrieve the `key_id`insert into vault.secrets (name, secret)values (  'clerk',  '<Clerk API key>' -- Clerk API key )returning key_id;

Connecting to Clerk#

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

1
2
3
4
5
6
7
8
9
10
create server clerk_server  foreign data wrapper wasm_wrapper  options (    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_clerk_fdw_v0.1.0/clerk_fdw.wasm',    fdw_package_name 'supabase:clerk-fdw',    fdw_package_version '0.1.0',    fdw_package_checksum '613be26b59fa4c074e0b93f0db617fcd7b468d4d02edece0b1f85fdb683ebdc4',    api_url 'https://api.clerk.com/v1',  -- optional    api_key_id '<key_ID>' -- The Key ID from above.  );

Note the fdw_package_* options are required, which specify the Wasm package metadata. You can get the available package version list from above.

Create a schema#

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

1
create schema if not exists clerk;

Options#

The full list of foreign table options are below:

• object - Object name in Clerk, required.

Supported objects are listed below:

Entities#

Allow-list#

This is a list of all identifiers allowed to sign up to an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create foreign table clerk.allowlist_identifiers (  id text,  invitation_id text,  identifier text,  identifier_type text,  instance_id text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'allowlist_identifiers'  );

Notes#

• The attrs column contains additional attributes in JSON format

Block-list#

This is a list of all identifiers which are not allowed to access an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
create foreign table clerk.blocklist_identifiers (  id text,  identifier text,  identifier_type text,  instance_id text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'blocklist_identifiers'  );

Notes#

• The attrs column contains additional attributes in JSON format

Domains#

This is a list of all domains for an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
create foreign table clerk.domains (  id text,  name text,  is_satellite boolean,  frontend_api_url text,  accounts_portal_url text,  attrs jsonb)  server clerk_server  options (    object 'domains'  );

Notes#

• The attrs column contains additional attributes in JSON format

Invitations#

This is a list of all non-revoked invitations for your application.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table clerk.invitations (  id text,  email_address text,  url text,  revoked boolean,  status text,  expires_at timestamp,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'invitations'  );

Notes#

• The attrs column contains additional attributes in JSON format

JWT Templates#

This is a list of all JWT templates.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table clerk.jwt_templates (  id text,  name text,  lifetime bigint,  allowed_clock_skew bigint,  custom_signing_key boolean,  signing_algorithm text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'jwt_templates'  );

Notes#

• The attrs column contains additional attributes in JSON format

OAuth Applications#

This is a list of OAuth applications for an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table clerk.oauth_applications (  id text,  name text,  instance_id text,  client_id text,  public boolean,  scopes text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'oauth_applications'  );

Notes#

• The attrs column contains additional attributes in JSON format

Organizations#

This is a list of organizations for an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
create foreign table clerk.organizations (  id text,  name text,  slug text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'organizations'  );

Notes#

• The attrs column contains additional attributes in JSON format

Organization Invitations#

This is a list of organization invitations for an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table clerk.organization_invitations (  id text,  email_address text,  role text,  role_name text,  organization_id text,  status text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'organization_invitations'  );

Notes#

• The attrs column contains additional attributes in JSON format

Organization Memberships#

This is a list of organization user memberships for an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
create foreign table clerk.organization_memberships (  id text,  role text,  role_name text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'organization_memberships'  );

Notes#

• The attrs column contains additional attributes in JSON format

Redirect URLs#

This is a list of all whitelisted redirect urls for the instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
create foreign table clerk.redirect_urls (  id text,  url text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'redirect_urls'  );

Notes#

• The attrs column contains additional attributes in JSON format

SAML Connections#

This is a list of SAML Connections for an instance.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create foreign table clerk.saml_connections (  id text,  name text,  domain text,  active boolean,  provider text,  user_count bigint,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'saml_connections'  );

Notes#

• The attrs column contains additional attributes in JSON format

Users#

This is a list of all users.

Ref: Clerk API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create foreign table clerk.users (  id text,  external_id text,  username text,  first_name text,  last_name text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'users'  );

Notes#

• The attrs column contains additional attributes in JSON format

Query Pushdown Support#

This FDW doesn't support query pushdown.

Supported Data Types#

The Clerk API uses JSON formatted data, please refer to Clerk Backend API docs for more details.

Limitations#

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

• Large result sets may experience slower performance due to full data transfer requirement
• Query pushdown is not supported
• Materialized views using these foreign tables may fail during logical backups

Examples#

Below are some examples on how to use Clerk foreign tables.

Basic example#

This example will create a "foreign table" inside your Postgres database and query its data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create foreign table clerk.users (  id text,  external_id text,  username text,  first_name text,  last_name text,  created_at timestamp,  updated_at timestamp,  attrs jsonb)  server clerk_server  options (    object 'users'  );-- query all usersselect * from clerk.users;

attrs is a special column which stores all the object attributes in JSON format, you can extract any attributes needed from it. See more examples below.

Query JSON attributes#

1
2
3
4
5
6
-- extract all email addresses from userselect  u.id,  e->>'email_address' as emailfrom clerk.users u  cross join json_array_elements((attrs->'email_addresses')::json) e;