Connection issue with URL-encoded passwords in `do-connect` event

35 views
Skip to first unread message

SeJun Bae

unread,
Nov 3, 2023, 7:41:39 PM11/3/23
to sqlalchemy
Hello everyone,
I have encountered an odd behavior when using URL-encoded tokens as passwords for connections with Postgres; my application connects to a Postgres AWS RDS instance using a token that expires (IAM Authentication). Specifically, if I set `cparams['password']` to a URL-encoded value in the `do_connect` event, the connection fails. However, it succeeds if the value isn't URL encoded. This seems inconsistent with how the engine handles URL-encoded values. Is this the intended behavior ?
If not, would the team be open to a PR that calls `unquote` on the `cparams['password'] when it's changed to ensure consistency ? 
 
Here is a minimal example to illustrate the issue:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, event, text
from urllib.parse import quote

# Assume there is a db user `test` with some initial token
POSTGRES_HOST = 'localhost'
PORT = '5432'
USERNAME = 'test'
NEW_PASSWORD = '%2FH'

engine1 = create_engine(f"postgresql://{USERNAME}:some_token_that_expires@{POSTGRES_HOST}:{PORT}/esrf", echo=True)

@event.listens_for(engine1, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs, cparams):
    # This doesn't work.
    cparams['password'] = quote(NEW_PASSWORD)

with engine1.connect() as connection:
    connection.execute(text('SELECT 1;'))

engine2 = create_engine(f"postgresql://{USERNAME}:some_token_that_expires@{POSTGRES_HOST}:{PORT}/esrf", echo=True)

@event.listens_for(engine2, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs, cparams):
    # This works
    cparams['password'] = NEW_PASSWORD

with engine2.connect() as connection:
    connection.execute(text('SELECT 1;'))



Mike Bayer

unread,
Nov 4, 2023, 4:09:32 AM11/4/23
to noreply-spamdigest via sqlalchemy


On Fri, Nov 3, 2023, at 7:41 PM, SeJun Bae wrote:
Hello everyone,
I have encountered an odd behavior when using URL-encoded tokens as passwords for connections with Postgres; my application connects to a Postgres AWS RDS instance using a token that expires (IAM Authentication). Specifically, if I set `cparams['password']` to a URL-encoded value in the `do_connect` event, the connection fails. However, it succeeds if the value isn't URL encoded. This seems inconsistent with how the engine handles URL-encoded values. Is this the intended behavior ?

the password needs to be url encoded, *when embedded into the URL*. When you handle the password as a single value in the parameters sent to the DBAPI, this needs to be the actual password, and not URL encoded, since it's not being extracted from a URL. This is consistent with the API for the URL object itself, where you have the option to create a URL progammatically from non-encoded fields (see https://docs.sqlalchemy.org/en/20/core/engines.html#creating-urls-programmatically).  hope this helps



If not, would the team be open to a PR that calls `unquote` on the `cparams['password'] when it's changed to ensure consistency ? 
 
Here is a minimal example to illustrate the issue:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine, event, text
from urllib.parse import quote

# Assume there is a db user `test` with some initial token
POSTGRES_HOST = 'localhost'
PORT = '5432'
USERNAME = 'test'
NEW_PASSWORD = '%2FH'

engine1 = create_engine(f"postgresql://{USERNAME}:some_token_that_expires@{POSTGRES_HOST}:{PORT}/esrf", echo=True)

@event.listens_for(engine1, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs, cparams):
    # This doesn't work.
    cparams['password'] = quote(NEW_PASSWORD)

with engine1.connect() as connection:
    connection.execute(text('SELECT 1;'))

engine2 = create_engine(f"postgresql://{USERNAME}:some_token_that_expires@{POSTGRES_HOST}:{PORT}/esrf", echo=True)

@event.listens_for(engine2, 'do_connect')
def receive_do_connect(dialect, conn_rec, cargs, cparams):
    # This works
    cparams['password'] = NEW_PASSWORD

with engine2.connect() as connection:
    connection.execute(text('SELECT 1;'))



--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
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.

SeJun Bae

unread,
Nov 4, 2023, 7:06:18 PM11/4/23
to sqlalchemy
Yes, it does. Thank you for the explanation.
Reply all
Reply to author
Forward
0 new messages