Here should be no trick concerning the subject, but I’ve been stuck for a day with it.

OK, having Oracle Linux 8 (CentOS 8), trying to install the postgresql:

sudo dnf install postgresql

The first surprise: by defauld the ancient version 10 is installed. Switch to something more fresh (e.g. 13) and install it.

sudo dnf remove postgresql
sudo dnf module reset postgresql
sudo dnf module list | grep postgre <check, which versions are available in the active repositories>
sudo dnf module enable postgresql:13
sudo dnf install postgresql

Phew, seems we are fine:

sudo systemctl start postgresql

Surprise: Unit postgresql.service could not be found.
Take a look on the directories:
/var/lib/pgsql – missing
/usr/lib/systemd/system/postgresql.service – missing

Why isn’t it in the first google search results, have no idea, even the chatGPT told me about only after several iterations (but the word had been of web-app development, so it is obvious, that I have to create my own DB, but not just to connect to existing one, hovewer it is not obvious, but considered).
The missing point – postgresql-server

sudo dnf install postgresql-server

Now run sudo systemctl start postgresql and… it doesn’t work again:

Job for postgresql.service failed because the control process exited with error code.
See "systemctl status postgresql.service" and "journalctl -xe" for details.

At least it is something new and the service exists at all.

Reinstalling of postgresql-package gives no result.

journalctl -xe output:

-- Unit postgresql.service has begun starting up.
postgresql-check-db-dir[1530742]: Directory "/var/lib/pgsql/data" is missing or empty.
postgresql-check-db-dir[1530742]: Use "/usr/bin/postgresql-setup --initdb"
postgresql-check-db-dir[1530742]: to initialize the database cluster.
postgresql-check-db-dir[1530742]: See /usr/share/doc/postgresql/README.rpm-dist for more info>
systemd[1]: postgresql.service: Control process exited, code=exited status=1
systemd[1]: postgresql.service: Failed with result 'exit-code'.

Following the advice:

sudo /usr/bin/postgresql-setup --initdb

Wow! Positive!

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Result:

postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
Active: active (running)

Not so complicated, isn’t? There is not the last trick: the first login is also not so obvious.

After installation PostgreSQL creates the UNIX user postgres with no password, that means, that this is generally blocked account and here is no way to login with the credentials directly. But if you try to open PostgreSQL console using any other account (even the root) the message appears:

psql: error: FATAL: role "username" does not exist

Here is the way to set the postgres user password with root permissions, but this default account makes the DB vulnerable against password hacking attacks. The proper way is to enter postgres account using

sudo su - postgres

or

sudo runuser postgres.

Then run the PostgreSQL console:

psql

And create your desired user account with password.

postgres=#CREATE USER username WITH PASSWORD 'secret';

And maybe it is necessary to give the user the permission to do something (pick your favorite):

GRANT CONNECT ON DATABASE database_name TO username;

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

ALTER USER username CREATEDB;

ALTER USER myuser WITH SUPERUSER;

PSQL can’t be open without any database, but the only existing are:

postgres
template0
template1

The existing databases may be listed with /l psql command. There is better not to connect template0 and template1 databases, or else there some troubles with creating of new databases will occur until the postgresql service is restarted.

If you enter with postgres user, psql opens the database with according name automatically. But in the case of cusomized user we have to point the database name directly:

psql -U username -d postgres

…but not so quick. By default any users from local machine have ‘ident’ authentication method, and do not accept any user credentials, but the matching any existing UNIX account only.

So, we have to bring then the /var/lib/pgsql/data/pg_hba.conf to the following order, if we want to create customized psql accounts:

#"local" is for Unix domain socket connections only
local all all trust
#IPv4 local connections:
host all all 127.0.0.1/32 trust
IPv6 local connections:
host all all ::1/128 trust
IPv4 remote connections:
host all all 0.0.0.0/0 md5
IPv6 remote connections:
host all all ::/0 md5
#Allow replication connections from localhost, by a user with the replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust

From now after reload of the configuration with e.g. sudo systemctl reload postgresql we may connect to psql databases in a following way:

• Without any auth if the login is performed from the same OS locally (local)
• Without any auth if the login is performed from the same host (localhost; 127.0.0.1/32; ::1/128)
• With password prompt if the login is performed from the outside

And at the end once more, login with customized user:

psql -U username -d postgres

… create your own database and connect to it:

CREATE DATABASE example_db WITH OWNER username;

\connect example_db

To check the database content use the \dt or \dt+ commands.

But it is not the complete solution. It gets obvious if we try to connect the psql from the remote host. Regardless the suthentification politics (remember, md5 password authentification from outside) the server doesn’t allow connections to default port 5432… Here is one more setting, which has to be done inside the main config file /var/lib/pgsql/data/postgresql.conf.

By default the paramenter listen_addresses = 'localhost' allows only the local connections. Here may be the entire list of IP addresses, perarated with ‘,’ pointed, or just set the parameter to listen_addresses = * to give an access from any forein IP.

Now, hope, surprises are over, wish me luck.