Setting Up and Connecting to PostgreSQL on a VPS

This guide provides a comprehensive process for setting up a PostgreSQL database on a Virtual Private Server (VPS) and remotely connecting to it using pgAdmin. It is specifically tailored for Ubuntu 22.04 LTS (Jammy Jellyfish) or other Debian-based systems that use the apt package manager. Commands and configurations provided in this guide may differ for other Linux distributions.

The tutorial covers updating the system, configuring PostgreSQL settings, securing connections, and testing the setup to ensure a reliable and secure database environment.

Step 1: update the system

Begin by updating the system. Open your terminal and run the following commands to update the package lists and upgrade installed packages:

“`bash title="Update and upgrade Ubuntu packages" sudo apt update && sudo apt upgrade

## Step 2: install PostgreSQL

Install PostgreSQL by entering:

```bash title="Install PostgreSQL from APT"
sudo apt install postgresql

Ensure you have the latest version by running:

“`bash title="Refresh the APT package index" sudo apt update

Next, modify the `postgresql.conf` file to allow connections from external addresses. Use the command:

```bash title="Open the PostgreSQL server configuration"
sudo nano /etc/postgresql/*/main/postgresql.conf

Locate the section titled Connections and Authentication and find the line containing listen_addresses. Change this line as follows:

“`conf title="Listen for PostgreSQL connections on all addresses" listen_addresses = '*'

This change allows the server to accept connections from any address. Save your changes and exit the editor.

![PostgreSQL connection settings displayed in postgresql.conf within Nano](/wp-content/themes/virtarix/assets/images/blog/posts/997/Step-2_2-1.webp)

## Step 3: log in as the PostgreSQL user

Log in as the PostgreSQL with the command:

```bash title="Open psql as the postgres system user"
sudo -u postgres psql

Once logged in, update the PostgreSQL user with a new secure password by executing:

“`sql title="Set the postgres account password" ALTER USER postgres WITH ENCRYPTED PASSWORD 'yourpassword';

If no error appears, the password update was successful. Exit the PostgreSQL shell by typing:

```psql title="Exit the psql shell"
\q

Step 4: edit the pg_hba.conf file

Edit the pg_hba.conf file to define the authentication methods for remote connections. Use the command

“`bash title="Open the PostgreSQL client authentication rules" sudo nano /etc/postgresql/*/main/pg_hba.conf

![Client authentication rules displayed in pg_hba.conf within Nano](/wp-content/themes/virtarix/assets/images/blog/posts/997/Step-4.webp)

Add the following entries only in an isolated test environment. They expose PostgreSQL authentication to every IPv4 and IPv6 address. For production, replace both networks with trusted client ranges, require `hostssl`, use a firewall or private network, verify certificate handling, and test access before removing any existing rule.

```conf title="Allow remote PostgreSQL clients from all addresses"
## Allow remote connections
host    all             all             0.0.0.0/0            scram-sha-256
hostssl all             all             ::/0                 scram-sha-256

Step 5: restart PostgreSQL service

Restart the PostgreSQL service to apply the changes:

“`bash title="Restart the PostgreSQL service" sudo systemctl restart postgresql.service

## Step 6: configure the firewall

Allow PostgreSQL-related traffic through the VPS firewall. Run the following command:

```bash title="Allow PostgreSQL through UFW"
sudo ufw allow 5432

Ensure unauthorized IP addresses are blocked and only trusted ones are allowed.

Step 7: test the connection

Install the PostgreSQL client if it's not already installed:

“`bash title="Install the PostgreSQL command-line client" sudo apt install postgresql-client

![Terminal installing the PostgreSQL client after restarting the service](/wp-content/themes/virtarix/assets/images/blog/posts/997/Step-5-1.webp)

To test the connection, run:

```bash title="Connect to a remote PostgreSQL server with psql"
psql --host YOUR_IP_ADDRESS --username postgres --password

Replace YOUR_IP_ADDRESS with the IP address of your VPS. Press Enter and provide the password when prompted. If successful, you will access your PostgreSQL database.

Step 8: connect via pgAdmin4

To connect using pgAdmin4, follow these steps:

1. Open pgAdmin4 and navigate to Object ➔ Register ➔ Server.
2. Enter a name for your server in the "General" tab.
3. In the "Connection" tab, input your VPS’s IP address, username, and password.

Click Save.

You should now be connected to your PostgreSQL database via pgAdmin4. Use the pgAdmin4 interface to manage and create necessary databases.

Additional notes

• For production environments, secure your server by restricting IP addresses in the pg_hba.conf file and using a firewall.
• Regularly update your system and database packages to maintain security.

Enjoy managing your PostgreSQL database securely and efficiently!

Frequently asked questions

How do I restrict access to specific IP addresses for improved security?

You can restrict access by modifying the pg_hba.conf file. Replace the 0.0.0.0/0 entry with a specific IP or range. For example:

“`conf title="Allow PostgreSQL clients from one IPv4 subnet" host all all 192.168.1.0/24 scram-sha-256

This limits connections to devices within the `192.168.1.0/24` subnet. Be sure to restart the PostgreSQL service after making changes.

### Why can’t I connect to PostgreSQL from pgAdmin4 even after completing all steps?

Common issues include:

#### Firewall settings

Ensure port `5432` is open using:

```bash title="Allow PostgreSQL through UFW"
sudo ufw allow 5432

Incorrect configuration

Verify that listen_addresses is set to * in postgresql.conf and the appropriate entries exist in pg_hba.conf.

Network restrictions

Check if your VPS provider has additional network security rules blocking the connection.

What should I do if I forget the PostgreSQL password?

You can reset the password by logging in as the postgres system user on your VPS and running the following commands:

“`bash title="Attempt a postgres password reset from the shell" sudo -u postgres psql ALTER USER postgres WITH ENCRYPTED PASSWORD 'newpassword';

Replace `newpassword` with a strong, unique password. Exit with `\q` and restart the PostgreSQL service to apply changes.