RDBMS with PostgreSQL

The Node.js application that we have been running as a systemd service since Chapter 15, and which we placed behind the reverse proxy app.example.local in Chapter 16, has not yet truly stored any data so far. As soon as the node process restarts or the server reboots, the entire state that once existed in memory disappears without a trace. Developers who build this kind of application sooner or later will definitely need a data storage solution that is persistent, structured, and reliable even when the server is restarted multiple times, a requirement answered by RDBMS (Relational Database Management System).

This chapter opens Part V with PostgreSQL, an open-source RDBMS often referred to by its own community as "the world's most advanced open source database". That reputation is not merely a marketing slogan; PostgreSQL is widely known for its strict compliance with SQL standards, full ACID (Atomicity, Consistency, Isolation, Durability) support to maintain data integrity, and a very rich extension ecosystem. We will start from installing PostgreSQL 18 on Ubuntu Server 26.04, understanding the two most important configuration files, namely postgresql.conf and pg_hba.conf, practicing the creation of a database along with its role and basic privileges, and closing by enabling a secure remote connection from outside the server itself.

18.1 Installing PostgreSQL 18

The release of PostgreSQL 18 in September 2025 brought a number of significant improvements at the engine level, including the Asynchronous I/O (AIO) subsystem that speeds up disk read operations, as well as the built-in uuidv7() function to generate time-ordered UUIDs, useful as a primary key that is more performance-friendly for indexes compared to random UUID v4. This version is the default PostgreSQL package in the official Ubuntu 26.04 LTS repository, so its installation can use standard APT without needing to add third-party repositories from the PostgreSQL Global Development Group (PGDG), consistent with the package installation philosophy we have followed since Chapter 6.

18.1.1 PostgreSQL Cluster Architecture on Ubuntu

Ubuntu wraps PostgreSQL through the postgresql-common package, a management layer that allows multiple major versions of PostgreSQL to be installed side-by-side on a single server without conflicting with each other, similar to the multi-version concept we learned from NVM and fnm in Chapter 15 for Node.js. Every running PostgreSQL instance on Ubuntu is called a cluster, a term that here has nothing to do with multi-server clusters like in Kubernetes, but rather refers to a collection of databases managed by a single postgres process listening on a specific port. The default cluster created after installation is named main, with data stored in /var/lib/postgresql/18/main and all of its configuration files, including postgresql.conf and pg_hba.conf which we will discuss in Section 18.2, placed separately in /etc/postgresql/18/main/. This separation of configuration from data differs from the upstream PostgreSQL custom on other distributions, which typically places both in a single data directory, and represents one of Debian/Ubuntu's packaging peculiarities that Sysadmins should know from the start.

18.1.2 Package Installation and Service Verification

The postgresql package in Ubuntu is a meta-package that automatically pulls in the latest available major version, while postgresql-contrib adds a number of additional, well-tested extensions frequently used in the field, such as pgcrypto for encryption functions and pg_stat_statements for query performance analysis.

Practical Steps

1. Update the package list, then install PostgreSQL along with its contrib package.

   sudo apt update
   sudo apt install postgresql postgresql-contrib

2. The installation automatically creates the main cluster, runs its service, and enables it at boot. Check its status via systemd.

   sudo systemctl status postgresql

3. View the list of installed PostgreSQL clusters on the server, including their version, port, and status.

   pg_lsclusters

4. Confirm the actual running version of PostgreSQL.

   psql --version

Verification and Troubleshooting

• A normal pg_lsclusters output displays a single line with version 18, cluster name main, port 5432, and status online. If the status is down, run sudo pg_ctlcluster 18 main start to start it manually.
• Unlike MySQL or MariaDB which we will cover in Chapters 19 and 20, installing PostgreSQL on Ubuntu never prompts for a database root password during the apt install process. Initial authentication is instead handled through the peer authentication mechanism that we will discuss in Section 18.2.1.
• The minor version number displayed in psql --version may differ depending on when the Ubuntu repository was last synchronized, just like the version notes for PHP in Section 14.1.1 and Certbot in Section 17.2.2.

