How to connect to PostgreSQL with psql

Once we installed PostgreSQL, we can use psql command to connect to our databases through the terminal and make queries. But before using it, we maybe need to know a few concepts about it to make our connections more straightforward.

In case you’re using PostgreSQL version 11, it manages the client authentications with the configuration file /etc/postgresql/11/main/pg_hba.conf. In there, you can see something like this.

# TYPE  DATABASE      USER      ADDRESS       METHOD
local   all           postgres                peer
local   all           all                     peer
host    all           all       127.0.0.1/32  md5
host    all           all       ::1/128       md5
local   replication   all                     peer
host    replication   all       127.0.0.1/32  md5
host    replication   all       ::1/128       md5

PostgreSQL controls which hosts are allowed to connect, which database user names clients can use, which databases they can access and how they are authenticated.

Connection Type

A socket is an endpoint from a communication between two programs running on a network or machine. PostgreSQL can manage connections through Unix-domain sockets and TCP/IP sockets.

• local: A Unix-domain socket
• host: A plain or encrypted TCP/IP socket
• hostnossl: A plain TCP/IP socket
• hostssl: An encrypted TCP/IP socket

A Unix-domain socket is a socket from a local communication between two programs on the same host or machine.

A TCP/IP socket is a socket from a communication between two programs over a network. It can be used by two programs running on the same machine too, but it’s slower.

Database

• all: All databases, except for replication
• replication: A replication connection
• [dbname]: Only connects to a database named [dbname]

User

• all: All users
• [user-name]: A specific user named [user-name]
• +[group-name]: Every user from a group named [group-name]

Address

It specifies the socket address. It can be a host name, or be made up of an IP (v4 or v6) address with an CIDR mask number. A CIDR mask is an integer (0-32 for v4 and 0-128 for v6) that represents the number of bits in the mask.

Authentication Method

• peer: Only connects using the operating system’s user name as the database user name. Only for local connections.
• md5: Only connects with a password. It’s encrypted.
• password: Only connects with a password.

Using psql

When we installed PostgreSQL, it created an Operating System user named postgres and a database user also named postgres with a connection method set as peer. This means we can only connect to the PostgreSQL user postgres from our OS user postgres. And for that, we can run the following command.

$ sudo -u postgres psql -U postgres -d [dbname]

By default, psql tries to connect to the database user with the same name as our OS active user. Then we can obviate the database user name from psql.

$ sudo -u postgres psql -d [dbname]

Now using sudo every time is a bit annoying. But we have a few ways to avoid that.

One is setting user postgres authentication method as md5 in the file /etc/postgresql/11/main/pg_hba.conf to make PostgreSQL let us connect from any OS user. And at the end we’d only had to run the next to connect.

$ psql -U postgres -d [dbname]

Another one is creating a database user with the same name as our OS active user. By default, PostgreSQL will create it with the method peer, because the authentication’s configuration file won’t change and set all users with a peer method on local connections and to connect we’d just had to run the next.

$ psql -d [dbname]

For me, this is the shortest and most straightforward method to connect to a database with psql. So we’re going to create a database user named the same as our OS active user. In the terminal, we run the next.

$ sudo -u postgres psql

We should see the following.

psql (11.7 (Ubuntu 11.7-2.pgdg18.04+1))
Type "help" for help.

postgres=#

By default, psql tries to connect to a database with the same name as our OS active user. This means we are connected to the database named postgres. Now, we just create our new database user with the same name as our OS user.

CREATE USER [your-os-user] SUPERUSER;

After that, we can exit typing in \q or pressing Ctrl+D. With this, we created a database user named as our OS user and we’re able to authenticate to PostgreSQL with psql just running the next.

$ psql -d [dbname]

And that’s all. Thanks for reading.