PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS) known for its stability, extensibility, and extensive feature set. In this tutorial, I'll show you step-by-step how to install PostgreSQL on Ubuntu Server 22.04 and secure it with the pre-installed firewall.
Some of the main features of PostgreSQL are:
1. Extensibility: You can create functions and data types that meet your specific needs.
2. Transaction support: PostgreSQL offers the ability to perform complex transactions securely and efficiently.
3. Data integrity: The DBMS provides mechanisms to enforce data integrity, including constraints and triggers.
4. Replication: PostgreSQL allows you to set up replication for high availability and fault tolerance.
5. Extended data types: It supports geographic and JSON data types, making it ideal for geographic applications and JSON-based APIs.
Step 1: Update the package manager
Before installing PostgreSQL, it's important to update the package manager to ensure we have the latest package information and any security updates. To do this, open your terminal and enter the following command:
sudo apt update && sudo apt upgrade -y
Step 2: PostgreSQL Installation
Now that your system is up to date, we can install PostgreSQL. Use the following command to install PostgreSQL and its associated accessories:
sudo apt install postgresql postgresql-contrib
During installation, PostgreSQL is automatically started as a service and the user “postgres” is created.
Step 3: Initializing the PostgreSQL database
After installation, we need to initialize the PostgreSQL database. To do so, use the following command:
sudo pg_createcluster 13 main --start
Make sure you replace version “13” with the actual PostgreSQL version you have installed.
Step 4: Access the PostgreSQL database
Now we can access the PostgreSQL database and connect as user “postgres”:
sudo -i -u postgres
You can then open the PostgreSQL command line with the command psql open:
psql
Step 5: Secure access to PostgreSQL
By default, PostgreSQL only allows local connections. If you want to use PostgreSQL from another computer, you'll need to edit the configuration file. Open the file /etc/postgresql/13/main/pg_hba.conf with a text editor of your choice:
sudo nano /etc/postgresql/13/main/pg_hba.conf
Change the lines for the desired access, e.g. from local on host:
# TYPE DATABASE USER ADDRESS METHOD host all all 0.0.0.0/0 md5
This allows connections from any IP address, provided a valid password is provided. Make sure you configure the access permissions according to your needs.
Step 6: Firewall configuration
Finally, you should configure the pre-installed firewall to control access to PostgreSQL. By default, the Ubuntu firewall blocks incoming connections. To open PostgreSQL port 5432, run this command:
sudo ufw allow 5432/tcp
Conclusion
Congratulations! You've successfully installed PostgreSQL on your Ubuntu Server 22.04 and enhanced firewall security. You can now use your PostgreSQL database to efficiently store and retrieve data. Remember to adjust your firewall rules according to your security needs and perform regular backups to protect your data.
EN 
DE 
FR 
PT 
ES