How to Install and Secure PostgreSQL on Rocky Linux 10: A Comprehensive Guide
PostgreSQL is a powerful, open-source object-relational database system renowned for its reliability, feature robustness, and performance. For developers and system administrators using Rocky Linux 10, it represents a stable and scalable foundation for a wide range of applications.
This guide provides a clear, step-by-step process for installing, securing, and configuring PostgreSQL on a fresh Rocky Linux 10 system.
Prerequisites
Before you begin, ensure you have the following:
- A running instance of Rocky Linux 10.
- Access to a user account with
sudoor root privileges.
Step 1: Update Your System’s Packages
First, it’s crucial to ensure your system is fully up to date. This prevents potential package conflicts and applies the latest security patches. Open your terminal and run the following command:
sudo dnf update -y
This command will download and install any available updates for your system’s packages.
Step 2: Install the PostgreSQL Server
Rocky Linux 10 includes PostgreSQL in its default AppStream repository, making the installation process straightforward. You don’t need to add any third-party repositories.
To install the PostgreSQL server package, along with its dependencies, execute the command below:
sudo dnf install postgresql-server -y
This command installs the core database server, client libraries, and essential command-line tools needed to manage your database.
Step 3: Initialize the Database Cluster
After the installation is complete, you must initialize the PostgreSQL database cluster. This one-time setup process creates the necessary data directory (/var/lib/pgsql/data), generates default configuration files, and sets up the initial postgres database.
Use the provided setup script to perform this initialization:
sudo postgresql-setup --initdb
Upon successful execution, you will see a confirmation message: Initializing database... OK.
Step 4: Start and Enable the PostgreSQL Service
With the database cluster initialized, the next step is to start the PostgreSQL service and enable it to launch automatically on system boot.
To start the service immediately, run:
sudo systemctl start postgresql
To enable the service to start on boot, run:
sudo systemctl enable postgresql
You can verify that the service is running correctly by checking its status:
sudo systemctl status postgresql
A successful status will show active (running) in the output.
Step 5: Secure the Default PostgreSQL User
By default, the installation creates a system user named postgres to manage the database. For security, it’s essential to set a strong password for this default database user.
First, switch to the postgres user account:
sudo -i -u postgres
Next, access the PostgreSQL command-line interface by typing:
psql
You are now inside the PostgreSQL prompt. Use the following command to set a password for the postgres user. Replace YourStrongPassword with a secure password of your choice.
\password postgres
You will be prompted to enter and confirm the new password. Once set, you can exit the psql prompt and return to your regular user shell:
\q
exit
This is a critical security step that ensures your primary database administrator account is protected.
(Optional) Step 6: Configure Remote Access
By default, PostgreSQL only allows connections from the local machine (localhost). If your application or database management tool needs to connect from another server, you must enable remote access.
This involves two key actions: adjusting the firewall and modifying PostgreSQL’s configuration files.
A. Adjusting Firewall Rules
The default firewall on Rocky Linux will block incoming connections to the standard PostgreSQL port, which is 5432. You need to create a rule to allow traffic on this port.
sudo firewall-cmd --add-port=5432/tcp --permanent
sudo firewall-cmd --reload
The first command adds a permanent rule to allow TCP traffic on port 5432, and the second command reloads the firewall to apply the change.
B. Editing the Configuration Files
You need to edit two configuration files to allow external connections:
postgresql.conf: This file controls the server’s general settings.pg_hba.conf: This file controls authentication and defines which hosts can connect.
First, open postgresql.conf with a text editor like nano or vi:
sudo nano /var/lib/pgsql/data/postgresql.conf
Find the line that starts with #listen_addresses = 'localhost'. Uncomment it (remove the #) and change 'localhost' to '*' to allow PostgreSQL to listen on all available network interfaces.
# Before
#listen_addresses = 'localhost'
# After
listen_addresses = '*'
Save and close the file.
Next, open the host-based authentication file, pg_hba.conf:
sudo nano /var/lib/pgsql/data/pg_hba.conf
Scroll to the bottom of the file. You need to add a line that specifies which IP addresses are allowed to connect. To allow connections from any IP address using an MD5-encrypted password (which is secure), add the following line:
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 md5
Security Tip: For better security in a production environment, replace 0.0.0.0/0 with a specific IP address or a CIDR range (e.g., 192.168.1.0/24) to restrict access to trusted networks only.
Save and close the file. Finally, you must restart the PostgreSQL service for these changes to take effect:
sudo systemctl restart postgresql
Conclusion
You have successfully installed, secured, and configured a PostgreSQL database server on your Rocky Linux 10 system. You now have a robust database ready for your development projects or production applications. From here, your next steps will be to create specific databases, roles, and users tailored to your application’s needs.
Source: https://centlinux.com/install-postgresql-on-rocky-linux-10/
