Installation and initial setup of PostgreSQL for local use

Step 1: Installation of PostgreSQL

Add the YUM repo to your host:

$ sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-37-x86_64/pgdg-fedora-repo-latest.noarch.rpm

Install and start PostgreSQL. Here we go with PostgreSQL 15:

$ sudo dnf install postgresql15-server postgresql15
$ sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
$ sudo systemctl start postgresql-15
$ sudo systemctl enable postgresql-15

Step 2: Set a password on the postgres user

The installation will create a postgres UNIX user on your system. Postgres is configured by default to log in with system user accounts.The postgres user is a superuser for PostgreSQL. The postgres user is setup without a password in PostgreSQL:

So, let's change it's password first:

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

postgres=# alter user postgres with password 'postgres';
ALTER ROLE

Step 3: Allow local access to PostgreSQL

Next up, we want to allow access from local connections to the PostgreSQL server. We'll edit the /var/lib/pgsql/11/data/postgresql.conf file:

sudo vim /var/lib/pgsql/10/data/pg_hba.conf

Alter the IPv4 section so it reads:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             localhost               md5

This allows connections from the loopback device as well as 127.0.0.1. The md5 modifier allows for authentication with PostgreSQL user accounts that aren't tied to a system user.

Step 4: creating users and databases

$ sudo -i -u postgres
# e.g. netsensei
$ createuser --interactive
$ createdb netsensei -O netsensei -T template0 -l en_US.UTF-8 -E UTF8

Let's set privileges

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

postgres=# grant all privileges on database netsensei to netsensei ;