Black Lives Matter

Neon Law

Cognito and Postgres

In this blog post, we will walk through using AWS Cognito, a service that authenticates or identifies users, with Postgres, a SQL database used to store data.

AWS Cognito is an IAM-as-a-service, meaning that you can use Cognito in your applications to identify who is making a request. AWS Cognito it pairs well with AWS's AppSync and Lambda because when set up properly, your API functions will be called with a "current user" parameters. For instance, if I have a GraphQL query listBuckets that is protected by an AWS Cognito User Pool, I am always guaranteed to know who made the listBuckets request because that user must have been previously authenticated by AWS Cognito.

PostgreSQL, or simply Postgres, is a SQL data store with a long history of open source contributions. It is battle-tested and is used in production at many companies, and has been for years. When adding authentication logic to a project that uses Postgres as a datastore, database administrators typically add a User table, and tables for authorization, which answer questions like "Is this person a user?" In a Role table that is joined to a User table.

When writing applications with AppSync and Lambda, you have the option of connecting to a PostgreSQL database and using it as a datastore. For instance, in our earlier example of listBuckets, the list of buckets can come from a Postgres query SELECT * FROM buckets. If you only wanted admin users to access that list of buckets, you could search your Postgres table for the user based on the information from Cognito, check if they're an admin, and return the list of buckets if and only if that user is admin.

In this article, we will go over how to set up Cognito, AppSync, Lambda, and Postgres, and how to sync users in AWS Cognito and your Postgres instance, so that you can seamlessly return the correct logic based on who that user is, and what type of authorizations that user has.

CloudFormation for Cognito, AppSync, Lambda, and Postgres

AWS CloudFormation is a way you can declare what AWS resources you want to use.

In these templates a Cognito, an AppSync API and two lambda functions for Cognito handling and API logic get created. Please note that these functions require a database URL, or a Postgres database URL, to be inputted into these templates as a parameter.

Additionally, the Cognito template makes an assumption that the username of the users authenticating into the Cognito User Pool and subsequently your application is their email address. This email address must be confirmed by confirming a code sent to that email before a user is authorized to log on. It also creates authorization groups, which can be returned to the client from Cognito. In this case, the admin group is created. If we sync our user roles from our database to Cognito upon creating a user role, then we do not need to make a trip to the database when looking up the user's roles. Instead, that information will be immediately available when the user authenticates into Cognito.

Database Design

In order to prepare our database for our Cognito users, we should create the following tables with the following column.

  • A User Table
    • The primary ID
    • The Sub, or primary ID from Cognito
    • email address, stored in citext, or case insensitive text.
    • the phone number of the user
  • A Role Table
    • The primary ID
    • The unique name of the Role
  • A User/Role Join Table
    • The Primary ID of the User
    • The Primary ID of the Role

Using the Post Confirmation Hook From Cognito to Create a User in Your Database

Within Cognito User Pools, you can set up Lambda functions to be called when certain hooks happen. One of those hooks is the "Post Confirmation Lambda Trigger" hook which fires right after Cognito confirms a user's email address when a user enters the correct code sent to their email. In that hook, you can create a row in your User table containing the user's email and sub passed in from Cognito.

Adding a user to a group after creating a user role

When creating authorizations for a user, you can first do it by saving it to the database. Since the database is usually the ultimate source of truth in data-driven applications, it should always reflect your true intent of what user should have that role. After creating or deleting a user role in your Postgres database, you can then reflect that user's authorizations in Cognito by adding or removing them to a group. In Node, the code to add a user to a group looks like this:

import * as AWS from 'aws-sdk';
AWS.config.update({ region: 'us-east-1' });
const createUserRole = async () => {
await saveUserRoleToPostgres();
await cognitoIdentityServiceProvider
UserPoolId: userPoolId,
Username: email,


And that's it! By now you should be able to authenticate users into your application and have an accurate reflection of what users and authorizations exist in your application's data-store, which in this article was Postgres. You can create similar tables in other datastores to achieve a similar syncing of data from Cognito to your application.

Neon Law

All content presented herein is for informational purposes only. Nothing should be construed as legal advice. Transmission and receipt of this information is not intended to create and does not constitute, an attorney-client relationship with lawyers on this platform. There is no expectation of attorney-client privilege or confidentiality of anything you may communicate to us in this forum. Do not act upon any information presented without seeking professional counsel.

Currently viewing this site in English

SupportPro BonoUpward Mobility
Join our e-mail list.
Sign up for our e-mail list, the Neon Law Monthly.

Copyright © 2021 Shook Law PLLC

this website was crafted by Nisar