67

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?

107

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
  • 4
    Since PostgreSQL 9.1 there is also the peer authentication method for local connections. Currently only for Linux, BSD, OS X or Solaris (not Windows). – Erwin Brandstetter Mar 11 '12 at 2:00
  • 1
    A note on the .pgpass option, you still have to specify username, database, and hostname (if you would've normally) in the psql command – raphael Feb 10 '17 at 18:41
  • with respect to 3, you need to edit the method. its explained here: gist.github.com/p1nox/4953113 – FuzzyAmi Apr 17 at 9:33
  • Easily use export PGPASSWORD="your_pw" to go version 1 – gies0r Aug 13 at 20:34
17

A simple example with PGPASSWORD will be something like:

PGPASSWORD=YOUR_PASSRORD psql -h YOUR_PG_HOST -U YOUR_USER_NAME

Hope it helps.

  • 4
    Hi Ian_H, thank for your comments and I all agree with your points. The accepted answer is very comprehensive and that did solve my problem. The reason I put my answer to this old post is because it is still not straightforward to me, as I believe others may have the same feeling. – pdm Oct 19 '17 at 20:23
  • This was definitely a good addition to the accepted answer - helpful reminder that we don't need to permanently set ENV vars. – kevlarr May 3 at 14:39
1

Depending your account permissions, the example without specifying the database may fail, because user permissions are checked against the database you connect to. It is better explicitly specify the database too.

# this can fail.
PGPASSWORD=YOUR_PASSRORD psql -h YOUR_PG_HOST -U YOUR_USER_NAME    

# this is more likely to work, assuming given account has permissions to that database.
PGPASSWORD=YOUR_PASSRORD psql -h YOUR_PG_HOST -U YOUR_USER_NAME -d YOUR_DATABASE  

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.