Navigating Postgresql – Command Line Login / Using .pgpass

While postgresql is a great database with lots of features that you would normally only get in an enterprise database, navigating through it on the command line the first few lines can be a bit of an experience.  So, I thought that if I write down all of the tricks I learn, they may help somebody else someday.  As connecting to postgresql with psql is a requirement for nearly anything you will need to do, we will discuss using Postgresql Command Line Login / Using .pgpass.

Psql Command Line Examples

You can login to the database directly from a linux shell using psql.  Typically, one would list the parameters used to connect directly on the command line.

# psql -h -p 5432 -U mary -W -d mydatabase
Password for user mary:
psql (9.3.1)
Type “help” for help.mydatabase=> \q

The example above used the single character options.  You can also use the longer more descriptive command line options:

# psql – –port=5432 –username=mary –password –dbname=mydatabase
Password for user mary:
psql (9.3.1)
Type “help” for help.acadoo=> \q

You can get more psql options from the linux shell just by typing: psql –help

# psql –help
psql is the PostgreSQL interactive terminal.Usage:
psql [OPTION]… [DBNAME [USERNAME]]General options:
-c, –command=COMMAND run only single command (SQL or internal) and exit
-d, –dbname=DBNAME database name to connect to (default: “root”)
-f, –file=FILENAME execute commands from file, then exit
-l, –list list available databases, then exit
-v, –set=, –variable=NAME=VALUE
set psql variable NAME to VALUE
-V, –version output version information, then exit
-X, –no-psqlrc do not read startup file (~/.psqlrc)
-1 (“one”), –single-transaction
execute as a single transaction (if non-interactive)
-?, –help show this help, then exitInput and output options:
-a, –echo-all echo all input from script
-e, –echo-queries echo commands sent to server
-E, –echo-hidden display queries that internal commands generate
-L, –log-file=FILENAME send session log to file
-n, –no-readline disable enhanced command line editing (readline)
-o, –output=FILENAME send query results to file (or |pipe)
-q, –quiet run quietly (no messages, only query output)
-s, –single-step single-step mode (confirm each query)
-S, –single-line single-line mode (end of line terminates SQL command)Output format options:
-A, –no-align unaligned table output mode
-F, –field-separator=STRING
set field separator (default: “|”)
-H, –html HTML table output mode
-P, –pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, –record-separator=STRING
set record separator (default: newline)
-t, –tuples-only print rows only
-T, –table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, –expanded turn on expanded table output
-z, –field-separator-zero
set field separator to zero byte
-0, –record-separator-zero
set record separator to zero byte

Connection options:
-h, –host=HOSTNAME database server host or socket directory (default: “local socket”)
-p, –port=PORT database server port (default: “5432”)
-U, –username=USERNAME database user name (default: “root”)
-w, –no-password never prompt for password
-W, –password force password prompt (should happen automatically)

For more information, type “\?” (for internal commands) or “\help” (for SQL
commands) from within psql, or consult the psql section in the PostgreSQL

Report bugs to <>.


Postgresql Psql Using .pgpass

Now that you can connect, you may not want to have to type all of those parameters if you plan to do it frequently or if you plan to create some scripts using psql.  One method for getting around having to use all of these parameters is to create a .pgpass file to store the parameters.  The password file is typically located at $HOME/.pgpass or ~/.pgpass whichever way you prefer to say it.

.pgpass file format

A typical .pgpass file will have the following fields:


Below is an example .pgsql file:

Now that we have our .pgpass file setup for the mary user, it becomes much easier to login because we no longer need to state all of the parameters.

[ ~]$ psql
WARNING: password file “/home/mary/.pgpass” has group or world access; permissions should be u=rw (0600) or less
psql (9.2.3)
Type “help” for help.mary=>

Oops!  We forgot to set the proper permissions on the file.  This is very important.  If we don’t set the permissions so only the owner has permissions then anybody can read the file and get access to the database.  So, we set the .pgpass permissions:

[ ~]$ chmod 0600 .pgpass
[ ~]$ ls -l .pgpass
-rw——- 1 mary mary 45 Nov 26 21:44 .pgpass

And that clears up the error message:

[ ~]$ psql
psql (9.2.3)
Type “help” for help.mary=>





The following two tabs change content below.
Jeff has 20 years of professional IT experience, having done nearly everything in his roles of IT consultant, Systems Integrator, Systems Engineer, CNOC Engineer, Systems Administrator, Network Systems Administrator, and IT Director. If there is one thing he knows for sure, it is that there is always a simple answer to every IT problem and that downtime begins with complexity. Seasoned IT professional by day, Jeff hopes to help other IT professionals by blogging about his experiences at night on his blog: You can find Jeff on or LinkedIn at: LinkedIn or Twitter at: Twitter

Latest posts by Jeff Staten (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *