How to Install PostgreSQL 17 on Ubuntu 24.04 and Allow Remote Access

Add the official PostgreSQL repository to an Ubuntu 24.04 server and install PostgreSQL 17, create a user and a database for remote access, and then configure remote access and the firewall so that only your PC can connect.

1. Requirements

• One Ubuntu 24.04 LTS server (1 GB of memory or more recommended)
• SSH access with an account that has sudo privileges
• The public IP address of the PC you will connect from (e.g. 203.0.113.10)

If you don't have a server, create the smallest DigitalOcean Droplet (1 vCPU · 1 GB). This Droplet is enough to install PostgreSQL 17. Every step in this guide was verified on a server created as a Droplet (1 vCPU · 1 GB memory · 25 GB SSD · Ubuntu 24.04 · Singapore).

2. Add the official PostgreSQL repository

The default Ubuntu 24.04 repositories include only PostgreSQL 16. To install PostgreSQL 17, add the official PostgreSQL repository (apt.postgresql.org). First, install the postgresql-common package, which contains the repository setup script.

sudo apt update
sudo apt install -y postgresql-common

Run the repository setup script. The script registers the repository signing key and the repository address, and then updates the package list. The -y option means that the script continues without the "Press Enter to continue" confirmation prompt.

sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y

This script will enable the PostgreSQL APT repository on apt.postgresql.org on
your system. The distribution codename used will be noble-pgdg.

Using keyring /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
Writing /etc/apt/sources.list.d/pgdg.sources ...

Running apt-get update ...
Get:1 https://apt.postgresql.org/pub/repos/apt noble-pgdg InRelease [189 kB]
Get:3 https://apt.postgresql.org/pub/repos/apt noble-pgdg/main amd64 Packages [897 kB]

You can now start installing packages from apt.postgresql.org.

Check the repository settings that were added.

cat /etc/apt/sources.list.d/pgdg.sources

Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: noble-pgdg
Components: main
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg

3. Install PostgreSQL 17

The repository setup script has already updated the package list, so you can install right away. Add the version (17) to the package name so that PostgreSQL 17 is installed.

sudo apt install -y postgresql-17

When the installation finishes, check the installed version. pgdg24.04 after the version means that the package is for Ubuntu 24.04 and was downloaded from the official repository.

psql --version

psql (PostgreSQL) 17.11 (Ubuntu 17.11-1.pgdg24.04+2)

4. Check the service status

When the installation finishes, the default cluster 17/main is created automatically and runs on port 5432.

pg_lsclusters

Ver Cluster Port Status Owner    Data directory              Log file
17  main    5432 online postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log

sudo systemctl status postgresql@17-main --no-pager

● postgresql@17-main.service - PostgreSQL Cluster 17-main
     Loaded: loaded (/usr/lib/systemd/system/postgresql@.service; enabled-runtime; preset: enabled)
     Active: active (running) since Fri 2026-10-09 11:46:32 UTC; 12s ago

Now check that you can connect to PostgreSQL. Right after installation, you can connect only as the Linux account postgres.

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

                                                                version
---------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.11 (Ubuntu 17.11-1.pgdg24.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

5. Check the default security settings

Before you allow remote connections, check the defaults for how passwords are stored and which connections are allowed.

sudo -u postgres psql -c "SHOW password_encryption;"

 password_encryption
---------------------
 scram-sha-256
(1 row)

scram-sha-256 stores a hash instead of the password itself, and does not send the password as is when you connect. The default is already scram-sha-256, so you do not need to change it.

sudo grep -v -E '^#|^$' /etc/postgresql/17/main/pg_hba.conf

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
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

pg_hba.conf is the file that sets who can connect from where. The local lines are for socket connections on the server and use peer authentication, which requires the Linux account name to match the database user name. The host lines are for TCP connections, and for now they allow password connections only from the server's own addresses (127.0.0.1, ::1). The admin account postgres has no password and can connect only through peer, so you cannot connect remotely as postgres. Keep it this way, and allow remote connections only for the dedicated user you create below.

6. Create a user and a database

It is safer for applications to connect as a dedicated user instead of the admin account postgres. Create the user appuser and the database appdb owned by this user. Be sure to replace the password with a value of your own.

sudo -u postgres psql -c "CREATE ROLE appuser WITH LOGIN PASSWORD 'change-this-password';"
sudo -u postgres psql -c "CREATE DATABASE appdb OWNER appuser;"

CREATE ROLE
CREATE DATABASE

Check that the user was created.

sudo -u postgres psql -c "\du"

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 appuser   |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

To connect with a password, you must add -h localhost. This connects over TCP and checks the password.

psql -h localhost -U appuser -d appdb -c "\conninfo"

Password for user appuser:
You are connected to database "appdb" as user "appuser" on host "localhost" (address "::1") at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)

