PGAdmin, Dbeaver, etc. are useful GUIs for managing and reviewing Postgres databases. Sometimes, however, there’s a desire to login via the CLI.

Every time I need to do this, however, I need to look it up.

This time, I’m writing it down, so at the very least - it’s easier to find in the future.

The general command will look like:

psql -h <db host name> -U <your username> -d <db_name> -W

For example, imagine the following confiiguration for your database:

{
  "host": "localhost",
  "port": 5432,
  "user": "admin",
  "database": "awesome-db",
  "password": "something-super-secure"
}

To log in from the command line then, it would be:

psql -h localhost -p 5432 -U admin -d awesome-db -W

The -W means that you will be prompted for the password as soon as the command runs (at which point, in this example, you would enter “something-super-secure”).

Note, I’m using psql here - which is a “terminal-based front-end to Postgres.”¹

Footnotes

• ¹ For more about psql, see the manual page in your terminal with man psql. An abbreviated version is pasted below:

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: "stephen")
  -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
                           (e.g., -v ON_ERROR_STOP=1)
  -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[=options]     show this help, *then* exit
      --help=commands      list backslash commands, *then* exit
      --help=variables     list special variables, *then* exit

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -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
                           field separator *for* unaligned output (default: "|")
  -H, --html               HTML table output mode
  -P, --pset=VAR[=ARG]     set printing option VAR to ARG (see *\p*set command)
  -R, --record-separator=STRING
                           record separator *for* unaligned output (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 *for* unaligned output to zero byte
  -0, --record-separator-zero
                           set record separator *for* unaligned output 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: "stephen")
  -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
documentation.

Report bugs to <pgsql-bugs@postgresql.org>.