Securely Connecting to PostgreSQL on a Virtual Machine from Anywhere
Published: 2024-04-22 11:21:47

**Prerequisites:**

- A virtual machine set up on a cloud provider (AWS, Google Cloud, Azure) or a local virtualization platform (VirtualBox).
- Basic understanding of command line and Linux concepts.

Deploying PostgreSQL on a Virtual Machine (VM) and connecting to it from anywhere involves several steps.  This guide walks you through deploying PostgreSQL on a Virtual Machine (VM) and connecting to it remotely with enhanced security practices.

Once your VM is running, you'll need to install PostgreSQL. This process varies depending on the operating system of your VM, but it generally involves downloading and installing the PostgreSQL package.

 **1. Install and Configure PostgreSQL**

**1.1. Update and Install:**

```bash
sudo apt update
sudo apt install postgresql postgresql-contrib -y
```

**1.2. Verify PostgreSQL user:**

PostgreSQL creates a user named `postgres`. Verify its existence:

```bash
sudo cat /etc/passwd | grep -i postgres
postgres:x:113:120:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
```

To connect to Postgres, switch to the `postgres` user and run psql:

```bash
sudo -i -u postgres

```

```bash
psql
postgres=#
```

**1.3. Create a Secure User (Optional):**

For better security, create a dedicated user for remote access instead of using the default `postgres` user. We'll create a user named ubuntu and grant them necessary privileges based on your specific needs. Avoid granting superuser access (equivalent to `root` in Linux) unless absolutely essential.

To create a Postgres user run the following command which will give an interactive prompt for configuring the new user. For the sake of simplicity, the ubuntu user will be a superuser, which is the equivalent of being a root user on linux. The super user will have the ability to create/delete/modify databases and users.

```bash
createuser --interactive
Enter name of role to add: ubuntu
Shall the new role be a superuser? (y/n) y

# Set password for myuser (replace 'password' with a strong password)
ALTER USER ubuntu PASSWORD 'password';

# Grant appropriate privileges (adjust as needed)
GRANT CONNECT ON DATABASE postgres TO ubuntu;
GRANT CREATE DATABASE TO ubuntu;

\q
exit
```

Login to postgres using the `postgres` user for now to verify the new `ubuntu` user was created successfully

```bash
psql
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 ubuntu    | Superuser, Create role, Create DB                          | {}

postgres=#
```

Exit out of the psql by running `\q` and also exit out of the `postgres` user by running `exit` on the command line

Let's try to run `psql` as the ubuntu user now. An error similar to the one below should be observed

```bash
$ psql
psql: error: could not connect to server: FATAL:  database "ubuntu" does not exist
```

The reason for this is that Postgres by default tries to connect to a database that is the same name as the user. Since the user is `ubuntu` it tries to connect to a database called `ubuntu` as well which does not exist. We can go in and create a database called `ubuntu` so that it will automatically connect, however I find this unnecessary. Instead we can pass in the `-d` flag and connect to a database that we know exists like the `postgres`

```bash
psql -d postgres
```

With the above steps we have successfully installed and configured Postgres on the server.

1.  Configure PostgreSQL to accept connections from any location. 

By default PostgreSQL is configured to be bound to “localhost”, on port 5432 . It means any attempt to connect to the postgresql server from outside the machine will be refused.

```bash
netstat -nlt
Proto Recv-Q Send-Q Local Address           Foreign Address         State

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
```

 To allow your postgres server to accept connection from any location or a specific location (IP address), you will need to edit two files:

i.`pg_hba.conf`

**ii.`postgresql.conf`**

You can use the following command to easily locate the files on your server

```bash
sudo -u postgres psql -c "SHOW hba_file" -c "SHOW config_file"
```

### `pg_hba.conf`

From [20.1. The pg_hba.conf File](https://www.postgresql.org/docs/11/auth-pg-hba-conf.html): "_Client authentication is controlled by a configuration file, which traditionally is named pg_hba.conf and is stored in the database cluster's data directory. (HBA stands for host-based authentication.) _"

```bash
host    all   all   0.0.0.0/0   trust
host    all   all   ::/0        trust

```

### `postgresql.conf`

The configuration file comes with helpful hints to get this working

To allow your Postgres server to accept connection from any location or a specific location (IP address), change the listen_addresses from ‘localhost’ to the IP address of the server you want to connect from. To allow for connection from anywhere( not advised), change ‘localhost” to “*”.

Remember, exposing your database to the internet comes with security risks. Always ensure you have strong, unique passwords and consider setting up additional security measures.

```sql
listen_addresses = 'localhost'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

```

For a quick and dirty solution just change it to

```sql
listen_addresses = '*'

```

### Restart postgres

After the configuration, you will need to restart the postgres server. Once PostgreSQL is restarted, it will start listening on all IP addresses. 

To restart PostgreSQL:

```bash
$ sudo systemctl restart postgresql@11-main # change 11 to the version of your postgres

# or

$ pg_ctl restart
```

1. **Open Postgres port on server firewall rule**

Remember to open the port that PostgreSQL uses (default is 5432) on your VM's firewall. This step will depend on your VM's operating system and firewall settings.

Finally, you can connect to your PostgreSQL database from anywhere using a PostgreSQL client. You'll need your VM's public IP address, the database name, and the login credentials.

1. **Secure Remote Connection with SSH Tunneling**

Another option to securely connect to postgres server is by using SSH Tunneling. To enable secure remote access, we'll leverage SSH Tunneling. This creates an encrypted tunnel between your local machine and the VM, allowing you to connect to PostgreSQL through this secure channel.

**4.1. Establish an SSH Tunnel:**

Use your preferred SSH client to create a tunnel. Here's an example command:

**Bash**

```bash
ssh -L 5433:localhost:5432 <username>@<VM_IP_Address>
```

**Replace the placeholders:**

- `<username>` with your VM username.
- `<VM_IP_Address>` with the public IP address of your VM.

This command creates a local port (5433) on your machine that forwards traffic to the PostgreSQL port (5432) on the VM.

**4.2 Connect to PostgreSQL Remotely:**

**Use a PostgreSQL client on your local machine and connect to:**

```bash
localhost:5433  # Port you forwarded in the tunnel (5433)
```

**Use the credentials of the user you created (e.g., `ubuntu`).**

The steps above establish a secure connection using SSH Tunneling. To further enhance security, consider these additional measures:

- **Never modify `listen_addresses` in `postgresql.conf` to `'*'`. This exposes your database server to the entire internet, making it vulnerable to attacks.**
- **Configure firewall rules on your VM to restrict access to the PostgreSQL port (default 5432) from only your specific IP address. This adds an extra layer of protection.**
- **Implement strong password policies and enforce secure user authentication methods. Utilize the `pg_hba.conf` file to control which users can connect from where and how they can authenticate.** Refer to PostgreSQL documentation for detailed configuration options.

**5. Conclusion:**

By following these steps, you can securely connect to your PostgreSQL database on a VM from anywhere or by using SSH tunneling. Remember, security should be a top priority. Implement the additional security measures mentioned above to create a more robust defense for your database server.

For further details on PostgreSQL configuration and security best practices, refer to the official PostgreSQL documentation https://www.postgresql.org/docs/.

*Helpful link and references:*

1. https://stackoverflow.com/questions/24504680/connect-to-postgres-server-on-google-compute-engine