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:

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

1.2. Verify PostgreSQL user:

PostgreSQL creates a user named postgres. Verify its existence:

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:

sudo -i -u postgres

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.

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

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

$ 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

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.

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

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

pg_hba.conf

From 20.1. The pg_hba.conf File: "_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.) _"

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.

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

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:

$ 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

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:

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