Excellent, I’ll test with the below. Thanks for the advice! On Fri, May 22, 2020 at 8:18 PM Mike Bayer <[email protected]> wrote:
> 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. > > On Fri, May 22, 2020, at 4:31 PM, Elmer de Looff wrote: > > 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://[email protected] > /dbname", strategy="rds") > > > On Fri, May 22, 2020 at 9:54 PM Mike Bayer <[email protected]> > wrote: > > > 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. > > > > > On Fri, May 22, 2020, at 1:39 PM, Ryan Kelly wrote: > > 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 > > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAHUie25g0G5OPuyDHaNn8oWkTzizwQxGY0tnkaJvOewLMQR4DQ%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAHUie25g0G5OPuyDHaNn8oWkTzizwQxGY0tnkaJvOewLMQR4DQ%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/d1bed41d-9fa2-4761-a963-c87720cf25b2%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/d1bed41d-9fa2-4761-a963-c87720cf25b2%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > > > > -- > > Elmer > > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAA7D1eG9e6uvpoPCHyDND-zjo3UVadDrhBCaNmUP_15vyKwreQ%40mail.gmail.com > <https://groups.google.com/d/msgid/sqlalchemy/CAA7D1eG9e6uvpoPCHyDND-zjo3UVadDrhBCaNmUP_15vyKwreQ%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/2aa50190-4a34-44f7-97c6-6923cfd5b656%40www.fastmail.com > <https://groups.google.com/d/msgid/sqlalchemy/2aa50190-4a34-44f7-97c6-6923cfd5b656%40www.fastmail.com?utm_medium=email&utm_source=footer> > . > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAHUie25q%2BGq3bDV6Si-CHMqcQtjyRnH4wyUr%3D8ahf2Cw9ZeTvA%40mail.gmail.com.
