8.2 PostgreSQL Database

8.2 PostgreSQL Database

PostgreSQL is a full-featured, open-source relational database management system (RDBMS), and it matters on Kali for one big reason: the Metasploit Framework uses it to store hosts, services, loot, and credentials collected during an engagement. The KLCP exam expects you to install it, start it, understand its Debian-specific cluster management, and handle its authentication model. As with Apache, PostgreSQL is a network service that Kali leaves disabled by default — installing it does not start it.

Installation, Port, and Service Control

Install with sudo apt update && sudo apt install postgresql. This metapackage pulls in whichever versioned package is current for your Kali release plus the client tools — check yours with pg_lsclusters and substitute that number wherever this section shows a version. Key facts to hold onto:

• PostgreSQL listens on TCP port 5432 by default, bound to localhost on Kali.
• Control the service with sudo systemctl start postgresql, sudo systemctl enable postgresql, and systemctl status postgresql.
• The database runs under a dedicated operating system account named postgres, which is also the name of the database superuser role.

Clusters: pg_lsclusters and pg_ctlcluster

Debian wraps PostgreSQL in a cluster abstraction, and the exam tests the wrapper commands, not just raw postgres binaries. A cluster is a single running database instance with its own data directory and port. When you install the package, Debian automatically creates one cluster named main for the installed version. Two commands manage clusters:

• pg_lsclusters — lists every cluster with its version, name, port, status (online/down), and data directory. Typical output shows 15 main 5432 online postgres /var/lib/postgresql/15/main.
• pg_ctlcluster — starts, stops, restarts, or reloads one cluster. Example: sudo pg_ctlcluster 15 main start. The arguments are always version, cluster name, then action.

The data lives in /var/lib/postgresql/<version>/main and the configuration in /etc/postgresql/<version>/main/, which holds postgresql.conf (listening addresses, port) and pg_hba.conf (authentication).

Authentication: peer versus password methods in pg_hba.conf

pg_hba.conf (host-based authentication) is the file that decides who may connect, from where, to which database, and how they prove their identity. Each line is a rule of the form type database user address method. The methods the exam cares about are:

The stock pg_hba.conf grants peer authentication to local connections. That single fact explains the standard administration idiom: the postgres OS user maps to the postgres database superuser, so you administer the server with:

sudo -u postgres psql

This runs the psql client as the postgres OS user, and peer authentication lets it straight in. A very common exam (and real-world) trap is running psql -U postgres as your normal kali user and getting a peer authentication failed error — your OS name does not match the role you asked for.

Creating Users and Databases

Inside the psql prompt, SQL statements create roles and databases:

• CREATE USER pentest WITH PASSWORD 'S3cret!'; — a new role with login and password.
• CREATE DATABASE engagedb OWNER pentest; — a database owned by that role.
• \l lists databases, \du lists roles, \c engagedb connects to a database, and \q quits.

Kali Linux Revealed does the same job from the shell rather than from SQL, using the wrapper commands and the postgres OS account:

sudo su - postgres
createuser -P pentest
createdb -T template0 -E UTF-8 -O pentest engagedb
exit

createuser -P prompts for the new role's password, createdb -O sets the owner, and -E UTF-8 sets the encoding (which requires -T template0 as the template). dropuser and dropdb are the counterparts. Every one of these accepts --port=PORT to act on a cluster other than the default one on 5432 — which matters because installing a second PostgreSQL major version creates a second cluster on the next free port, usually 5433. postgresql.service itself is an empty wrapper: each cluster has its own postgresql@<version>-<cluster>.service unit, and Debian's postgresql-common provides pg_createcluster, pg_dropcluster, pg_ctlcluster, pg_lsclusters and pg_upgradecluster to manage them.

A password-authenticated role is useless over TCP until pg_hba.conf permits it. Add or adjust a line such as host engagedb pentest 127.0.0.1/32 scram-sha-256 in /etc/postgresql/<version>/main/pg_hba.conf, then reload with sudo systemctl reload postgresql (a reload suffices for pg_hba.conf changes; no restart needed). After that, psql -h 127.0.0.1 -U pentest engagedb prompts for the password.

Roles, Privileges, and Network Access

PostgreSQL uses the term role for both users and groups; a user is simply a role with the LOGIN privilege. CREATE USER is shorthand for CREATE ROLE ... LOGIN. Grant extra privileges with ALTER USER pentest CREATEDB; or strip them with ALTER USER pentest NOCREATEDB;, and remove objects in dependency order — DROP DATABASE engagedb; before DROP USER pentest; — because PostgreSQL refuses to drop a role that still owns objects.

Remote access requires two coordinated changes, and exam questions frequently describe a client that cannot connect because only one was made. In postgresql.conf set listen_addresses to something other than localhost (or '*'), and in pg_hba.conf add a host line covering the client's address with scram-sha-256. Note the difference in how these apply: a reload picks up pg_hba.conf changes, but listen_addresses requires a full restart, because the server cannot rebind its sockets on the fly. Confirm the listener afterward with ss -tlnp | grep 5432.

PostgreSQL and Metasploit: msfdb

Kali ships the msfdb helper to wire PostgreSQL into Metasploit without manual SQL. Run sudo msfdb init and it starts PostgreSQL, creates the msf database and msf user with a random password, and writes the connection details to /usr/share/metasploit-framework/config/database.yml. Inside msfconsole, db_status confirms the connection, and commands like hosts, services, and vulns then return data persisted across sessions. You can also attach an already-running database manually with db_connect msf:password@127.0.0.1/msf. The companion commands msfdb start, msfdb stop, msfdb status, and msfdb delete manage the service afterward. If a question describes Metasploit forgetting scan results between sessions, or db_status replying that no database is connected, the answer is almost always that PostgreSQL was never initialized with msfdb init — or the postgresql service itself is simply stopped, since Kali disables it by default.