Installing and Configuring PostgreSQL with Open Port Access on Ubuntu
Learn how to install and secure PostgreSQL database server on Ubuntu 22.04 for reliable, scalable data management.
Install PostgreSQL Database Server on Ubuntu 22.04
This section shows you how to install the PostgreSQL database server package, ideal for anyone installing PostgreSQL on Ubuntu 22.04, whether for development or production use.
Step 1: Add the PostgreSQL repository to your server's APT sources. (optional)
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Step 2: Import the PostgreSQL repository key to your server using the wget utility. (optional)
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo tee /etc/apt/trusted.gpg.d/postgresql.asc > /dev/null
Step 3: Update the server packages to synchronize the new PostgreSQL repository.
sudo apt update
Step 4: Install PostgreSQL on your server.
sudo apt install postgresql
Step 5: Start the PostgreSQL database server.
sudo systemctl start postgresql
Step 6: Enable the PostgreSQL database server.
sudo systemctl enable postgresql
Step 7: View the PostgreSQL service status and verify that it's active.
sudo systemctl status postgresql
Use the PostgreSQL Database Command Line Interface (CLI)
The psql client tool creates a connection to the PostgreSQL database server with support for operations such as SQL statement execution and remote database access. Follow the steps below to use the PostgreSQL client tool to connect and access your database server.
Step 1: Log in to the PostgreSQL Database server using the default postgres user.
sudo -u postgres psql
Step 2: Create a new sample database blog.
CREATE DATABASE blog;
Step 3: Modify the default postgres user with a new strong password.
ALTER USER postgres WITH PASSWORD 'strong_password';
Secure the PostgreSQL Database Server with Password Authentication
Follow the steps below to secure the PostgreSQL database server with password authentication to enable only authorized users to access databases.
Step 1: Back up the default pg_hba.conf configuration file for recovery purposes. Replace 16 with your actual database server version.
sudo cp /etc/postgresql/16/main/pg_hba.conf /etc/postgresql/16/main/pg_hba.conf.ORIG
Step 2: Open the pg_hba.conf configuration file using a text editor such as Vim
sudo nano /etc/postgresql/16/main/pg_hba.conf
Step 3: Find the following section within the file.
local all postgres peer # TYPE DATABASE USER ADDRESS METHOD
Step 4: Change the peer value to password to enable password authentication on the PostgreSQL database server.
local all postgres password # TYPE DATABASE USER ADDRESS METHOD
Step 5: Restart the PostgreSQL server to apply the new configuration changes.
sudo systemctl restart postgresql
Open the PostgreSQL port and configure it on Ubuntu
Step 1: Configure PostgreSQL By default, PostgreSQL listens on the localhost (127.0.0.1) and only accepts connections from the local machine. If you want to allow remote connections or change the listening address, you need to modify the configuration file.
Open the PostgreSQL configuration file using a text editor of your choice.
sudo vim /etc/postgresql/16/main/postgresql.conf
In the configuration file, find the line that starts with listen_addresses and uncomment it by removing the leading '#' character. Then, set the IP address you want PostgreSQL to listen on. To listen to all available IP addresses, you can use the '*'.
listen_addresses = '*'
Step 2: Now, Need to adjust your firewall settings. If you are using UFW (Uncomplicated Firewall), you can modify the settings using the following command:
sudo ufw allow 5432
If you are using a different firewall management tool or have custom firewall rules, make sure to allow incoming connections on the PostgreSQL port (5432).
Step 3: Configure PostgreSQL Access Control By default, PostgreSQL uses a file called pg_hba.conf to manage access control for client connections. Open the file using a text editor:
sudo vim /etc/postgresql/16/main/pg_hba.conf
In the pg_hba.conf file, you'll find a section with a list of entries defining client authentication rules. By default, remote connections are disabled.
To allow remote connections, add the following line at the end of the file:
host all all 127.0.0.1/32 scram-sha-256
Replace 127.0.0.1 with the IP address or range of IP addresses from which you want to allow connections. For example, to allow connections from any IP address, you can use 0.0.0.0/0. The scram-sha-256 authentication method is used here, which requires a password for authentication. You can use different authentication methods based on your needs.
Save the changes and exit the text editor.
Step 4: Restart PostgreSQL To apply the changes you made to the PostgreSQL configuration, restart the PostgreSQL service using the following command:
sudo service postgresql restart