Multiple Password attempts pg_dump python

Question

I am trying to allow the user of my script to prompt users to enter the correct password if they have entered an invalid one. I am not sure whether this a psql configuration or if its done by using error handling with python. Which one do I use and how? My current code is as follows:

 backup_ps = subprocess.Popen(
    ['pg_dump','-h', ORIGIN_DB, '-U', ORIGIN_DB_USER, ORIGIN_DB_NAME, '-f', destination, '-c',
     '-t', 'table1',
     '-Fp', '--inserts'],
    stdout=subprocess.PIPE
)

output = backup_ps.communicate()[0]

# if password incorrect, prompt for correct password.

Answer 1

I am running postgresql96 on FreeBSD. When I enter a bad password the return code for pg_dump is 1, rather than 0.

A naive solution would be to put your code in a while loop like this:

while True:
    backup_ps = subprocess.Popen(
        ['pg_dump','-h', ORIGIN_DB, '-U', ORIGIN_DB_USER, ORIGIN_DB_NAME, '-f', destination, '-c',
            '-t', 'table1',
            '-Fp', '--inserts'],
        stdout=subprocess.PIPE
    )

    output = backup_ps.communicate()[0]
    if backup_ps.returncode == 0:
        break

The problem is that pg_dump will likely return 1 on any error, including when system calls to allocate memory or disk space fail. So it doesn't actually mean that the password input failed, and you might want a hook to exit without a successful return after n tries or using something else.

Another solution might be to check if the dump output file exists and was recently modified. You might also want to inspect stderr for the error message that talks about a bad password.

I think those solutions are hacky.

The best solution (AFAICT) is to not use a separate process to do the dump (because then communicating with that process can be a pain in the ass), but use a Python library, like psycopg2 to access the database.