How to use psql with no psql password prompt?

725 Asked by BuffyHeaton in SQL Server , Asked on Mar 13, 2023

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

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:

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

Because REINDEX locks tables, I should use sleep  between each REINDEX.

Is there any automatic solution?

Answered by Claudine Tippins

You have four choices regarding the psql password prompt:

set the PGPASSWORD environment variable. For details see the manual:

http://www.postgresql.org/docs/current/static/libpq-envars.html

use a .pgpass file to store the password. For details see the manual:

http://www.postgresql.org/docs/current/static/libpq-pgpass.html

use "trust authentication" for that specific user:

http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST

use a connection URI that contains everything:

http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532

How to use psql with no psql password prompt?

Your Answer