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.
Restoring a logical backup of a database with a materialized view using a foreign table can fail. For this reason, either do not use foreign tables in materialized views or use them in databases with physical backups enabled.
Preparation
Before you get started, make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
and then create the foreign data wrapper:
_10create 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.
_10insert into vault.secrets (name, secret)_10values (_10 'cognito_secret_access_key',_10 '<secret access key>'_10)_10returning 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:
_10create 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).
| Cognito | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Records | ✅ | ❌ | ❌ | ❌ | ❌ |
For example:
_10create foreign table cognito (_10 email text,_10 username text_10)_10server cognito_server_10options (_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, onlyusersis 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:
_10create foreign table cognito_table (_10 email text,_10 username text_10)_10server cognito_server_10options (_10 object 'users'_10);
You can now fetch your Cognito data from within your Postgres database:
_10select * from cognito_table;