How to install PostgreSQL 14 and configure it to use md5 connection on Fedora 36

In this guide, we will walk through installing PostgreSQL 14 and then configure I to use an md5 connection whenever you are login into Postgres.

PostgreSQL is a powerful, open-source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. It was designed to handle a range of workloads from single machines to data warehousing.

Every database must conform to ACID properties i.e Atomicity, Consistency, Isolation, and Durability. This is true for PostgreSQL as well, it must conform to those features for transactions to pass the tests.

1. Update system repositories

Before you do any installation, begin by updating your repositories in order to make them up to date.

sudo dnf update -y

We can check the Apstream for available PostgreSQL first.

sudo dnf module list postgresql

This will list all available PostgreSQL in your Fedora Apstream. The output should look like this.

Fedora Modular 36 - x86_64
Name              Stream        Profiles                 Summary                
postgresql        10            client, server [d]       PostgreSQL module      
postgresql        11            client, server [d]       PostgreSQL module      
postgresql        12            client, server           PostgreSQL module      
postgresql        13            client, server           PostgreSQL module      
postgresql        14            client, server           PostgreSQL module      

Fedora Modular 36 - x86_64 - Updates
Name              Stream        Profiles                 Summary                
postgresql        10            client, server [d]       PostgreSQL module      
postgresql        11            client, server [d]       PostgreSQL module      
postgresql        12            client, server           PostgreSQL module      
postgresql        13            client, server           PostgreSQL module      
postgresql        14            client, server           PostgreSQL module      

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

2. Install PostgreSQL 14

To install PostgreSQL use the following command.

sudo dnf install postgresql-server postgresql-contrib

After installation is complete, we need to enable Postgressql, it is disabled by default in our system.

sudo systemctl enable postgresql

Now we need to populate the PostgreSQL database with initial data. This will create postgresql.conf and pg_hba.conf files.

$ sudo postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log

Then we can start Postgresql using the following code.

sudo systemctl start postgresql

Check if your PostgreSQL server is running.

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendo>
     Active: active (running) since Thu 2022-09-01 05:30:34 UTC; 32s ago
    Process: 13028 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql>
   Main PID: 13030 (postmaster)
      Tasks: 8 (limit: 4659)
     Memory: 16.4M
        CPU: 60ms
     CGroup: /system.slice/postgresql.service
             ├─ 13030 /usr/bin/postmaster -D /var/lib/pgsql/data
             ├─ 13031 "postgres: logger "
             ├─ 13033 "postgres: checkpointer "
             ├─ 13034 "postgres: background writer "
             ├─ 13035 "postgres: walwriter "
             ├─ 13036 "postgres: autovacuum launcher "
             ├─ 13037 "postgres: stats collector "
             └─ 13038 "postgres: logical replication launcher "

So to check the version of the installed server use the following command.

postgres -V
postgres (PostgreSQL) 14.3

3. Setting up password for Postgres role

Local connections on Postgresql use peer-to-peer connections to check the authenticated users. That is Postgresql instead of asking for the user password, it only checks on the logged-in user to ascertain if the user has credentials to get access to the resources.

Let’s change the way we log in and use a more strong password instead. Let’s set the password for the Postgres user first.

Open psql as the Postgres user with this command sudo -u postgres psql

$ sudo -u postgres psql

could not change directory to "/root": Permission denied
psql (14.3)
Type "help" for help.

The reason we are getting could not change directory to "/root": Permission denied is because of the Postgres authentication, we need to set the password first.

To change the Postgres password use the following command inside of the Postgres interface.

ALTER USER POSTGRES WITH ENCRYPTED PASSWORD 'mypasswrd';
ALTER ROLE

After we have set the password, we now need to tell Postgres to use this password. To do that we need to change pg_hba.conf file. I am using vim as my text editor here, you are free to use your preferred editor as well.

Look for this line of code and change peer to md5

sudo vi /var/lib/pgsql/data/pg_hba.conf

Restart PostgreSQL for the changes to take effect.

sudo systemctl restart postgresql

To ascertain that changes did take place login again to your Postgres server, if it asks for the password, then you are good t go.

$ psql -U postgres
psql (14.3)
Type "help" for help.

You can use the following code whenever you want to upgrade PostgreSQL to a higher version.

postgresql-setup upgrade