Below are the complete, tested steps to install and set up PostgreSQL 15 (the default in Ubuntu 22.04) on Ubuntu 22.04 LTS .

Step 1: Update System Packages

bash

sudo apt update && sudo apt upgrade -y

Step 2: Install PostgreSQL

bash

sudo apt install postgresql postgresql-contrib -y

This installs PostgreSQL 15 and useful additional tools.

Step 3: Verify PostgreSQL is Running

bash

sudo systemctl status postgresql

You should see active (running).

PostgreSQL starts automatically on boot.

Step 4: Switch to the postgres User

PostgreSQL creates a system user called postgres.

bash

sudo -i -u postgres

Step 5: Access PostgreSQL Shell

bash

psql

You’ll enter the PostgreSQL prompt:

text

postgres=#

Step 6: Create a Database & User (for your PROJECT)

Example: Create user iotuser and database iotdb

sql

CREATE USER iotuser WITH ENCRYPTED PASSWORD 'your_secure_password';
CREATE DATABASE iotdb OWNER iotuser;
GRANT ALL PRIVILEGES ON DATABASE iotdb TO iotuser;

Replace ‘your_secure_password’ with a strong password.

Exit psql:

sql

\q

Exit postgres shell:

bash

exit

Step 7: Test Connection with New User

bash

psql -U iotuser -d iotdb -h localhost -W

Enter the password when prompted. You should connect successfully.

Step 8: Allow Remote Connections (Optional – for IoT devices)

If your IoT devices will connect over the network:

1. Edit postgresql.conf

bash

sudo nano /etc/postgresql/15/main/postgresql.conf

Find and change:

conf

#listen_addresses = 'localhost'

to:

conf

listen_addresses = '*'

2. Edit pg_hba.conf

bash

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

Add this line at the bottom:

conf

host all all 192.168.1.0/24 md5

Adjust 192.168.1.0/24 to your network (or use 0.0.0.0/0 for all – not recommended in production).

3. Restart PostgreSQL

bash

sudo systemctl restart postgresql

4. Open Port 5432 (if using firewall)

bash

sudo ufw allow 5432/tcp

Step 9: Secure Your Installation (Recommended)

Change postgres user password:

bash

sudo -u postgres psql

sql

ALTER USER postgres WITH ENCRYPTED PASSWORD 'new_strong_password';
\q

Enable SSL (for production):

bash

sudo apt install openssl
# Generate self-signed cert (or use Let's Encrypt)
sudo openssl req -new -x509 -days 365 -nodes -out /etc/ssl/certs/postgresql.crt -keyout /etc/ssl/private/postgresql.key
sudo chmod 640 /etc/ssl/private/postgresql.key
sudo chown postgres:postgres /etc/ssl/private/postgresql.key

Then enable in postgresql.conf:

conf

ssl = on ssl_cert_file = '/etc/ssl/certs/postgresql.crt' ssl_key_file = '/etc/ssl/private/postgresql.key'

Restart:

bash

sudo systemctl restart postgresql

Final Test (from another machine or app)

bash

psql -h YOUR_SERVER_IP -U iotuser -d iotdb -W

Useful Commands

Command	Purpose
sudo systemctl start postgresql	Start service
sudo systemctl stop postgresql	Stop service
sudo systemctl restart postgresql	Restart
createdb mydb	Create DB (as postgres)
dropdb mydb	Delete DB
pg_dump iotdb > backup.sql	Backup
psql iotdb < backup.sql	Restore

Done! PostgreSQL is now ready for your IoT dashboard (e.g., Node-RED, Grafana, Django, etc.).

Let me know your stack (e.g., Python, Node.js) if you want connection strings or ORM setup!

PART-2

Why “localhost” Makes Sense Here

Localhost = 127.0.0.1: This refers to the same machine you’re on. When you’re SSH’d into your VPS (e.g., via PuTTY, Terminal, or hPanel’s console) and running commands like psql -U iotuser -d iotdb -h localhost, it connects to PostgreSQL on the VPS itself. It’s secure, fast (no network latency), and ideal for:
- Testing your dashboard app (e.g., Grafana, Node-RED, or whatever you’re using) locally on the VPS.
- Initial setup and debugging—everything stays internal.
No need to change it yet: If your IoT dashboard is running on the VPS and querying the DB from the same server (common for a self-contained project), stick with localhost. It’s like testing on your local Ubuntu machine, but scaled to the cloud.

