Skip to content

Gravatar

Gravatar is a service for providing globally unique avatars.

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

This wrapper is contributed and supported by Automattic.

Available Versions

Version Wasm Package URL Checksum Required Wrappers Version
0.2.0 https://github.com/Automattic/gravatar-wasm-fdw/releases/download/v0.2.0/gravatar_fdw.wasm 5273ae07e66bc2f1bb5a23d7b9e0342463971691e587bbd6f9466814a8bac11c >=0.5.0

Preparation

Before you can query Gravatar, 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 Gravatar 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
-- Save your Gravatar API key in Vault and retrieve the created `key_id`
select vault.create_secret(
  '<Gravatar API key>', -- Gravatar API key
  'Gravatar',
  'Gravatar API key for Wrappers'
);

Connecting to Gravatar

We need to provide Postgres with the credentials to access Gravatar 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 gravatar_server
  foreign data wrapper wasm_wrapper
  options (
    fdw_package_url 'https://github.com/Automattic/gravatar-wasm-fdw/releases/download/v0.2.0/gravatar_fdw.wasm',
    fdw_package_name 'automattic:gravatar-fdw',
    fdw_package_version '0.2.0',
    fdw_package_checksum '5273ae07e66bc2f1bb5a23d7b9e0342463971691e587bbd6f9466814a8bac11c',
    api_url 'https://api.gravatar.com/v3',  -- optional
    api_key_id '<key_ID>' -- The Key ID from above.
  );

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
create server gravatar_server
  foreign data wrapper wasm_wrapper
  options (
    fdw_package_url 'https://github.com/Automattic/gravatar-wasm-fdw/releases/download/v0.2.0/gravatar_fdw.wasm',
    fdw_package_name 'automattic:gravatar-fdw',
    fdw_package_version '0.2.0',
    fdw_package_checksum '5273ae07e66bc2f1bb5a23d7b9e0342463971691e587bbd6f9466814a8bac11c',
    api_url 'https://api.gravatar.com/v3',  -- optional
    api_key '<Gravatar API key>'  -- Gravatar API key
  );

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 gravatar;

Options

The full list of foreign table options are below:

  • table - Component name in Gravatar, required.

Supported components are listed below:

Component name
profiles

Entities

We can use SQL import foreign schema to import foreign table definitions from Gravatar.

For example, using below SQL can automatically create foreign tables in the gravatar schema.

1
2
-- create all the foreign tables
import foreign schema gravatar from server gravatar_server into gravatar;

Profiles

This is the user profile provided by Gravatar.

Ref: Gravatar API docs

Operations

Component Select Insert Update Delete Truncate
profiles ✅ ❌ ❌ ❌ ❌

Usage

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
create foreign table gravatar.profiles (
  hash text,
  email text,
  display_name text,
  profile_url text,
  avatar_url text,
  avatar_alt_text text,
  location text,
  description text,
  job_title text,
  company text,
  verified_accounts jsonb,
  pronunciation text,
  pronouns text,
  timezone text,
  languages jsonb,
  first_name text,
  last_name text,
  is_organization boolean,
  links jsonb,
  interests jsonb,
  payments jsonb,
  contact_info jsonb,
  number_verified_accounts integer,
  last_profile_edit timestamp without time zone,
  registration_date timestamp without time zone,
  json jsonb
)
  server gravatar_server
  options (
    table 'profiles'
  );

Note

You can use import foreign schema statement to automatically create the profiles foreign table see above

Notes

  • The json column contains additional attributes in JSON format

Query Pushdown Support

This FDW only supports email equal condition (email = ) query pushdown and it is mandatory. For example,

1
2
3
4
select * from gravatar.profiles where email = 'email@example.com';

-- no result if the email equal condition is not specified
select * from gravatar.profiles

Supported Data Types

Postgres Data Type Gravatar Data Type
boolean Boolean
bigint Number
text String
timestamp Time
jsonb Json

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

Limitations

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

  • Only supports profiles component
  • Only email equal condition (email = ) query pushdown is supported
  • Materialized views using these foreign tables may fail during logical backups

Examples

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

Basic example

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

1
2
3
4
import foreign schema gravatar from server gravatar_server into gravatar;

-- query an user profile 
select * from gravatar.profiles where email = 'email@example.com';

json 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
select p.json->'section_visibility' as section_visibility
from gravatar.profiles p
where email = 'email@example.com';