Support of "#" in the password part of a DSN

[glenfant]

• asyncpg version: 0.20.1
• PostgreSQL version: 11
• Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
  the issue with a local PostgreSQL install?: Whatever PG hosting
• Python version: 3.8
• Platform: MacOS and Linux (maybe Windows)
• Do you use pgbouncer?: No
• Did you install asyncpg with pip?: Yes
• If you built asyncpg locally, which version of Cython did you use?: NA
• Can the issue be reproduced under both asyncio and
  uvloop?: Yes, and FastAPI too

My company DBA provided me an access to a database with a "#" inside the password. That's legal according to PG passwords rules. This makes a DSN like :

dsn = "postgresql://username:sH#iy!tyPW@somehost:5432/database"

A short run with iPython, psycopg2 and asyncpg :

In [1]: dsn = "postgresql://username:sH#iy!tyPW@somehost:5432/database"         

In [2]: import psycopg2, asyncpg                                                

In [3]: psycopg2.extensions.parse_dsn(dsn)                                      
Out[3]: 
{'user': 'username',
 'password': 'sH#iy!tyPW',
 'dbname': 'database',
 'host': 'somehost',
 'port': '5432'}

In [4]: # This is correct and the cryptic password is here                      

In [5]: await asyncpg.create_pool(dsn=dsn)                                      
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-5-72ee7c4f9e6c> in async-def-wrapper()

~/.local/share/virtualenvs/andi/lib/python3.7/site-packages/asyncpg/pool.py in _async__init__(self)
    396         self._initializing = True
    397         try:
--> 398             await self._initialize()
    399             return self
    400         finally:

[... TRUNCATED ...]

~/.local/share/virtualenvs/andi/lib/python3.7/site-packages/asyncpg/connect_utils.py in _parse_hostlist(hostlist, port, unquote)
    194                 if unquote:
    195                     hostspec_port = urllib.parse.unquote(hostspec_port)
--> 196                 hostlist_ports.append(int(hostspec_port))
    197             else:
    198                 hostlist_ports.append(default_port[i])

ValueError: invalid literal for int() with base 10: 'sH'

In [6]: # Appears that it took the first letters before the "#" for port nb     

In [7]: # Looking at line 213 of asyncpg.connect_utils, there's a use of        

In [8]: # urllib.parse.urlparse                                                 

In [9]: import urllib                                                           

In [10]: urllib.parse.urlparse(dsn)    # Will fail parsing as I expected                                         
Out[10]: ParseResult(scheme='postgresql', netloc='username:sH', path='', params='', query='', fragment='iy!tyPW@somehost:5432/database')

In [11]: # Using a password without "#" works correctly                         

In [12]: urllib.parse.urlparse("postgresql://username:password@hostname:5432/dat
    ...: abase")                                                                
Out[12]: ParseResult(scheme='postgresql', netloc='username:password@hostname:5432', path='/database', params='', query='', fragment='')

I don't know if this is a stdlib issue, but I think you should take a copy of psycopg2 DSN parser that works as expected and replace urllib.parse.urlparse with it.

Thanks again for asyncpg.

[elprans]

You must use URL-safe encoding for the elements of the URI with urliib.parse.quote

[glenfant]

OK ! I was somehow confused since the same DSN worked with psycopg2. Thank you @elprans for the hint.
Feel free to close this issue if you won't "fix" it.

[takeda]

@glenfant it's also recommended to add safe='' to the quote e.g. quote(password, safe='') otherwise later you might run into a similar issue with passwords containing /.