When/Why You’d Switch from Localhost

For a real IoT setup, your devices (sensors, Raspberry Pis, etc.) are probably remote—on your home network, in the field, or elsewhere. They can’t reach “localhost” on your VPS (that’s only accessible from the VPS itself). In that case:

Use your VPS’s public IPv4 (e.g., something like 185.123.45.67—check it in hPanel or via curl ifconfig.me on the VPS).
Or, if you have a domain pointed to the VPS, use that (e.g., db.yourdomain.com).

This is exactly why I marked Step 8 as “Optional – for IoT devices” in the original guide. If your sensors need to send data directly to the DB, enable remote access there.

Quick VPS-Specific Adjustments

Since you’re on Hostinger’s KVM 2 (Ubuntu 22.04 pre-installed, I assume), here’s how to adapt:

Find Your VPS Public IP (for remote connections):bashcurl ifconfig.me
- Note it down. Hostinger gives you one dedicated IPv4 per VPS—no extras needed unless you scale up.
Update the Test Connection for Remote Use (from Step 7):
- From your local machine (not the VPS), install psql client if needed (sudo apt install postgresql-client on Ubuntu/Mac, or download for Windows).
- Then test:bashpsql -h YOUR_VPS_PUBLIC_IP -U iotuser -d iotdb -W
  - Enter password. If it fails, ensure Step 8 is done (remote config + firewall).
Hostinger Firewall Note:
- Hostinger has a built-in firewall in hPanel (under VPS > Firewall). After opening port 5432 in UFW (as in Step 8), also add a rule in hPanel:
  - Protocol: TCP
  - Port: 5432
  - Source: Your IoT devices’ IPs (or 0.0.0.0/0 for all—risky, but okay for testing).
- This is in addition to UFW, as Hostinger’s is an extra layer.
Security Reminder for VPS:
- Never expose PostgreSQL publicly without SSL (as in Step 9). IoT traffic can be sniffed—use VPN (e.g., WireGuard on the VPS) for devices if possible.
- Monitor resources in hPanel (CPU/RAM/disk) since IoT data can spike usage.
- Backups: Hostinger includes weekly snapshots—enable them for your DB.

Example Connection String for Your IoT App

If your dashboard uses Python (e.g., with psycopg2) or Node.js:

Local (on VPS): postgresql://iotuser:password@localhost:5432/iotdb
Remote (from devices/app): postgresql://iotuser:password@YOUR_VPS_IP:5432/iotdb

If your IoT devices are connecting directly (e.g., via MQTT to Node-RED, which writes to PG), follow Step 8 to allow their IPs.

PART 3 CHECKING STATUS:

How to Check Real Status (Better Commands)

Use PostgreSQL-specific tools instead of systemctl status postgresql:

bash

# 1. Check cluster status
sudo pg_lsclusters

Expected output (healthy):

text

Ver Cluster Port Status Owner    Data directory              Log file
15  main    5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

online = running
down = stopped

bash

# 2. Test connection
psql -U postgres -h localhost -c "\l"

TL;DR: You’re 100% Fine

bash

sudo pg_lsclusters
# → shows "online"? You're good.
psql -U iotuser -d iotdb -h localhost -W
# → connects? Database is healthy.

active (exited) = normal and expected on Ubuntu 22.04 with PostgreSQL 15.

Your IoT dashboard can proceed — DB is up and running!

PART 4: USER CONNECTION TO DATABASE PROBLEM RESOLUTION:

⚠️ The Error Explained

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed:
FATAL:  Peer authentication failed for user "iotadmin"

What it means:

PostgreSQL tried to authenticate your login using the peer method, but you’re not a Linux system user named iotadmin.
So it refused the connection.

🧠 PostgreSQL Authentication Basics

When you connect via psql, PostgreSQL checks a configuration file:

/etc/postgresql/15/main/pg_hba.conf

(Version number 15 may vary; you can check with ls /etc/postgresql/)

This file defines who can connect and how.

Each line has columns like:

TYPE   DATABASE   USER     ADDRESS        METHOD

Example:

local   all        postgres                peer
local   all        all                     peer
host    all        all       127.0.0.1/32  scram-sha-256

local = connections through UNIX socket (same server)
peer = trust only if Linux username == PostgreSQL username
scram-sha-256 / md5 = password-based authentication