18.2 Access Configuration: postgresql.conf and pg_hba.conf

PostgreSQL separates engine behavior settings from connection permissions into two different files. postgresql.conf manages runtime parameters such as the port used, listening network addresses, and memory allocation, while pg_hba.conf (host-based authentication) determines rules for who is allowed to connect, from where, to which database, and using what authentication method. Understanding the difference and relationship between the two is key before opening remote connections in Section 18.4, because the most common mistake made by new Sysadmins stems from assuming editing a single file is sufficient when both need to be adjusted together.

18.2.1 Examining the Default pg_hba.conf Rules

Rules inside pg_hba.conf are evaluated line by line from top to bottom, and PostgreSQL stops as soon as it finds the first line that matches the request's connection type, database, user, and source address, applying the authentication method on that line as-is. Line order is therefore critical; more specific rules must be placed before more general ones.

Practical Steps

1. Open the default pg_hba.conf file for the main cluster.

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

   Relevant active lines (not comments) usually look like the following:

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   local   all             postgres                                peer
   local   all             all                                     peer
   host    all             all             127.0.0.1/32            scram-sha-256
   host    all             all             ::1/128                 scram-sha-256

2. Try logging into psql via Unix socket as the postgres system role, using sudo -u postgres to switch OS identity first.

   sudo -u postgres psql

3. Type \q to exit, then try the second method: connect via TCP to the loopback address using the same role.

   psql -h 127.0.0.1 -U postgres

The first attempt successfully logs in without prompting for a password at all, while the second attempt immediately displays the prompt Password for user postgres:. This is pg_hba.conf working exactly as written: the line local ... peer matches connections via Unix socket and uses peer authentication, a method that permits login without a password as long as the OS username on the system matches the target PostgreSQL role name exactly. The line host ... 127.0.0.1/32 ... scram-sha-256 matches TCP connections to loopback and requires a password verified via SCRAM-SHA-256, a password hashing method that has been PostgreSQL's default since version 14 and is much more resistant to replay attacks compared to the older md5 method still found in outdated tutorials.

The two methods above are not the only options available in the METHOD column. The following table summarizes the methods Sysadmins encounter most frequently in the field, including two methods not yet shown in this default file.

Verification and Troubleshooting

• The second attempt above will fail with the message FATAL: password authentication failed because the postgres role was never assigned a password via the APT installation. This failure is normal and intentionally shown to demonstrate how pg_hba.conf works; it is not a step that needs fixing at this point.
• If the message Peer authentication failed for user "postgres" appears when running psql without sudo -u postgres first, it means the logged-in OS username (for example, deploy, following the user we created in Chapter 3) does not match the target role name, demonstrating the exact peer authentication mechanism just explained.
• The command \du inside psql displays a list of all existing roles on the server along with their respective attributes, serving as a quick reference throughout this chapter.

18.3 Creating Roles, Databases, and Basic Privileges

The scenario in this section follows the requirements of a Developer setting up a new backend: a database named webapp_db to store application data, along with a dedicated role named webapp_user used by the application to connect, separated from the superuser role postgres which should only be held by the Sysadmin. Separating application roles from administrative roles like this is a fundamental least privilege practice that we will explore deeper in Chapter 34, ensuring that credentials leaked from the application side do not automatically grant an Attacker full access to the entire database server.

18.3.1 Creating a Role and Database with Proper Ownership

PostgreSQL treats the concepts of users and groups as a single entity called a role. A role can log in directly like a user (if granted the LOGIN attribute) or function as a group containing other roles, without needing two separate systems as in some other RDBMSs.

Practical Steps

1. Log into psql as the postgres role via Unix socket, following the method proven successful in Section 18.2.1.

   sudo -u postgres psql

2. Create a new role for the application, complete with the LOGIN attribute and a password. Replace this example password with your own strong password before implementing it on a real server.

   CREATE ROLE webapp_user WITH LOGIN PASSWORD 'S4ngatRahasia!2026';

3. Create a new database, setting webapp_user as its owner directly upon creation.

   CREATE DATABASE webapp_db OWNER webapp_user;

