How to use psql with no password prompt?

Question

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:

I can not create a user on database with no password.
Because REINDEX locks tables, I should use sleep <num> between each REINDEX.

Is there any automatic solution?

score 195 · Accepted Answer · 2016-05-13T09:31:30.967

195

You have four choices regarding the 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

edited May 13 '16 at 09:31

answered Mar 10 '12 at 08:52

7

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 02:00
2

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: https://gist.github.com/p1nox/4953113 – FuzzyAmi Apr 17 '19 at 09:33
2

Easily use export PGPASSWORD="your_pw" to go version 1 – gies0r Aug 13 '19 at 20:34
1

@gies0r I would never export a password because of security considerations – Stefan Haberl Jan 30 '20 at 07:43

score 49 · Answer 2 · answered Oct 19 '17 at 00:27

49

A simple example with PGPASSWORD will be something like:

PGPASSWORD=YOUR_PASSRORD psql -h YOUR_PG_HOST -U YOUR_USER_NAME

Hope it helps.

answered Oct 19 '17 at 00:27

Eric

591
4
5

5

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. – Eric Oct 19 '17 at 20:23
3

This was definitely a good addition to the accepted answer - helpful reminder that we don't need to permanently set ENV vars. – kevlarr May 03 '19 at 14:39
5

Note, that this would still expose your password to the command shell history. – Stefan Haberl Jan 30 '20 at 07:44
1

Don't forget to restart your command prompt on Windows if you're adding this permanently to Path. – Cloud Sep 30 '22 at 07:03
@StefanHaberl raises a good point. Using read can help (e.g., read -s -p "Enter password: " PGPASSWORD; export PGPASSWORD; see more at this answer). – toraritte Mar 28 '23 at 13:05

score 5 · Answer 3 · edited Mar 21 '19 at 16:54

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

score 5 · Answer 4 · answered Jul 29 '20 at 02:48

5

In Windows you can set it system wide:

SETx PGPASSWORD YourPassword /M

Or for security if you don't want it system wide you can login in one line:

set PGPASSWORD=YourPassword&& psql -d database -U user

answered Jul 29 '20 at 02:48

Jeremy Thompson

201
3
10

score 2 · Answer 5 · answered Jun 12 '23 at 18:56

2

Way-1:

PGPASSWORD="<password>" psql -U <db_user> -d <db_name> -h <host_name/domain> -p <port>

Way-2:

psql postgres://<db_user>:<user_password>@<hostname/domain>:<db_port>/<db_name>

answered Jun 12 '23 at 18:56

Sadia

21
1

score 0 · Answer 6 · answered Oct 14 '19 at 11:36

0

Very helpful answers in this thread. I'm just adding this for ubuntu 18.04:

sudo PGPASSWORD=yourpasswordHere -u postgres psql

This will take you into the postgres without the password prompt, without having to set any environment variables. This is not a permanent setting.

answered Oct 14 '19 at 11:36

Dhiraj

101
1

2

Don't do this: The password will be visible in the output of ps -ef. – Colin 't Hart Oct 14 '19 at 13:09

score -2 · Answer 7 · edited Aug 18 '21 at 15:53

-2

 var processStartInfo = new ProcessStartInfo();
 processStartInfo.UseShellExecute = false;
 processStartInfo.EnvironmentVariables["PGPASSWORD"] = "password";
 Process proc = Process.Start(processStartInfo);

It will work.

edited Aug 18 '21 at 15:53

Hannah Vernon

70,041
22
171
315

answered Aug 18 '21 at 13:27

user237200

9

I pointer or two about how to actually make this work would be helpful. For instance, you'd need Visual Studio 2019, and would need to create a solution, with a C# console project, etc, etc. – Hannah Vernon Aug 18 '21 at 15:55
Ok, maybe missing what was written by Hannah but still very helpful for me :-) – user2126375 Sep 21 '21 at 13:41

How to use psql with no password prompt?

7 Answers7

Linked

Related