SQLAlchemy refreshable credentials for IAM auth with Boto3

Question

I am connecting to Amazon RDS with Sqlalchemy using a Boto3 generated auth-token:

self.client = boto3.client("rds", region_name="eu-central-1")
self.token = self.client.generate_db_auth_token(
   self.endpoint, self.port, self.user
)

connection_string = (
   f"postgresql://{urlquote(self.user)}:"
   f"{urlquote(self.token)}@{self.endpoint}:{self.port}/dummy"
)

self.engine = db.create_engine(connection_string)

The problem is that the provided token is only valid for 15 minutes (I want to use temporary credentials!) and i don't now how i can tell SQLAlchemy to automatically create a new authentication token when the old one expires.

I found the creator argument, but this function seems not to be called when the token gets invalid:

def get_new_connection(self):
    self.token = self.client.generate_db_auth_token(
        self.endpoint, self.port, self.user
    )
    conn = psycopg2.connect(
        f"dbname='dummy' user='{self.user}' host='{self.endpoint}' password='{self.token}'"
    )
    return conn


 self.engine = db.create_engine(
 connection_string, creator=self.get_new_connection
 )

Is there a build in function for this, or a more elegant way to solve this?

I did not had time to further investigate this issue, maybe we can create a feature request. I will check again later and come back to you. — Chris, Oct 23 '20 at 09:52
we have been trying the same, if we ever manage to do so I will post something below. I find requesting a token at *every connection* not optimal. I also do not find any TTL parameter when the connection pool is created in SQLAlchemy. — Mattia Paterna, Sep 07 '21 at 13:16

score 5 · Answer 1 · answered May 17 '21 at 13:45

According to the SQLAlchemy documentation, the 'correct' way of working with volatile authentication credentials is to make use of the events system:

Generating dynamic authentication tokens

DialectEvents.do_connect() is also an ideal way to dynamically insert an authentication token that might change over the lifespan of an Engine. For example, if the token gets generated by get_authentication_token() and passed to the DBAPI in a token parameter, this could be implemented as:

from sqlalchemy import event

engine = create_engine("postgresql://user@hostname/dbname")

@event.listens_for(engine, "do_connect")
def provide_token(dialect, conn_rec, cargs, cparams):
    cparams['token'] = get_authentication_token()

There are multiple ways of working with the SQLAlchemy events system though, I urge you to read the documentation on how to work with events in the first place and pick the right implementation/mechanism for your specific need.

Based on this information I was able to generate a new RDS IAM password whenever the engine needed to establish a new connection.

what is the use of `dialect, conn_rec, cargs` inside `provide_token`? I do not understand how this function is used to update the connection pool and I can't find much inside SQLAlchemy docs. — Mattia Paterna, Sep 07 '21 at 13:02
@MattiaPaterna: The documentation doesn't talk about them, yes. If you really want to know what they do you'll have to crawl the source of SQLAlchemy. I didn't care too much as I only had to alter the cparams dict, which is documented in the links above. — Luminger, Sep 10 '21 at 13:54

SQLAlchemy refreshable credentials for IAM auth with Boto3

1 Answers1

Linked