PostgreSQL – How to Use psql Without Password Prompt?

postgresqlpsql

I wrote a script to REINDEX indexes in a database. Here is one of them:

echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX scm_main.unq_vbvdata_vehicle;"
if [[ ${?} -eq 0 ]]; then
    echo "reindex for unq_vbvdata_vehicle finished at: `date "+%F %T"`" >> ${LOG_FILE}
else
    echo "reindex for unq_vbvdata_vehicle failed" >> ${LOG_FILE}
    exit 1
fi

The problem is I can not run this script in standalone mode. psql is prompting password every time it runs. There is also two limitations:

  1. I can not create a user on database with no password.

  2. Because REINDEX locks tables, I should use sleep <num> between each REINDEX.

Is there any automatic solution?

Best Answer

You have four choices regarding the password prompt:

  1. set the PGPASSWORD environment variable. For details see the manual:
    http://www.postgresql.org/docs/current/static/libpq-envars.html
  2. use a .pgpass file to store the password. For details see the manual:
    http://www.postgresql.org/docs/current/static/libpq-pgpass.html
  3. use "trust authentication" for that specific user:
    http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
  4. use a connection URI that contains everything:
    http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532
Related Question