Basic server configuration

In order to access the server, we need to understand the PostgreSQL authentication mechanism. On Linux systems, one can connect to PostgreSQL using a unix-socket or TCP/IP protocol. Also, PostgreSQL supports many types of authentication methods.

When a PostgreSQL server is installed, a new operating system user, as well as a database user, with the name postgres is created. This user can connect to the database server using peer authentication. The peer authentication gets the client's operating system username and uses it to access the databases that can be accessed. Peer authentication is supported only by local connections—connections that use Unix sockets. Peer authentication is supported by Linux distribution but not by Windows.

Client authentication is controlled by a configuration file named pg_hba.conf, where pg stands for PostgreSQL and hba stands for host-based authentication. To take a look at peer authentication, one should execute the following command:

grep -v '^#' /etc/PostgreSQL/10/main/pg_hba.conf|grep 'peer'
local all postgres peer
local replication all peer

The interpretation of the first line of the result is shown here: The postgres user can connect to all the databases using Unix-socket and the peer authentication method.

To connect to the database servers using the postgres user, first we need to switch the operating system's current user to postgres and then invoke psql. This is done via the Linux command as follows:

$sudo -u postgres psql
psql (10.0)
Type "help" for help.

postgres=# SELECT version();
                                                    version 
----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

The preceding query shows the psql interactive terminal. The select statement SELECT version (); was executed, and the PostgreSQL version information was displayed. As shown in the preceding result, the installed version is PostgreSQL 10.0.

Prior to PostgreSQL 10, the PostgreSQL version number has three digits. Major releases occur roughly on an annual basis and usually change the internal format of the data. This means that the stored data's backward compatibility between major releases is not maintained. A major release is numbered by incrementing either the first or the second digit such as 9.5 and 9.6. Minor releases are numbered by increasing the third digit of the release number, for example 9.6.1 to 9.6.2. Minor releases are only bug fixes.

In PostgreSQL 10, the versioning policy has changed, major releases are numbered by incrementing the first number that is from 10 to 11. Minor releases are numbered by incrementing the second part of the number, for example, 10.0 to 10.1.