AWS RDS generate-db-auth-token and Redshift get-cluster-credentials

[Ryan Kelly]

Hi,

I am looking to use credentials provided by the above functionality from AWS. Basically, using either of these methods, you can obtain temporary credentials (for RDS, just password, and Redshift both username and password) that can be used to access the database. However, for long running processes, connection failures and subsequent reconnections as well as new connections initiated by the connection pool (or even just waiting a long time between generating the credentials and making your first connection) the credentials configured on a URL as passed to create_engine will eventually begin to fail.

At first I thought I'd simply subclass URL and make username/password properties that could be refreshed as needed, but digging into create_connection it seems like those properties are read out of the URL object and into cargs/cwargs and provided to pool as such.

I took then a roundabout approach or creating a proxy object that is capable of refreshing the value and using this object as the username/password, which only works because psycogp2 is helpfully calling str() on them as it constructs the connstring/dsn. Which... I mean, is an interesting, but also unsustainable, solution.

What I am asking, I suppose, is 1) am I missing something obvious that would make this achievable? and 2) if not, what kind of best-approach pull request could I produce that could make this happen?

Thanks,

-Ryan

[Mike Bayer]

You can modify how the engine makes connections using the do_connect event hook:

https://docs.sqlalchemy.org/en/13/core/events.html?highlight=do_connect#sqlalchemy.events.DialectEvents.do_connect

each time the engine/ pool go to make a new connection, you can affect all the arguments here, or return an actual DBAPI connection.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHUie25g0G5OPuyDHaNn8oWkTzizwQxGY0tnkaJvOewLMQR4DQ%40mail.gmail.com.

[Elmer de Looff]

For reference, we've used this engine strategy for a while, which seems to get the job done. We're strictly on Postgres so the code could do with some alterations to make it compatible with multiple backends, that's left as an exercise to the reader :-)

The main work is done in _rds_engine_creator() which gets the necessary (short-lived) credentials for the connection just before it's actually created. There's a couple of ways to do this, this is simply one that got us a nice hands-off result where all we needed was to provide a different engine strategy in the config. Adjust for your particular use case.

# Register this engine strategy somewhere in your imported models

class RdsEngineStrategy(PlainEngineStrategy):

name = 'rds'

def create(self, name_or_url, **kwargs):

"""Adds an RDS-specific 'creator' for the engine connection."""

engine_url = make_url(name_or_url)

kwargs['creator'] = self._rds_engine_creator(engine_url)

return super().create(engine_url, **kwargs)

def _rds_engine_creator(self, engine_url):

instance_id, region = engine_url.host.split('.')

connector = engine_url.get_dialect().dbapi().connect

rds = boto3.client('rds', region_name=region)

if self._rds_first_instance_by_name(rds, instance_id) is None:

raise ValueError('No RDS instances for the given instance ID')

def engine_func():

instance = self._rds_first_instance_by_name(rds, instance_id)

password = rds.generate_db_auth_token(

DBHostname=instance['Endpoint']['Address'],

DBUsername=engine_url.username,

Port=instance['Endpoint']['Port'])

return connector(

host=instance['Endpoint']['Address'],

port=instance['Endpoint']['Port'],

database=engine_url.database,

user=engine_url.username,

password=password,

sslmode='require')

return engine_func

def _rds_first_instance_by_name(self, client, name):

response = client.describe_db_instances(DBInstanceIdentifier=name)

return next(iter(response['DBInstances']), None)

# Make sure to actually register it

RdsEngineStrategy()

# Caller code

engine = sqlalchemy.create_engine("postgres://us...@instance-name.region/dbname", strategy="rds")

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d1bed41d-9fa2-4761-a963-c87720cf25b2%40www.fastmail.com.

--

Elmer

[Mike Bayer]

engine strategies are gone in 1.4 so you're going to want to make use of event and plugin hooks such as:

https://docs.sqlalchemy.org/en/13/core/connections.html?highlight=plugin#sqlalchemy.engine.CreateEnginePlugin

https://docs.sqlalchemy.org/en/13/core/events.html?highlight=do_connect#sqlalchemy.events.DialectEvents.do_connect

these two hooks are both available in all 1.x versions and if they are not sufficient for what you need, if you can let me know that would be great, strategies are already removed from master as these were not really the "public" hook.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAA7D1eG9e6uvpoPCHyDND-zjo3UVadDrhBCaNmUP_15vyKwreQ%40mail.gmail.com.

[Ryan Kelly]

Excellent, I’ll test with the below. Thanks for the advice!

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2aa50190-4a34-44f7-97c6-6923cfd5b656%40www.fastmail.com.