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 can query AWS Cognito, 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 Cognito Wrapper

Enable the cognito_wrapper FDW:


_10
create foreign data wrapper cognito_wrapper
_10
handler cognito_fdw_handler
_10
validator cognito_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 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
);

Create a schema

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


_10
create schema if not exists cognito;

Entities

Users

This is an object representing Cognito User Records.

Ref: AWS Cognito User Records

Operations

ObjectSelectInsertUpdateDeleteTruncate
Users✅❌❌❌❌

Usage


_13
create foreign table cognito.users (
_13
username text,
_13
email text,
_13
status text,
_13
enabled boolean,
_13
created_at timestamp,
_13
updated_at timestamp,
_13
attributes jsonb
_13
)
_13
server cognito_server
_13
options (
_13
object 'users'
_13
);

Notes

  • Only the columns listed above are accepted in the foreign table
  • The attributes column contains additional user attributes in JSON format

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 User Pool objects from Cognito API
  • No support for Identity Pool operations
  • Materialized views using these foreign tables may fail during logical backups

Examples

Basic example

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


_13
create foreign table cognito.users (
_13
username text,
_13
email text,
_13
status text,
_13
enabled boolean,
_13
created_at timestamp,
_13
updated_at timestamp,
_13
attributes jsonb
_13
)
_13
server cognito_server
_13
options (
_13
object 'users'
_13
);

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


_10
select * from cognito.users;