So right now, your system says:

“Local logins use peer authentication — I’ll only accept if the Linux username matches the database username.”

You are logged into Ubuntu as user, not iotadmin.
Hence → “Peer authentication failed”.

✅ The Fix — Switch `iotadmin` to Password Authentication

We’ll edit PostgreSQL’s auth config to allow password-based login for local users.

Step 1 — Open pg_hba.conf

Run this:

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

(The * handles your version, e.g., 15)

Scroll until you find lines like:

# "local" is for Unix domain socket connections only
local   all             all                                     peer

Step 2 — Change “peer” to “scram-sha-256” (modern password method)

Modify that line to:

local   all             all                                     scram-sha-256

💡 scram-sha-256 is newer and more secure than md5.
You can also use md5 if your PostgreSQL version doesn’t support SCRAM.

Do NOT change the “host” lines yet, only the first “local” one.

Step 3 — Save and Exit

In Nano:

Ctrl + O → Save
Enter
Ctrl + X → Exit

Step 4 — Restart PostgreSQL Service

Apply the change:

sudo systemctl restart postgresql

Check it’s running:

sudo systemctl status postgresql

Should say active (running) ✅

Step 5 — Try Connecting Again

Now use:

psql -U iotadmin -d iotdb

It will now ask for a password (the one you set during CREATE USER iotadmin ...).
Enter it.

If successful, you’ll see:

psql (15.x)
Type "help" for help.

iotdb=>

Then confirm:

\conninfo

Output should show:

You are connected to database "iotdb" as user "iotadmin" using scram-sha-256 authentication.

🔍 Optional Check: Verify Your pg_hba.conf Entry Works

To see the active rules:

sudo cat /etc/postgresql/*/main/pg_hba.conf | grep -v '^#'

You should see:

local   all   all   scram-sha-256
host    all   all   127.0.0.1/32   scram-sha-256
...

✅ Recap — What You Just Learned

Concept	Description
Peer Authentication	Works only if Linux user == DB user
Password Authentication (scram-sha-256)	Uses password regardless of Linux user
pg_hba.conf	Controls who can connect and how
Restart Service	Needed after any PostgreSQL config change
Now:	You can connect to DB from any Linux user with correct password

Once you confirm successful connection, reply with:

✅ Connected to iotdb as iotadmin via password

🧠 Quick Recap of What We’re Doing

We want PostgreSQL to:

Authenticate local users (like iotadmin) using their passwords, not by matching Linux usernames.

That means changing the “authentication method” column for local and host entries to:

scram-sha-256

✅ Step-by-Step Fix

Step 1 — Open the file fully

Run:

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

Once inside Nano:

Use the arrow keys to move right/left (so you can see entire lines)
The file will have many comment lines (#) — ignore those.

Find lines that start like this:

local   all             postgres
local   all             all
host    all             all             127.0.0.1/32
host    all             all             ::1/128

Step 2 — Replace the method at the end of each line

Change them so they look like this exactly (these are the last words on each line):

local   all             postgres                                scram-sha-256
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32             scram-sha-256
host    all             all             ::1/128                  scram-sha-256

Step 3 — Save and exit Nano

In Nano:

Ctrl + O   → Save
Enter      → Confirm filename
Ctrl + X   → Exit

Step 4 — Restart PostgreSQL

Apply the new authentication rules:

sudo systemctl restart postgresql

Verify it’s running:

sudo systemctl status postgresql

You should see active (running).

Step 5 — Test your connection

Now, from your Ubuntu user shell:

psql -U iotadmin -d iotdb

It should now prompt:

Password for user iotadmin:

Enter your password → and you should get the iotdb=> prompt 🎉

Then run:

\conninfo

Expected output:

You are connected to database "iotdb" as user "iotadmin" using scram-sha-256 authentication.

⚙️ Step 6 (optional) — Verify the active pg_hba rules

To confirm your edit is in place:

sudo grep -v '^#' /etc/postgresql/*/main/pg_hba.conf

You should see:

local   all   postgres   scram-sha-256
local   all   all        scram-sha-256
host    all   all        127.0.0.1/32   scram-sha-256
host    all   all        ::1/128        scram-sha-256
...

🧩 Summary

