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=# 

Using psql from command line to get data from postgresql

Sometimes we need to get some data from postgresql database, and we want the output to appear on the terminal so that we can further process the output.

Lets say, we want to get a list of actor from a database called dvdrental, we can simply use below command:

$ psql -U postgres -d dvdrental -c "select * from actor;"

The output will be in an interactive mode if the output is very long.

In order to run psql with output that is not interactive, we can use here-document method. The method comprise of a "<<" symbol followed by some text used as ending text for the here-document. For example, in order to get the same result as above using here-document method, we can use below command:

$ psql -U postgres -d dvdrental <<END
select * from actor;

END

The END keyword is a signal to end the here-document, thus executing the command. The output will be like below, which is not interactive, but easy to copy and paste: