Basic server configuration

In order to access the server, we need to understand the PostgreSQL authentication mechanism. On Linux systems, you 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, let's display the content of pg_hba.conf:

$sudo su 
grep -v '^#' /etc/postgresql/11/main/pg_hba.conf|grep 'peer'
local all postgres peer
local all all peer
local replication all peer

The interpretation of the first line of the grep result is shown in the preceding command. 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 following Linux command:

$sudo -u postgres psql
psql (11.1 (Ubuntu 11.1-1.pgdg18.04+1))
Type "help" for help.

postgres=# SELECT version();
                                                             version 
----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 (Ubuntu 11.1-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit
(1 row)

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

Prior to PostgreSQL 10, the PostgreSQL version number had 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 isn't 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.