4. Exit the postgres session, then test logging in with the new role via TCP to loopback, utilizing the scram-sha-256 rule present by default since Section 18.2.1.

   \q
   psql -h 127.0.0.1 -U webapp_user -d webapp_db

Verification and Troubleshooting

• A successful login is indicated by the prompt changing to webapp_db=>. Run \conninfo inside psql to verify that the session is truly connected as webapp_user to the database webapp_db.
• The message FATAL: database "webapp_db" does not exist usually indicates a typo in the database name in the -d flag, whereas FATAL: role "webapp_user" does not exist indicates that the CREATE ROLE command in the previous step was not actually executed; re-check with \du.

18.3.2 Understanding Basic Privileges and Public Schema Changes Since PostgreSQL 15

Prior to PostgreSQL 15, the public schema in every new database granted CREATE privileges by default to all roles through a pseudo-role named PUBLIC, meaning anyone who logged into that database, even via a role with minimal privileges, could create arbitrary tables in that schema. This behavior was considered too permissive from a security standpoint, so starting with PostgreSQL 15, the CREATE privilege on the public schema is no longer automatically granted to PUBLIC. Instead, new databases have a public schema owned by the pseudo-role pg_database_owner, which automatically represents whoever owns that database. Because step 18.3.1 above created webapp_db with OWNER webapp_user from the start, webapp_user automatically inherits full rights over the public schema in that database without requiring any additional GRANT commands, a pattern that represents the cleanest way to avoid schema privilege complexity in modern PostgreSQL versions.

Prove this directly by attempting to create a simple table using the webapp_user session still open from the previous step.

