Database

AWS Cognito


AWS Cognito is an identity platform for web and mobile apps.

The Cognito wrapper allows you to read data from your Cognito Userpool within your Postgres database.

Preparation

Before you get started, make sure the wrappers extension is installed on your database:


_10
create extension if not exists wrappers with schema extensions;

and then create the foreign data wrapper:


_10
create foreign data wrapper cognito_wrapper
_10
handler cognito_fdw_handler
_10
validator cognito_fdw_validator;

Secure your credentials (optional)

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


_10
insert into vault.secrets (name, secret)
_10
values (
_10
'cognito_secret_access_key',
_10
'<secret access key>'
_10
)
_10
returning key_id;

Connecting to Cognito

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


_10
create server cognito_server
_10
foreign data wrapper cognito_wrapper
_10
options (
_10
aws_access_key_id '<your_access_key>',
_10
api_key_id '<your_secret_key_id_in_vault>',
_10
region '<your_aws_region>',
_10
user_pool_id '<your_user_pool_id>'
_10
);

Creating Foreign Tables

The Cognito Wrapper supports data reads from Cognito's User Records endpoint (read only).

CognitoSelectInsertUpdateDeleteTruncate
Records

For example:


_10
create foreign table cognito (
_10
email text,
_10
username text
_10
)
_10
server cognito_server
_10
options (
_10
object 'users'
_10
);

Foreign table options

The full list of foreign table options are below:

  • object: type of object we are querying. For now, only users is supported

Query Pushdown Support

This FDW doesn't support query pushdown.

Examples

Some examples on how to use Cognito foreign tables.

Basic example

This will create a "foreign table" inside your Postgres database called cognito_table:


_10
create foreign table cognito_table (
_10
email text,
_10
username text
_10
)
_10
server cognito_server
_10
options (
_10
object 'users'
_10
);

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


_10
select * from cognito_table;