There are two ways to non interactively provide password for the
psql command which is a PostgreSQL interactive terminal. Each method allows to easily write shell scripts using terminal-based PostgreSQL utilities as you can provide user credentials from the password file or environment variables.
Provide password using the password file
To use this method create
.pgpass file inside your home directory and restrict its permissions so it would not be ignored by utilities.
$ touch ~/.pgpass $ chmod 0600 ~/.pgpass
Each line defines user credentials using the following structure.
Please note that every field other than password can be replaced with an asterisk to match anything, everything else is self explanatory so I will jump directly to the example.
localhost:5432:bookmarks:milosz:JOAvaDtW8SRZ2w7S 10.0.0.15:5432:wikidb:mediawiki:631j7ZtLvSF4fyIR 10.0.0.113:*:*:development:iGsxFMziuwLdEEqw
Now, as user with defined PostgreSQL password file, you can use PostgreSQL utilities without password prompt to perform desired tasks.
$ psql -w -U milosz bookmarks -c "select * from domains"
$ pg_dump -w -c -U development -h 10.0.0.113 bookmarks | \ gzip --best > bookmarks.sql.gz
You are not forced to use only
~/.pgpass file as you can define
PGPASSFILE variable to use entirely different password file.
$ PGPASSFILE=~/.pg/.altpgpass pg_dump -c -w -U milosz bookmarks
$ export PGPASSFILE=~/.altpgpass $ psql -w -U mediawiki -h 10.0.0.15 wikidb -c "select * from user"
As you probably noticed I am using
-w parameter so the above-mentioned utilities will fail if password is not available instead of asking for one.
Provide password using environment variables
Instead of using password file, you can define
PGPASSWORD environment variables.
$ PGHOST=10.0.0.15 PGPORT=5432 \ PGDATABASE=wikidb \ PGUSER=mediawiki PGPASSWORD=631j7ZtLvSF4fyIR \ pg_dump -w -c | gzip --best > wikidb.sql.gz
$ PGHOST=10.0.0.15 \ PGUSER=development PGPASSWORD=iGsxFMziuwLdEEqw \ psql bookmarks -c "select * from domains"
$ PGUSER=milosz PGPASSWORD=JOAvaDtW8SRZ2w7S \ psql bookmarks -c "select * from favorites"