How to Install and Secure PostgreSQL 18 on Ubuntu 24.04 LTS: A Step-by-Step Guide
PostgreSQL is a powerhouse in the world of open-source relational databases, celebrated for its robustness, feature-rich ecosystem, and unwavering commitment to standards compliance. For developers and system administrators running Ubuntu 24.04 LTS, installing the latest version, PostgreSQL 18, provides access to cutting-edge performance improvements and new features.
This comprehensive guide will walk you through the entire process, from installation to essential security configurations, ensuring your database server is production-ready.
Prerequisites
Before we begin, ensure you have the following:
- An instance of Ubuntu 24.04 LTS.
- Access to a user account with sudo or root privileges.
Step 1: Add the Official PostgreSQL Repository
While Ubuntu’s default repositories include PostgreSQL, they often lag behind the latest stable release. To get version 18, we must add the official PostgreSQL APT repository, which guarantees access to the most up-to-date packages.
First, import the PostgreSQL repository signing key:
sudo install -d /usr/share/postgresql-common/pgdg-key.d
sudo curl -o /usr/share/postgresql-common/pgdg-key.d/pgdg-key.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
Next, create the repository configuration file. This command automatically detects your Ubuntu version and sets up the correct source list.
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg-key.d/pgdg-key.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Finally, update your server’s package list to include the newly added repository:
sudo apt update
Step 2: Install PostgreSQL 18
With the repository configured, installing PostgreSQL is now straightforward. The following command installs the server package along with postgresql-contrib, which provides useful additional modules and utilities.
sudo apt install postgresql-18 postgresql-contrib-18 -y
Once the installation completes, the PostgreSQL service will automatically start. You can verify its status to ensure it’s running correctly:
sudo systemctl status postgresql
You should see an “active (running)” status, confirming that your database server is operational.
Step 3: Secure the Default postgres User
By default, PostgreSQL creates a superuser named postgres to manage the database server. This user is initially configured for peer authentication, meaning it can only be accessed by the corresponding postgres system user without a password.
For security, it is critical to set a strong password for this superuser.
First, switch to the postgres system user:
sudo -i -u postgres
Now, open the PostgreSQL command-line interface, psql:
psql
From within the psql prompt, set a password for the postgres user. Replace YourStrongPassword with a secure password of your choice.
\password postgres
After setting the password, you can exit the psql shell by typing \q and return to your regular user shell by typing exit.
Step 4: Create a New User and Database
Using the postgres superuser for application connections is a significant security risk. The best practice is to create a dedicated, non-superuser role for each application or user.
From your standard user shell (with sudo privileges), you can use the createuser utility. This command creates a new user and prompts you to set a password for them.
sudo -u postgres createuser --interactive --pwprompt
Enter the name for the new role (e.g., myapp_user) and provide a strong password when prompted.
Next, create a database that will be owned by this new user. This is a common and secure pattern for isolating application data.
sudo -u postgres createdatabase myapp_db -O myapp_user
Your new user myapp_user is now the owner of the myapp_db database, granting them full permissions over it.
Step 5: Allowing Remote Connections (Optional)
By default, PostgreSQL only listens for connections from the local machine (localhost). To enable remote connections, you need to make two critical changes.
Update
postgresql.confto Listen on All Interfaces:
Open the main configuration file with a text editor likenano:sudo nano /etc/postgresql/18/main/postgresql.confFind the
listen_addressesline. Uncomment it (remove the#) and change its value from'localhost'to'*':listen_addresses = '*'This tells PostgreSQL to accept connections from any IP address. Save and close the file.
Configure
pg_hba.conffor Authentication:
Next, you must specify which users can connect from which IP addresses and how they should authenticate. Open the host-based authentication file:sudo nano /etc/postgresql/18/main/pg_hba.confAdd the following line to the end of the file to allow your new user to connect from any IP address using a password (
scram-sha-256is a secure modern standard):# TYPE DATABASE USER ADDRESS METHOD host myapp_db myapp_user 0.0.0.0/0 scram-sha-256Save and close the file.
Restart PostgreSQL and Configure Firewall:
For these changes to take effect, you must restart the PostgreSQL service:
bash
sudo systemctl restart postgresql
Finally, if you are using UFW (Uncomplicated Firewall), you must allow traffic on PostgreSQL’s default port, 5432:
bash
sudo ufw allow 5432/tcp
You now have a fully functional, secure, and modern PostgreSQL 18 database server running on your Ubuntu 24.04 LTS system, ready to power your applications.
Source: https://www.tecmint.com/install-postgresql-on-ubuntu/
