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.
