Note – if you are logged in as root superuser before the sudo command (temporary superuser) is naturally not needed

For Debian

Based systems, run the following commands

user@localhost: ~ sudo apt update

Optional:

user@localhost: ~ sudo apt upgrade

The command to install the latest version for your distribution will be as follows:

user@localhost: ~ sudo apt install postgresql

Check if it is running:

user@localhost: ~ systemctl status postgres*

See similar to the following:

stgresql.service - PostgreSQL RDBMS

Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)

Active: active (exited) since Tue 2019-07-23 15:29:57 EEST; 1min 28s ago

Main PID: 25711 (code=exited, status=0/SUCCESS)

Tasks: 0 (limit: 4915)

Memory: 0B

CGroup: /system.slice/postgresql.service

postgresql@11-main.service - PostgreSQL Cluster 11-main

Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime; vendor preset: enabled)

• Active: active (running) since Tue 2019-07-23 15:30:01 EEST; 1min 24s ago
• Main PID: 25928 (postgres)
• Tasks: 7 (limit: 4915)
• Memory: 17.2 M
• CGroup: /system.slice/system-postgresql.slice/postgresql@11-main.service
• 25928 /usr/lib/postgresql/11/bin/postgres -D /var/lib/postgresql/11/main -c config_file=/etc/postgresql/11/main/postgresql.conf
• 25930 postgres: 11/main: checkpointer
• 25931 postgres: 11/main: background writer
• 25932 postgres: 11/main: walwriter
• 25933 postgres: 11/main: autovacuum launcher
• 25934 postgres: 11/main: stats collector
• 25935 postgres: 11/main: logical replication launcher
• user@localhost: ~ sudo ss -ntlp | grep postgres

user@localhost: ~ sudo ss -ntlp | grep postgres

LISTEN 0 128 127.0.0.1:5432 0.0.0.0:* users: ((“postgres”,pid=27885,fd=5))

LISTEN 0 128 [::1]:5432 [::]:* users:((“postgres”,pid=27885,

install the client part for connection and configuration

user@localhost: ~ sudo apt install postgresql-client

Connect to make sure that the server is allowed to itself:

user@localhost: ~ sudo -u postgres psql

In this case, even if you root to enter “sudo -u postgres” before ‘psql’ need otherwise the server will not let us

The psql console:

psql (11.4 (Debian 11.4-1))

Type "help" for help.

postgres=#

Enter ‘\q’ to close the session.

Authentication settings

The Creation Of User/Role

user@localhost: ~ sudo -u postgres createuser --interactive -P

The program createuser will ask you a few questions to establish on the basis of your answers account

Remote access

After installation on debian/Ubuntu the PostgreSQL server allows you to connect only to local clients through sockets or UNUX on the IP 121.0.0.1/[::1]. For rare remote access, you can use ssh tunnels (Most modern graphics clients have this option).

If you need a permanent connection, open the file /etc/postgresql/11/main/pg_hba.conf – Host Based Authentication .To start even little will understand the meaning of the entries in this file. The entries in this file control the behavior of the server when it decides to allow you to the database or not

For example the entry:

“local all postgres peer”

Mean the following:

“A UNIX user (peer) named ‘postgres’ connected from the local node(local UNIX socket only) to be started up to any (all) database The Postgres user is an analog of root on UNIX. The presence of such a record means that logging in as a Postgres user can connect without a password to any database and do anything with them without restrictions.

For example, adding a record for another user:

“local all JohnSith peer” will allow the user JohnSith also connect from their local session without a password to all databases and do with them anything without restrictions. Therefore, write access to the file “pg_hba.conf” should be strictly limited

If we want to organize access to the SQL server via TCP/IP only from the subnet 192.168.1.0/24 only to the user Programmer and only to the database MY_BIG_SITE and password, you need to do 3 things:

* from under the user postgres run the program createuser.

user@localhost: ~ sudo -u postgres createuser --interactive -P

The program will ask a couple of questions and create an account based on your answers

* In /etc/postgresql/11/main/postgresql.conf to add the line:

listen_addresses = *.

* In /etc/postgresql/11/main/pg_hba.conf to add the line:

"192.168.1.0/24 Programmer MY_BIG_SITE scram-sha-256“

After changing the configuration files tell the server to reread the configuration:

systemctl reload postgresql