Step 1: Install PostgreSQL 14 on Ubuntu 22.04.2 LTS

1. Open the terminal and update the package list:

   sudo apt update
   

   

2. Install PostgreSQL 14 with the following command:

   sudo apt install postgresql-14 -y
   

   

3. PostgreSQL will automatically start after installation. You can check its status with the command:

   sudo systemctl status postgresql
   

   

Step 2: Secure PostgreSQL

1. Start by setting a password for the “postgres” user account (default):

   sudo passwd postgres
   

   

2. Switch to the “postgres” account and open the PostgreSQL shell:

   sudo -i -u postgres
   psql
   

   

3. Set a password for the “postgres” account:

   \password postgres
   

   

   Enter the new password and confirm.

4. Exit the PostgreSQL shell:

   \q
   exit
   

   

Step 3: Create Database and User

1. Log in to PostgreSQL with the “postgres” account:

   sudo -i -u postgres
   

2. Open the PostgreSQL shell:

   psql
   

3. Create a database (example: “mydatabase”):

   CREATE DATABASE mydatabase;
   

4. Create a user (example: “myuser”) and set a password:

   CREATE USER myuser WITH PASSWORD 'mypassword';
   

5. Assign privileges to the user to access and manage the database:

   GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;
   

6. End the session and exit:

   \q
   exit
   

Step 4: Check the connection

psql -h localhost -U myuser -d mydatabase

Step 5: Configure security

1. Open the configuration file:

   sudo nano /etc/postgresql/14/main/pg_hba.conf
   

   

2. Change the authentication method of all connections to “md5”:

   local   all             all                                     md5
   host    all             all
   

   

3. Restart PostgreSQL:

   sudo systemctl restart postgresql
   

Thank you for reading this article.