Install PostgreSQL on Debian 13
This document will cover how to install PostgreSQL on Debian and derivatives. First, we will cover how to do a simple install and connect. Then we will cover reconfiguring the server to listen on a Private IPv4, along with recommendations how to secure it further using firewalls.
Installing PostgreSQL
Install postgresql:
apt install postgresql
Once complete, postgres should be listening on localhost port 5432 (127.0.0.1:5432),
which you can verify with the ss command:
root@11test1-postgres:~# ss -tlnp |grep postgres
LISTEN 0 244 127.0.0.1:5432 0.0.0.0:* users:(("postgres",pid=2374,fd=6))
LISTEN 0 244 [::1]:5432 [::]:* users:(("postgres",pid=2374,fd=5))
root@11test1-postgres:~#
Connecting to the Server
You can connect to the server with the psql command. This command will not work when
run as root. You need to use su to become the postgres user:
su postgres
Your command prompt should change, and you can confirm you are now the postgres user with
the whoami command:
postgres@11test1-postgres:/root$ whoami
postgres
postgres@11test1-postgres:/root$
Then connect using the psql command:
postgres@11test1-postgres:/root$ psql
psql (15.10 (Debian 15.10-0+deb12u1))
Type "help" for help.
postgres=# exit
postgres@11test1-postgres:/root$
If your application lives on the same server and only needs to communicate with the database on localhost (127.0.0.1), you are all set! Otherwise if this installation needs to be accessed from other hosts, read on.
Changing the IP Address and Port
A common scenario is to have a dedicated database server, which is accessed by outside clients
using Private IPv4 to restrict connections to the local private network. The first step is
to set the listen_addresses parameter in /etc/postgresql/15/main/postgresql.conf. The
listen_addresses parameter is commented out by default, about 50 lines into the file:
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
If you have a Private IPv4 of 10.11.11.63, you would update the file like so:
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '10.11.11.63'
Restart the server to make the new setting take effect:
service postgresql restart
Confirm it is listening on the address(es) with the ss command:
root@11test1-postgres:/var/log/postgresql# ss -tlnp |grep postgres
LISTEN 0 244 10.11.11.63:5432 0.0.0.0:* users:(("postgres",pid=966,fd=5))
root@11test1-postgres:/var/log/postgresql#
Allowing non-local Connections
PostgreSQL requires host lines specifying which client hosts are able to connect. These
are located at the end of the file /etc/postgresql/15/main/pg_hba.conf. Here is the
default entry, at the end of the file:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
If you have a web server at Private IPv4 10.11.11.15 that needs to make queries,
you would add an additional line in the “IPv4 local connections” section at the bottom of
pg_hba.conf, like so:
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
# Allow connections from 10.11.11.15
host all all 10.11.11.15/32 md5
Hint
Note that we used “all” for the DATABASE and USER columns. Once things are working, you are encouraged to further harden security by specifying exact DATABASE and USER values as well.
Restart the server to make the new settings take effect:
service postgresql restart
Firewalls
Note
This section is not necessary for things to work, but strongly recommended. The method described below can be applied to any service; just use different port numbers.
The host lines described in the previous section help security. But the server is still open on
port 5432 at the network layer, allowing for brute-force attacks. We can confirm this easily
from an outside host, by using the nmap command against our server 10.11.11.63:
root@11test1-3247:~# nmap -p 5432 10.11.11.63
Starting Nmap 7.93 ( https://nmap.org ) at 2025-01-05 20:52 UTC
Nmap scan report for 10.11.11.63
Host is up (0.00073s latency).
PORT STATE SERVICE
5432/tcp open postgresql
MAC Address: 52:54:00:7B:5C:F6 (QEMU virtual NIC)
Nmap done: 1 IP address (1 host up) scanned in 0.21 seconds
You can use ufw or any other tool to firewall that port. Below, we describe how to do it
directly with the iptables command.
First, using the example from the previous section we will allow client connections from
10.11.11.15. We do this with the following iptables command:
iptables -A INPUT -j ACCEPT -p tcp --dport 5432 -s 10.11.11.15/32
In the above command, we allow (ACCEPT) from -s (source) 10.11.11.15/32.
We then deny (DROP) all other connections to port 5432:
iptables -D INPUT -j DROP -p tcp --dport 5432
You should be able to run nmap as described above to confirm the port is only open to
client IP addresses you want to allow.
Saving Firewall Rules
Once the firewall rules are confirmed working, we will need them to persist over reboots.
This can be done using the iptables-persistent package:
apt install iptables-persistent
…apt will prompt to save the existing rules. If you make changes to your rules later, be sure to save your changes using the following command:
iptables-save > /etc/iptables/rules.v4
Upon reboot, the system will run any rules in /etc/iptables/rules.v4.