Python: send postgres password with using subprocess

Question

I'm stuck at a dead end with sending password with using subprocess, and I don't know what is wrong. I would be grateful for any tips.

My code :

os.system("sudo -u postgres -h " + db_host + " psql postgres -c \"\password\" ")
os.system("sudo -u postgres -h " + db_host + " createuser " + db_user);
print bcolors.WARNING, "Please enter your password for : " + db_user, bcolors.ENDC
str = "sudo -u postgres -h " + db_host + " psql postgres -c \"\password " + db_user + "\""
# os.system(str)
p = subprocess.Popen(['sudo','-u', 'postgres', '-h', db_host, 'psql', 'postgres', "-c", "\password", db_user],stdin=subprocess.PIPE, shell=False)
p.communicate(raw_input("Please give me a password!")

When using os.system(str) everything is okay, but I want to catch the typed password from the user and in the future I want to store the password in configuration file. Is that possible?

Answer 1

Stop what you're doing, and use psycopg2. Please. You almost never need to shell out to psql. Just connect with the psycopg2 native client driver for PostgreSQL.

If you need to connect as a superuser, modify pg_hba.conf to allow md5 password authentication from your user and set a password for the postgres user.

---

os.system("sudo -u postgres -h " + db_host + " psql postgres -c \"\password\" ")

This doesn't make sense. You're trying to execute the command -h. You need to invoke psql first.

---

sudo tries to avoid reading the password from stdin. It closes its stdin and reopens the current tty directly as a security measure to help stop people wrapping sudo with a command that traps the password.

So it's designed to defeat what you are trying to do.

You can:

• Set NOPASSWD mode in sudo for psql;
• Use sudo -A with an askpass helper program in the SUDO_ASKPASS environment variable. You could write a SUDO_ASKPASS that reads the password from a file descriptor inherited from your program by creating a pipe() to yourself.
• Use sudo -n to disable the password prompt. sudo will fail if a password is required.
• ... or best of all, don't use sudo to run psql, just use psycopg2