[CT-2860] [Feature] Add AWS IAM Authentication to dbt-postgres

[christopherscholz]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

Describe the feature

As with dbt-redshift, I would like to enable dbt-postgres to use AWS IAM Authentication. I use IAM Authentication for nearly everything and it just bugs me that it is available with dbt-redshift but not with dbt-postgres.

I envision the same setup as dbt-redshift. With a connection factory that returns a connection function. This would easily allow other implementations as well.

The following profile configurations would change

• password becomes optional
• method is added: default database; options: database, iam
• iam_profile is added: default None, used to overwrite default AWS IAM profile
• region is added: default None, used to overwrite default AWS Region

If IAM is chosen as method, then before connecting to the PostgreSQL db using psycopg2.connect, it would

• open boto3 session
• create rds client
• get the auth token by calling generate_db_auth_token and pass it to kwargs as password

Describe alternatives you've considered

Instead of implementing it in dbt, you could create a wrapper for dbt, which gets the token and passes it as a environment variable to dbt. Using env_var in the profile I could use this token as password. But this token could only be used once. If during the run, the connection has to be reopened, this wouldn't work. Therefore I really don't like this this idea.

Who will this benefit?

Everybody who is running dbt in AWS and is using IAM authentication for RDS PostgreSQL.

Are you interested in contributing this feature?

Yes; Since this is my first contribution to dbt, please give me some guidance.

Anything else?

There is a closed issue dbt-labs/dbt-core#3122, which was closed because it became stale. The author is asking for a implementation for IAM Authentication with Amazon Aurora PostgreSQL. There is no difference in the IAM Authentication implementation for Amazon RDS Aurora PostgreSQL and Amazon RDS for PostgreSQL. Both work exactly the same.

[jtcohen6]

My concern is that this sets precedent for making dbt-postgres less vendor-agnostic. Would we need to support auth methods for GCP Cloud SQL, Azure PostgreSQL, etc? What about all the added dependencies (boto3, etc)? Those would probably need to go into extras_require, i.e.

pip install dbt-postgres[aws]
pip install dbt-postgres[gcp]
pip install dbt-postgres[azure]
...etc...

I'll let @Fleid and @dataders make the ultimate call here. There are, no doubt, a lot of people using dbt-postgres + AWS RDS out in the world. This risks adding some dependency bloat & maintenance burden to an adapter that we otherwise try to keep pretty lightweight & vanilla.

[Fleid]

@graciegoheen your take would be valuable too.

The way I see it, Amazon RDS for PostgreSQL is not PostgreSQL, which is also not Azure Database for PostgreSQL, etc. The main variation being authentication, which for us in dbt land is kind of a big deal, but each vendor/provider has the ability to add/remove more than that (like Citus for Azure Cosmos DB for PostgreSQL) which may need to be surfaced in dbt.

So I'd much rather have long-lived shallow forks of dbt-postgres being developed and maintained out there, with the additional authentication methods living there. That could, and should, be the only difference in those forks compared to the origin, to be able to stay aligned as easily as possible.

But that requires us to move dbt-postres out of dbt-core, which last I checked was in the plans. If I'm not mistaken @jtcohen6?

[graciegoheen]

But that requires us to move dbt-postres out of dbt-core, which last I checked was in the plans. If I'm not mistaken @jtcohen6?

https://github.com/dbt-labs/dbt-postgres indeed!

[roitalpaz]

We would love to see this happen as well. should we fork at our own organization? or would you prefer this to live at dbt-labs?

We can create a new fork which will be called dbt-rds-postgres with support of short lived aws IAM tokens.

[EitanKatsightfull]

@christopherscholz I have forked the repository and created this in our org every session that is opened to the database has a fresh token https://github.com/sightfullco/dbt-postgres-rds/tree/feature/postgresrds-adapter

[colin-rogers-dbt]

Closing this out as we view this as outside the scope of dbt-postgres as an adapter.