CREATE TABLE notes (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO notes (content) VALUES ('Database webapp_db siap dipakai');
SELECT * FROM notes;

All three commands above should execute smoothly without a single permission denied message. For more complex requirements, such as an additional role that should only read data without being able to modify it—like an account used by a reporting tool or BI dashboard—granular privileges can be granted directly on specific tables without touching database ownership at all.

CREATE ROLE webapp_readonly WITH LOGIN PASSWORD 'B4caSaja!2026';
GRANT CONNECT ON DATABASE webapp_db TO webapp_readonly;
GRANT USAGE ON SCHEMA public TO webapp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp_readonly;

These three GRANT lines each represent a different layer of privilege: CONNECT allows the role merely to open a connection to the database, USAGE on the schema permits the role to "look inside" the public schema (without it, access is blocked even if table privileges are granted), and SELECT on tables actually allows reading data. All three must be present together; if even one is missing, the connection will be rejected or the query will fail with permission denied.

Verification and Troubleshooting

• The command \dp notes inside psql displays the access privileges table for the notes table, a quick way to audit who has what privileges without needing to re-read the entire history of GRANT commands.
• The command GRANT ... ON ALL TABLES IN SCHEMA public only applies to tables that exist when the command is run. New tables created afterward are not automatically covered unless combined with ALTER DEFAULT PRIVILEGES, an advanced topic worth exploring further when production applications begin to feature many tables.

18.4 Secure Remote Connections

So far, all connections we tested originated from the server itself, either via Unix socket or through the loopback address 127.0.0.1. In the real world, databases are often accessed from other machines, such as a Developer's laptop writing application code, or a separate application server calling the database over an internal network. Enabling this kind of access requires three simultaneous changes: PostgreSQL must listen on the network interface (not just loopback), pg_hba.conf must have a rule for the target source address, and the OS-level firewall must allow traffic to the database port.

18.4.1 Enabling listen_addresses and Adding pg_hba.conf Rules

Practical Steps

1. Open postgresql.conf, locate the listen_addresses directive which defaults to localhost.

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

   Modify the line so that PostgreSQL listens on all network interfaces owned by the server.

   listen_addresses = '*'

2. The listen_addresses directive is a parameter only read when the PostgreSQL process first starts, unlike pg_hba.conf rules which can simply be reloaded. Perform a full restart of the cluster so this change takes effect.

   sudo systemctl restart postgresql

3. Add a new rule in pg_hba.conf, allowing connections from the entire lab network 192.168.1.0/24 that we have consistently used since Chapter 8, restricted specifically to the webapp_db database with the webapp_user role, rather than permitting all roles to all databases.

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

   host    webapp_db       webapp_user     192.168.1.0/24          scram-sha-256

   Place this line before any existing host all all ... lines if those rules also encompass the same network, following the top-down evaluation principle discussed in Section 18.2.1.
4. Rules in pg_hba.conf only need to be reloaded without a full restart, ensuring ongoing connections are not disconnected.

   sudo systemctl reload postgresql

Verification and Troubleshooting

• Ensure PostgreSQL is actually listening on all interfaces, not just loopback.

  ss -tlnp | grep 5432

  The correct output displays the address 0.0.0.0:5432, not 127.0.0.1:5432.
• Opening listen_addresses to * while granting pg_hba.conf rules with as narrow a network scope as possible, such as 192.168.1.0/24 instead of 0.0.0.0/0, is a far more secure combination than exposing the database to the entire internet. Production databases almost never have a reason to accept direct connections from the public internet, a misconfiguration that repeatedly causes data leaks in real-world incidents.

18.4.2 Opening UFW Firewall for PostgreSQL Port

PostgreSQL runs by default on port 5432. Just like port 443 which we opened specifically for HTTPS requirements in Section 17.3.2, this database port also needs to be explicitly opened in UFW, restricted to sources from the lab network rather than opened freely to everyone.

Practical Steps

1. Add a UFW rule allowing traffic to port 5432 specifically from the 192.168.1.0/24 network.

   sudo ufw allow from 192.168.1.0/24 to any port 5432 proto tcp

2. Verify that the rule is recorded properly.

   sudo ufw status

Verification and Troubleshooting

• The output of ufw status must display the 5432/tcp line with action ALLOW and source 192.168.1.0/24, not Anywhere.
• If UFW is not active at all on this server, enable it first with sudo ufw enable, but ensure the SSH rule from Chapter 3 is in place beforehand so active remote sessions do not get disconnected.

18.4.3 Testing Connections from Another Client on the Network

Practical Steps

1. From another computer on the same lab network, such as a Developer's laptop, install only the PostgreSQL client without the full server.

   sudo apt install postgresql-client

2. Attempt to connect to the database server via IP 192.168.1.20, using the role and database created in Section 18.3.1.

   psql -h 192.168.1.20 -U webapp_user -d webapp_db

Verification and Troubleshooting

• A successful connection will prompt for a password and then display the webapp_db=> prompt, identical to testing via loopback in Section 18.3.1, except this time actually traversing the network.
• The message psql: error: connection to server ... failed: Connection refused indicates PostgreSQL is not listening on the network interface or the firewall is still blocking port 5432; re-check Sections 18.4.1 and 18.4.2.
• The message FATAL: no pg_hba.conf entry for host "192.168.1.x", user "webapp_user", database "webapp_db" indicates the rule in pg_hba.conf does not match, either because the written subnet is incorrect or the line is overridden by another rule positioned above it.
• Attempting to use the webapp_readonly role from Section 18.3.2 to connect to webapp_db over the network will fail with the same pg_hba.conf message, because the rule in Section 18.4.1 intentionally covers only webapp_user. Adding another role to remote access means deliberately adding a new pg_hba.conf line, rather than carelessly broadening existing rules.
• Connections over an internal lab network like this still consist of plain TCP traffic without encryption, which is sufficiently safe as long as the network is fully trusted. For databases accessed over more open networks or across data centers, PostgreSQL supports TLS connections via the ssl = on directive along with certificate and private key pairs, using the exact same principles as TLS in Nginx and Apache discussed thoroughly in Chapter 17.

At this point, the server is running PostgreSQL 18 complete with the webapp_db database owned by a dedicated application role, separate from the administrative postgres role, and safely accessible from the lab network via pg_hba.conf rules and strictly restricted firewall settings. Chapter 19 continues Part V with the second RDBMS, MySQL, directly comparing its inner workings with PostgreSQL, including authentication philosophy differences and privilege management between the two.