Connecting to psql shell

psql_connect.md

Connecting as the admin:

$ sudo -u postgres psql

Creating a new user:

postgres=# CREATE ROLE foouser WITH LOGIN PASSWORD '************';

Connecting as a normal user:

$ psql -U foouser -W -d foodatabase -h localhost
Password for user foouser: ************

• -U is the user
• -W means typing password interactively
• -d is database
• -h is host

Note that without specifying the host and with default configuration the authentication fails:

$ psql -U foouser -W -d foodatabase
Password for user foouser: ************
psql: FATAL:  Peer authentication failed for user "foouser"

The reason in described on StackOverflow. Basically without the host a local socket connection is made with "Peer authentication" (ie. the linux username is used instead). By specifying the host there's a TCP/IP conenction and "Password authentication" is used instead.

When connecting from a normal application typically the TCP/IP connection is used so we don't even run into this problem.

Non-interactive usage

The password can be stored in the ~/.pgpass file (docs) where host:port is typically localhost:5432:

hostname:port:database:username:password

It needs to have stricter permissions:

$ chmod 0600 ~/.pgpass

Then you can connect without explicit password (no -W):

$ psql -U foouser -d foodatabase