search

perseas / Pyrseas

Provides utilities for Postgres database schema versioning.
https://perseas.github.io/
BSD 3-Clause "New" or "Revised" License
395 stars 67 forks source link

dbtoyaml Fails When PG Password Has Spaces (Improper Escaping) #245

Open benjamin-kirkbride opened 2 years ago

benjamin-kirkbride commented 2 years ago

How to reproduce:

  1. Create PD DB with role with a password with spaces
  2. Try to connect

Example:

$ python -m pdb -c continue .venv/bin/dbtoyaml -H db.REDACTED.supabase.co -U postgres --password postgres
Password: 
Traceback (most recent call last):
  File "/usr/lib/python3.10/pdb.py", line 1726, in main
    pdb._runscript(mainpyfile)
  File "/usr/lib/python3.10/pdb.py", line 1586, in _runscript
    self.run(statement)
  File "/usr/lib/python3.10/bdb.py", line 597, in run
    exec(cmd, globals, locals)
  File "<string>", line 1, in <module>
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/bin/dbtoyaml", line 8, in <module>
    sys.exit(main())
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/database.py", line 443, in to_map
    self.from_catalog(True)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/database.py", line 349, in from_catalog
    self.db = self.Dicts(self.dbconn, single_db)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/database.py", line 92, in __init__
    self.schemas = SchemaDict(dbconn)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/__init__.py", line 633, in __init__
    self._from_catalog()
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/__init__.py", line 640, in _from_catalog
    for obj in self.fetch():
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/dbobject/__init__.py", line 682, in fetch
    self.query = self.cls.query(self.dbconn.version)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/database.py", line 77, in version
    self.connect()
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pyrseas/database.py", line 63, in connect
    super(CatDbConnection, self).connect()
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pgdbconn/dbconn.py", line 50, in connect
    raise exc
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/pgdbconn/dbconn.py", line 43, in connect
    self.conn = connect("%s%sdbname=%s%s%s" % (
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/psycopg2/__init__.py", line 121, in connect
    dsn = _ext.make_dsn(dsn, **kwargs)
  File "/home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/psycopg2/extensions.py", line 145, in make_dsn
    parse_dsn(dsn)
psycopg2.ProgrammingError: invalid dsn: missing "=" after "password" in connection info string

Uncaught exception. Entering post mortem debugging
Running 'cont' or 'step' will restart the program
> /home/bkirkbride/projects/shopping-list/pyrseas/.venv/lib/python3.10/site-packages/psycopg2/extensions.py(145)make_dsn()
-> parse_dsn(dsn)
(Pdb) dsn
'host=db.REDACTED.supabase.co dbname=postgres user=postgres password=a password with spaces 12345'

As you can see from the last line, the password is not properly escaped.

If I had to guess this could be reproduced even without actually even configuring a PG DB with a password with spaces.

I have confirmed that everything works if the spaces are removed from the password.

jmafc commented 2 years ago

I had never heard that spaces were allowed in passwords!

Technically, however, this is a bug in pgdbconn rather than Pyrseas. It doesn't escape any of the parameters. Which brings me to another issue of moving forward with Pyrseas. Way back when, I thought that having pgdbconn as a separate repo was a good idea, because eventually (if a concurrent user app was to be supported), we'd need more than simple connection management. Currently, I think if Pyrseas is to move forward, on Python, pgdbconn ought to be folded back in.