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 :ref:`private_ip`, along with recommendations how to secure it further using firewalls. Installing PostgreSQL --------------------- Install postgresql: .. code-block:: shell 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: .. code-block:: shell :caption: Show ports postgresql is listening on. 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: .. code-block:: shell :caption: 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: .. code-block:: shell :caption: Confirm what user we currently are. postgres@11test1-postgres:/root$ whoami postgres postgres@11test1-postgres:/root$ Then connect using the ``psql`` command: .. code-block:: shell :caption: Connect to postgres 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 :ref:`private_ip` 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: .. code-block:: shell :caption: Default postgresql.conf #------------------------------------------------------------------------------ # 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 :ref:`private_ip` of ``10.11.11.63``, you would update the file like so: .. code-block:: shell :caption: Modified postgresql.conf to listen on 10.11.11.63 #------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '10.11.11.63' Restart the server to make the new setting take effect: .. code-block:: shell service postgresql restart Confirm it is listening on the address(es) with the ``ss`` command: .. code-block:: shell :caption: Show ports postgresql is listening on. 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: .. code-block:: shell :caption: Default pg_hba.conf host lines # 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 :ref:`private_ip` ``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: .. code-block:: shell :caption: Modified pg_hba.conf to allow connections from 10.11.11.63 # 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: .. code-block:: shell 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``: .. code-block:: shell :caption: Use nmap to confirm port 5432 is open to us. 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: .. code-block:: shell :caption: Allow connections to port 5432 from 10.11.11.15 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: .. code-block:: shell :caption: Deny all other traffic destined for 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: .. code-block:: shell 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: .. code-block:: shell :caption: Save all iptables rules in their current state. iptables-save > /etc/iptables/rules.v4 Upon reboot, the system will run any rules in ``/etc/iptables/rules.v4``.