7. Allow remote connections

The default settings allow only localhost connections. To allow remote connections, you need to edit two files.

First, change listen_addresses in postgresql.conf so that the server also accepts connections on external addresses. '*' means that PostgreSQL accepts connections on all of the server's addresses.

sudo sed -i "s/^#listen_addresses = 'localhost'/listen_addresses = '*'/" /etc/postgresql/17/main/postgresql.conf
grep -n '^listen_addresses' /etc/postgresql/17/main/postgresql.conf

60:listen_addresses = '*'		# what IP address(es) to listen on;

Next, add one line to pg_hba.conf with the IP, user and database to allow. Replace 203.0.113.10 with the public IP of the PC you will connect from.

echo 'host    appdb    appuser    203.0.113.10/32    scram-sha-256' | sudo tee -a /etc/postgresql/17/main/pg_hba.conf
sudo systemctl restart postgresql

host    appdb    appuser    203.0.113.10/32    scram-sha-256

Check that port 5432 is now open on external addresses as well. If you see 0.0.0.0:5432, it is working.

sudo ss -tlnp | grep 5432

LISTEN 0      200          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=6081,fd=6))
LISTEN 0      200             [::]:5432         [::]:*    users:(("postgres",pid=6081,fd=7))

8. Configure the firewall (ufw)

Separately from the PostgreSQL settings, allow only your PC's IP to reach port 5432 in the firewall as well. Always allow SSH before you enable the firewall. If you enable the firewall without allowing SSH, your connection to the server is cut off. --force is an option that enables the firewall without a confirmation prompt.

sudo ufw allow OpenSSH
sudo ufw allow from 203.0.113.10 to any port 5432 proto tcp
sudo ufw --force enable
sudo ufw status

Status: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
5432/tcp                   ALLOW       203.0.113.10
OpenSSH (v6)               ALLOW       Anywhere (v6)

9. Check the connection from your PC

If psql isn't installed on your PC, you can run the PostgreSQL client with Docker and connect. Replace 203.0.113.20 with the server's IP. Because the --rm option is added, the container is deleted automatically when you end the session. -it is an option that attaches a terminal so that you can enter the password.

docker run --rm -it postgres:17-alpine psql -h 203.0.113.20 -U appuser -d appdb -c "\conninfo"

Password for user appuser:
You are connected to database "appdb" as user "appuser" on host "203.0.113.20" at port "5432".
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)

SSL connection (protocol: TLSv1.3 …) in the output means that the connection is encrypted. The PostgreSQL packages from the official repository also turn on SSL by default with a self-signed certificate at install time.

10. Troubleshooting

11. Uninstall

To delete PostgreSQL and all of its data, run the commands below. Because the database files are deleted as well, back up any data you need with pg_dump first.

sudo DEBIAN_FRONTEND=noninteractive apt purge -y postgresql-17 postgresql-client-17 postgresql-common postgresql-client-common
sudo apt autoremove -y
sudo rm -rf /var/lib/postgresql /etc/postgresql

DEBIAN_FRONTEND=noninteractive is an option that uses the default answers instead of showing configuration prompts while the packages are removed. Without this option, the prompt Remove PostgreSQL directories when package is purged? is displayed. The data directories are deleted by rm -rf on the next line.

Also delete the official repository you added in step 2, and update the package list.

sudo rm /etc/apt/sources.list.d/pgdg.sources
sudo apt update

Also delete the port 5432 rule you added to the firewall.

sudo ufw delete allow from 203.0.113.10 to any port 5432 proto tcp

Rule deleted

Check that no PostgreSQL packages remain. If 0 is displayed, everything has been deleted.

dpkg -l | grep -c postgresql

0