There are two ways to non interactively provide a password for the psql command (PostgreSQL interactive terminal). Each method allows you to quickly 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.

server:port:database:username:password

Please note that every field other than a 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

As a user with a defined PostgreSQL password file, you can use PostgreSQL utilities without a 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 the ~/.pgpass file as you can define PGPASSFILE variable to use an 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 utilities mentioned above will fail if the password is not available.

Provide password using environment variables

Instead of using a password file, you can define PGHOST, PGPORT, PGDATABASE, PGUSER and 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"

References

ko-fi