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

[SeJun Bae]

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]

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

 

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/6a124e8b-fabc-47b6-8e68-db939acc98f7n%40googlegroups.com.

[SeJun Bae]

Yes, it does. Thank you for the explanation.