Friday, November 10, 2023

Using psql without entering password

Sometimes, we need to run psql command without entering the password, even though the account is protected with password. The usual situations are, when we are running the psql command in a script, or we have to constantly monitor the output of psql commands using watch. Here is the method on how to achieve that.

Create a .pgpass file inside the user's home directory who's going to access psql without password

$ touch ~/.pgpass

Follow below format to add the user's details into pgpass

hostname:port:database:username:password

$ echo "10.10.10.10:5432:mydatabase:myuser:mysuperlongpassword" > ~/.pgpass

We can also use wildcard, such as *. If you password contains ":" or "\", use "\" to escape them

$ echo "*:*:mydatabase:myuser:mysuperlongpassword" > ~/.pgpass

Give a proper permission to the file, nobody accept the owner of the home directory is allowed to use the file
$ chmod 0600 ~/.pgpass
Now we should be able to use psql to mydatabase as myuser, without entering any password
$ psql -h localhost -U myuser mydatabase
postgres=# 

No comments:

Post a Comment