Step	Action	Result
Edit pg_hba.conf	changed all local/host methods to `scram-sha-256`	✅
Restart PostgreSQL	applied new auth rules	✅
Connect as `iotadmin`	now uses password	✅

When done, please reply with:

✅ Connected to iotdb as iotadmin via password (scram-sha-256)

🧠 What You Have Right Now

Line	Type	What it Does	Current Auth
`local all postgres`	Local (socket)	PostgreSQL superuser (`postgres`)	peer
`local all all`	Local (socket)	All users (like `iotadmin`)	peer
`host all all 127.0.0.1/32`	TCP IPv4 (localhost)	For 127.0.0.1 connections	scram-sha-256 ✅
`host all all ::1/128`	TCP IPv6	For localhost IPv6 connections	scram-sha-256 ✅

So right now:

Password login (scram-sha-256) is allowed only for TCP connections (host lines).
When you just run psql -U iotadmin -d iotdb, PostgreSQL connects via Unix socket, which uses the local lines — still set to peer.

That’s why it still fails with peer authentication failed if you try to connect locally without -h localhost.

✅ Two Easy Fix Options

You can solve this in two ways, depending on your preference:

🟢 Option 1: Keep Both Peer and Password (recommended & flexible)

You can keep “peer” for the postgres admin, but switch all others to password-based auth.

Edit the file:

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

Change these lines:

local   all             postgres                                peer
-local   all             all                                     peer
+local   all             all                                     scram-sha-256

Save (Ctrl + O, Enter, Ctrl + X)
Then restart:

sudo systemctl restart postgresql

✅ This keeps:

postgres still using peer (for system admin tasks)
All other users (iotadmin, etc.) can log in using passwords (both local and remote connections)

Now you can connect simply with:

psql -U iotadmin -d iotdb

and it’ll prompt for a password.

🔵 Option 2: Keep it simple (use TCP always)

If you don’t want to edit the file right now, you can also connect using TCP explicitly:

psql -U iotadmin -d iotdb -h 127.0.0.1

That will use the host rule (scram-sha-256) and prompt for the password successfully.

✅ Works right away without editing config.
But it’s better long-term to fix the “local” rule as in Option 1.

⚙️ Recommended Setup (Best Practice)

Here’s what your final lines should look like:

local   all             postgres                                peer
local   all             all                                     scram-sha-256
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Restart PostgreSQL after saving:

sudo systemctl restart postgresql

✅ Test Connection Again

Now test both methods:

Socket:

psql -U iotadmin -d iotdb

TCP:

psql -U iotadmin -d iotdb -h 127.0.0.1

Both should prompt:

Password for user iotadmin:

Then give you the iotdb=> prompt. 🎉

Check connection info:

\conninfo

→ You should see:

You are connected to database "iotdb" as user "iotadmin" using scram-sha-256 authentication.

🧩 Summary

Setting	Purpose	Status
`local all postgres peer`	Keep peer for system admin	✅ Good
`local all all scram-sha-256`	Enable password auth for normal users	✅ Correct
`host all all 127.0.0.1/32 scram-sha-256`	IPv4 password auth	✅ Correct
Restarted PostgreSQL	Reloaded config	✅
Connection test	`iotadmin` login works via password	🎯 Success

Once you test and confirm success, reply with:

✅ Connected to iotdb as iotadmin via password (scram-sha-256)

So, please proceed with these confirmed safe steps next:

