Connect to PostgreSQL command line Linux

Operating System : Ubuntu 16.04 (Xenial Xerus)

Database Server : PostgreSQL 9.3

We can connect to PostgreSQL simply using a command line interface in Linux operating system distribution. Below are steps to do to be able to connect to PostgreSQL console through a command line interface which is represented by a bash prompt. Below is the default way to connect through fresh-installed PostgreSQL which doesn’t have any special configuration regarding the authentication connection :

1. Make sure PostgreSQL is installed in the host, server or workstation. In this article term, it is a local host, server or workstation where it don’t need any remote connection. Below is one of the way to be used in order to check whether PostgreSQL was already installed or not.

2. Make sure the service handling request of PostgreSQL in the host, server or workstation is currently running or active. Below is the command used to check the state of PostgreSQL service :

3. Execute the following command to enter PostgreSQL console as soon as it has already know that PostgreSQL has been installed and its currently service is active :

[user@hostname ~]# psql -Upostgres

1. On succeeding the above command, it will then be directed PostgreSQL command console as follows :

psql (9.4.5)
Type "help" for help.
postgres=#

The context of this article is about the pg_hba.conf file which is allowing any connection within local request without any authentication at all. So, when the above command in step 4 is executing, PostgreSQL server doesn’t ask for any password at all.

Depends on the operating system and also the version of PostgreSQL database server, the location of the pg_hba.conf file is different. In the context of this article, using Ubuntu 16.04 (Xenial Xerus), it can be found in /etc/postgresql/9.3/main. Below is the content of the file which is actually set up for a no password entering PostgreSQL command console using user ‘postgres’ :

local   all                      all        trust

first field : local > the first field is local and it is definitely just a connection type method which is in a Unix-domain socket.

second field : all > the second field is all which is a field to define database name and it is set as all since it will cover all database.

third field : all > the third field is all which is a field to define username and it is set as all since it can use all user to connect.

fourth field : trust > it is an authentication method and in this case, trust means it doesn’t need any password to connect to database since it is using the reserved keyword ‘trust’.

The above configuration of pg_hba.conf can be read as follows :

Anyone can connect to any database without ever any need to insert password for authentication in local machine or socket. We see in the above sample, it doesn’t need to provide password to use to connect to database using username ‘postgres’.