Find better alternative than Lambda function for provisioning database roles

[lorenyu]

There are some downsides with using Lambda for provisioning database roles:

• not language agnostic (the lambda is using python)
• code written in general purpose languages are more of a liability and harder to maintain than less expressive code (declarative code or SQL)
• the lambda deploy process is a bit hacky – either requiring a local provisioner to pip install python requirements pg8000, or to check the dependencies into source control

Some alternatives that could be considered:

1. Using ECS Exec

... TODO fill in details

2. Using AWS's newly launched data APIs

see this recent email from AWS:

Hello,

You are receiving this notification because you had one or more Amazon Aurora PostgreSQL-Compatible Edition clusters that were accessed through RDS Data API in 2023. We are launching a new redesigned Data API for Aurora PostgreSQL-Compatible Edition that now supports Aurora Serverless v2 (ASv2) and Aurora provisioned database instances. This API also no longer enforces a 1,000 request per second (RPS) rate limit. You can now access these Aurora clusters via the Data API's secure HTTP endpoint and run SQL statements without the use of database drivers and without managing connections. The existing RDS Data API for Aurora Serverless v1 (ASv1) will continue to function unchanged with ASv1 clusters.

Data API will be initially available in US-EAST-1 (Northern Virginia), US-WEST-2 (Oregon), EU-CENTRAL-1 (Frankfurt), and AP-NORTHEAST-1 (Tokyo) regions with more regions being added after this launch. You are encouraged to upgrade from Aurora Serverless v1 (ASv1) to take advantage of the performance and scalability of Aurora Serverless v2 (ASv2). You will be able to use your existing applications with the redesigned Data API when you upgrade. You can find additional information needed to plan your upgrade in the Data API section of the Aurora documentation [1].

You are also advised that the redesigned RDS Data API uses AWS CloudTrail data plane events [2] to log activity instead of management events, as was the case with Data API for ASv1. You may enable data events logging through the CloudTrail console, CLI, or SDK if you want to track this activity. This will incur charges as set forth on the CloudTrail pricing page [3]. The redesigned RDS Data API also incurs a charge as described on the Aurora pricing page [4]. As a courtesy to you as an existing customer who made use of RDS Data API for ASv1 in 2023, you will automatically receive quarterly billing credits for up to 100 million Data API calls per month, not including any Cloudtrail charges, to offset these charges in calendar years 2024 and 2025.

If you have any questions or concerns, please contact AWS Support [5].

[1] https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/data-api.html [2] https://docs.aws.amazon.com/awscloudtrail/latest/userguide/logging-data-events-with-cloudtrail.html#logging-data-events [3] https://aws.amazon.com/cloudtrail/pricing/ [4] https://aws.amazon.com/rds/aurora/pricing/ [5] https://aws.amazon.com/support

[rocketnova]

@lorenyu I think we spent some time talking about this in the fall, but the context and details have escaped me. I could use a refresher on your current thoughts on this one.

[lorenyu]

If the solution you have for using ECS as jump host (#148 ) works for this use case then I'd suggest we use that approach and leverage raw SQL files which are more language agnostic. For example we'd have a folder of SQL files /infra/modules/database/role_manager/sql/ and files like provision-users--postgres.sql that gets run as the postgres user to provision the app and migrator roles, as well as files like default-privileges--migrator.sql that gets run as the migrator user to set default privileges. Not entirely sure how to port over the "check db roles" functionality but perhaps we can leverage SQL for that too. But I'm more ok with having language specific code for test code.

[lorenyu]

Concept: if we can run SQL from lambda then we can put the SQL in the app config module

[coilysiren]

Concept: if we can run SQL from lambda then we can put the SQL in the app config module

Seems like we could write a custom "SQL runtime" for lambda that accomplishes this. Similar to, or derived from, the bash runtime described here: https://stackoverflow.com/a/53849964

I'm not convinced that that would be a better solution than ECS exec, though. ECS exec seems like a more "conventional" way to handle this.