CODEWITHSUNDEEP
pythonpostgresqldockersubprocesspg-dump
lbened
lbened

Reputation: 85

How to give password to pg_dump without prompt?

I have the following Python code that works locally to generate a dump. I would like to dockerise it without having the prompt password for the database connection when I use pg_dump command and also I don't know what will I give in the dockerfile instead of postgres_bin = r"C:\Program Files\PostgreSQL\13\bin"

import os
import paramiko
import subprocess

postgres_bin = r"C:\Program Files\PostgreSQL\13\bin"
dump_file = "database_dump.sql"

with open(dump_file, "w") as f:
    result = subprocess.call([os.path.join(postgres_bin, "pg_dump"), "-Fp", "-d", "XXX", "-U", "XXX", "-h", "XXX", "-p", "XXX"], stdout=f)

transport.close()

This code works locally. I would like to have the same code working without having to give a password in a prompt that I have locally.

How can I do that ?

---- UPDATE ----

I updated my code below. This code works with a prompt. But When I uncomment the ligne with "-w" and env={'PGPASSWORD': 'secret'} I have the error that hostname can't be translate : Unknown server error.

import os
import paramiko
import subprocess

print("Import has been done !")

postgres_bin = r"C:\Program Files\PostgreSQL\13\bin"
dump_file = "database_dump.sql"

with open(dump_file, "w") as f:
        result = subprocess.call([
                os.path.join(postgres_bin, "pg_dump"), 
                "-Fp", 
                "-d", 
                "anonymedev", 
                "-U", 
                "pgsqladmin", 
                "-h", 
                "hostname", 
                "-p", 
                "32045", 
                # '-w'
                ], 
                # env={'PGPASSWORD': 'secret'},
                stdout=f
                )

Upvotes: 1

Views: 1466

Answers (1)

larsks
larsks

Reputation: 311436

You can use the PGPASSWORD or PGPASSFILE environment variables as described in the documentation.

For example, to dump the database "example" as user "postgres" with password "secret", I can write:

PGHOST=127.0.0.1 \
PGUSER=postgres \
PGPASSWORD=secret \
PGDATABASE=example \
pg_dump

I can store the password in a file instead of using the PGPASSWORD environment variable; in that case, we need to format the file as described in "The Password File":

echo 127.0.0.1:5432:example:postgres:secret

And then reference that file with the PGPASSFILE environment variable:

PGPASSFILE=pgpass \
pg_dump -h 127.0.0.1 -U postgres example

If you are running pg_dump from Python, then in order to set an environment variable you need to set keys in os.environ, or use the env parameter to subprocess.call.

Using the env parameter sets the environment for that single subprocess.call invocation, and you must explicitly include existing environment variables if you want them visible in the child process:

with open(dump_file, "w") as f:
    result = subprocess.call(
        [
            os.path.join(postgres_bin, "pg_dump"),
            "-Fp",
            "-d",
            "XXX",
            "-U",
            "XXX",
            "-h",
            "XXX",
            "-p",
            "XXX",
        ],
        env=os.environ | {'PGPASSWORD': 'secret'},
        stdout=f,
    )

(Note that the dict union syntax here requires Python 3.9 or later).

Alternately, you can update os.environ, which will make the variable available to all subsequent subprocesses:

os.environ['PGPASSWORD'] = 'secret'

with open(dump_file, "w") as f:
    result = subprocess.call(
        [
            os.path.join(postgres_bin, "pg_dump"),
            "-Fp",
            "-d",
            "XXX",
            "-U",
            "XXX",
            "-h",
            "XXX",
            "-p",
            "XXX",
        ],
        stdout=f,
    )

Upvotes: 2

Related Questions