Open and edit /etc/postgresql/*/main/pg_hba.conf
→ change peer → scram-sha-256
Restart PostgreSQL sudo systemctl restart postgresql
Test connection psql -U iotadmin -d iotdb

Then reply with either:

✅ “Connected to iotdb as iotadmin via password”

PART 5: FIREWALL SETTINGS

🧠 Firewall Concept Refresher

UFW (Uncomplicated Firewall) is a simple interface for Linux’s internal firewall system (iptables or nftables).
It controls which network ports can be accessed from outside the server.

Your server constantly listens on many ports — but only the ones UFW allows can be reached by the internet.

✅ Your Current Rules (Fully Explained)

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
80/tcp                     ALLOW       Anywhere
443                        ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
80/tcp (v6)                ALLOW       Anywhere (v6)
443 (v6)                   ALLOW       Anywhere (v6)

Port	Protocol	Purpose	Explanation
22	SSH	`OpenSSH`	Allows you to connect via SSH (how you log in to your VPS). If you block this, you’ll lose remote access.
80	HTTP	Web	For normal, non-encrypted website traffic. Used by NGINX for the dashboard and Let’s Encrypt certificate verification.
443	HTTPS	Web (secure)	Encrypted dashboard access after we set up SSL (important for your customers).

(v6) entries mean the same rules are also applied to IPv6 traffic — it’s good and safe to have them.

🚫 What’s Not Open (and That’s Good)

5432 (PostgreSQL) → blocked from outside world. ✅
→ Your database can only be accessed locally by your backend API.
1883 (MQTT) → blocked from outside. ✅
→ We’ll open this later, only after Mosquitto is secured with passwords and TLS.
6379 (Redis) → blocked. ✅
→ Always keep Redis local only.

You are following best security practice:

“Expose only what’s necessary, and only when ready.”

🧩 Optional Commands to Remember

Command	Use
`sudo ufw status verbose`	shows default policy (should be “deny incoming”)
`sudo ufw allow 1883/tcp`	open MQTT later (only after securing it)
`sudo ufw delete allow 1883/tcp`	close MQTT again
`sudo ufw reload`	reapply rules after editing manually
`sudo ufw disable` / `sudo ufw enable`	turn firewall off/on (avoid disabling in production)

✅ Current Firewall Evaluation

Aspect	Status
SSH secure remote access	✅
Web (HTTP/HTTPS) open for dashboard	✅
All other ports blocked	✅
IPv6 protection mirrored	✅
Perfect for your current setup	✅

You don’t need to change anything right now.

🔒 Summary:

🟢 Your firewall is configured exactly as it should be for this stage.
Nothing is exposed that shouldn’t be.

GILKUT®

Step 1: Update System Packages

Step 2: Install PostgreSQL

Step 3: Verify PostgreSQL is Running

Step 4: Switch to the postgres User

Step 5: Access PostgreSQL Shell

Step 6: Create a Database & User (for your PROJECT)

Example: Create user iotuser and database iotdb

Step 7: Test Connection with New User

Step 8: Allow Remote Connections (Optional – for IoT devices)

1. Edit postgresql.conf

2. Edit pg_hba.conf

3. Restart PostgreSQL

4. Open Port 5432 (if using firewall)

Step 9: Secure Your Installation (Recommended)

Change postgres user password:

Enable SSL (for production):

Final Test (from another machine or app)

Useful Commands

PART-2

Why “localhost” Makes Sense Here

When/Why You’d Switch from Localhost

Quick VPS-Specific Adjustments

Example Connection String for Your IoT App

PART 3 CHECKING STATUS:

How to Check Real Status (Better Commands)

TL;DR: You’re 100% Fine

PART 4: USER CONNECTION TO DATABASE PROBLEM RESOLUTION:

⚠️ The Error Explained

What it means:

🧠 PostgreSQL Authentication Basics

✅ The Fix — Switch iotadmin to Password Authentication

Step 1 — Open pg_hba.conf

Step 2 — Change “peer” to “scram-sha-256” (modern password method)

Step 3 — Save and Exit

Step 4 — Restart PostgreSQL Service

Step 5 — Try Connecting Again

🔍 Optional Check: Verify Your pg_hba.conf Entry Works

✅ Recap — What You Just Learned

🧠 Quick Recap of What We’re Doing

✅ Step-by-Step Fix

Step 1 — Open the file fully

Step 2 — Replace the method at the end of each line

Step 3 — Save and exit Nano

Step 4 — Restart PostgreSQL

Step 5 — Test your connection

⚙️ Step 6 (optional) — Verify the active pg_hba rules

🧩 Summary

🧠 What You Have Right Now

✅ Two Easy Fix Options

🟢 Option 1: Keep Both Peer and Password (recommended & flexible)

🔵 Option 2: Keep it simple (use TCP always)

⚙️ Recommended Setup (Best Practice)

✅ Test Connection Again

🧩 Summary

PART 5: FIREWALL SETTINGS

🧠 Firewall Concept Refresher

✅ Your Current Rules (Fully Explained)

🚫 What’s Not Open (and That’s Good)

🧩 Optional Commands to Remember

✅ Current Firewall Evaluation

🔒 Summary:

Comments

Leave a Reply Cancel reply

Social Media

✅ The Fix — Switch `iotadmin` to Password Authentication