AWS DSQL is a new serverless flavor of PostgreSQL. It only supports AWS IAM security credentials and these must be generated dynamically (they expire in 15 minutes). An IAM secret ID and secret KEY are presented to get a token with a 15 minute lifespan.
The Retool "standard" PostgreSQL resource type can be configured for IAM authentication and that path will accept an AWS access key ID / AWS secret key ID pair, but the resource test button then fails: "unable to accept connection, access denied".
@Darren confirmed during office hours that if one generates a key in the AWS console and presents that as a "password" (configuring the resource NOT as an IAM authentication configuration, but as a database username / password configuration), this "just works". However, the saved resource is only good for 15 minutes.
Apparently the AWS Boto3 SDK has some helper functionality that handles getting a key dynamically and refreshing it upon expiration.
There are mature Node.js libraries for dealing with AWS IAM so I went down the path of using them outside of Retool (a cloud function) and discovered that they too fail. DSQL is brand new and apparently only the Boto3 SDK (Python) is supported. I confirm this in two ways:
- Before I even bothered with Retool I used Databricks to load up the database. That code is using Boto3 and "just works".
- I then tried Retool's "Postgres Resource" and it failed.
- I then tried creating a cloud function using Node.js (which Retool could use as a REST API). But that too failed.
- I then tried building the REST API with a Python cloud function using Boto3 and it worked just like Databricks.
So... AWS DSQL has some really great features. I would encourage Retool to support it; but it will probably require a special Resource type or a special option on the Standard Postgres Resource.