Module 02 — PostgreSQL Setup

Objective

Install PostgreSQL on db-server, create the application database and tables, and configure remote access so app-server can connect.

By the end of this module you will have:

Component	Detail
PostgreSQL	Running on db-server (192.168.56.11)
Database	customerdb owned by appuser
Tables	customers and users
Remote access	app-server can connect to the database over the network

Prerequisites

• Module 01 complete — all 3 VMs running and accessible via ssh <hostname> from your Mac
• SSH access to db-server: ssh db-server

All commands in this module are run on db-server. Start by opening a terminal on your Mac and connecting:

ssh db-server

---

1. Install PostgreSQL

1.1 Update packages and install PostgreSQL

sudo apt update && sudo apt install -y postgresql postgresql-contrib

This installs the PostgreSQL server and a set of commonly used extensions.

1.2 Verify the service is running

sudo systemctl status postgresql

Expected output (key line):

Active: active (exited)

Note: PostgreSQL shows active (exited) rather than active (running) because the main postgresql service is a wrapper that starts the actual cluster process. This is normal.

1.3 Verify the PostgreSQL version

sudo -u postgres psql -c "SELECT version();"

Expected output:

                                                              version
------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 16.x on x86_64-pc-linux-gnu, compiled by gcc ...
(1 row)

You should see PostgreSQL 16 (the version included with Ubuntu 24.04).

---

2. Create the Database and User

2.1 Open the PostgreSQL interactive shell

sudo -u postgres psql

You should see the postgres=# prompt. This connects you as the built-in postgres superuser.

2.2 Create the application user and database

Run these SQL commands one at a time at the postgres=# prompt:

CREATE USER appuser WITH PASSWORD 'apppassword123';

CREATE DATABASE customerdb OWNER appuser;

\q

The \q command exits the PostgreSQL shell and returns you to the regular terminal.

2.3 Verify the new user can connect

psql -U appuser -d customerdb -h localhost -c "SELECT 1;"

When prompted, enter the password: apppassword123

Expected output:

 ?column?
----------
        1
(1 row)

This confirms the appuser account works and can connect to customerdb.

---

3. Create Tables

3.1 Connect to the database as appuser

psql -U appuser -d customerdb -h localhost

Enter the password: apppassword123

You should see the customerdb=> prompt.

3.2 Create the customers table

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

Expected output:

CREATE TABLE

3.3 Create the users table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Expected output:

CREATE TABLE

3.4 Insert a default admin user

INSERT INTO users (username, password) VALUES ('admin', 'admin123');

Expected output:

INSERT 0 1

3.5 Verify the tables were created

\dt

Expected output:

           List of relations
 Schema |   Name    | Type  |  Owner
--------+-----------+-------+---------
 public | customers | table | appuser
 public | users     | table | appuser
(2 rows)

3.6 Verify the admin user was inserted

SELECT * FROM users;

Expected output:

 id | username | password |         created_at
----+----------+----------+----------------------------
  1 | admin    | admin123 | 2025-...
(1 row)

3.7 Exit the PostgreSQL shell

\q

---

4. Configure Remote Access

By default, PostgreSQL only listens for connections from localhost. To let app-server connect, you need to change two configuration files.

4.1 Allow PostgreSQL to listen on all network interfaces

Open the PostgreSQL configuration file:

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

Find the line that says:

#listen_addresses = 'localhost'

Tip: In nano, press Ctrl+W to search. Type listen_addresses and press Enter to jump to the line.

Change it to:

listen_addresses = '*'

Make sure you remove the # at the beginning of the line (the # means the line is commented out).

Save and exit: Ctrl+O, Enter, Ctrl+X.

4.2 Allow password authentication from the network

Open the host-based authentication file:

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

Scroll to the bottom of the file and add this line:

host    all    all    192.168.56.0/24    md5

This allows any host on the 192.168.56.0/24 network (your VMs) to connect to any database using password authentication.

Save and exit: Ctrl+O, Enter, Ctrl+X.

4.3 Restart PostgreSQL to apply changes

sudo systemctl restart postgresql

4.4 Verify PostgreSQL restarted successfully

sudo systemctl status postgresql

Expected output (key line):

Active: active (exited)

4.5 Verify PostgreSQL is listening on all interfaces

sudo ss -tlnp | grep 5432

Expected output:

LISTEN  0  244  0.0.0.0:5432  0.0.0.0:*  users:(("postgres",pid=...,fd=...))

The 0.0.0.0:5432 confirms PostgreSQL is listening on all interfaces, not just localhost.

---

5. Verify Remote Access from app-server

Now test that app-server can connect to the database on db-server.

5.1 Open a new terminal and connect to app-server

On your Mac, open a new terminal window and run:

ssh app-server

5.2 Install the PostgreSQL client on app-server

sudo apt update && sudo apt install -y postgresql-client

5.3 Connect to the database on db-server

psql -U appuser -d customerdb -h 192.168.56.11 -c "SELECT 1;"

Enter the password: apppassword123

Expected output:

 ?column?
----------
        1
(1 row)

This confirms app-server can reach the PostgreSQL database on db-server over the network.

5.4 Exit app-server

exit

---

Troubleshooting

"Connection refused" when connecting from app-server

Cause: PostgreSQL is not listening on the network interface, or the service was not restarted after changing the configuration.

Fix: On db-server, verify listen_addresses is set correctly:

sudo grep listen_addresses /etc/postgresql/16/main/postgresql.conf

It should show:

listen_addresses = '*'

Also verify the pg_hba.conf entry exists:

sudo grep "192.168.56" /etc/postgresql/16/main/pg_hba.conf

It should show:

host    all    all    192.168.56.0/24    md5

If either is missing, make the changes from Section 4 and restart:

sudo systemctl restart postgresql

"Password authentication failed"

Cause: Either the password is wrong, or pg_hba.conf is using peer authentication instead of md5 for the connection.

Fix:

1. Double-check you are using the correct password: apppassword123

2. Make sure you are connecting with -h localhost (or the IP address). Without -h, PostgreSQL uses peer authentication (which checks your Linux username) instead of password authentication.

3. Verify the pg_hba.conf has md5 for the connection type you are using:

   sudo grep -v "^#" /etc/postgresql/16/main/pg_hba.conf | grep -v "^$"

"psql: command not found" on app-server

Cause: The PostgreSQL client is not installed on app-server.

Fix:

sudo apt update && sudo apt install -y postgresql-client

"could not connect to server: No route to host"

Cause: db-server's firewall is blocking the connection, or the VMs cannot reach each other.

Fix:

1. First verify basic connectivity from app-server:

   ping -c 2 192.168.56.11

2. If ping works but PostgreSQL does not, check if a firewall is blocking port 5432 on db-server:

   sudo ufw status

   If ufw is active and blocking, allow PostgreSQL:

   sudo ufw allow 5432/tcp

---

What You Have Now

Component	Status
PostgreSQL 16	Running on db-server (192.168.56.11)
Database customerdb	Created, owned by appuser
Table customers	Ready for application data
Table users	Contains default admin account
Remote access	app-server can connect on port 5432

The database layer is ready. In Module 03, you will build the Go backend that connects to this database and serves a REST API.