Publishing and set up Postgres in Ubuntu [Sharing knowledge]

Hi,

I just hired the Linux VPS - Ubuntu 20.04 from A supplier. So my purpose is to host my website on the internet. So my technical stack, I need to create a Postgres database. So here are the things which I set up and also public Postgres to the internet which I can access without ssh.

What will you read:

1. Connect to VPS.
2. Update Ubuntu and install Postgres Database.
3. Change the password and create a database for postgres users.
4. Customize Postgres config and public database to the internet.
5. ufw ****- Uncomplicated Firewall.

1. Connect to VPS by ssh.

ssh root@<VPS_IP_ADDRESS>

First, I need to make sure everything is updated. So I run the command in the terminal. I don’t run with sudo because I accessed to VPS by root account ssh root@...

apt-get update

2. Update Ubuntu and install Postgres Database.

After the command is done. I will run the command to install Postgres in VPS.

apt-get install postgresql postgresql-contrib

3. Change the password and create a database for postgres users.

The user postgres is the default account when installing Postgres. So I will keep this account to process my database. So I will access to Postgres by command:

su - postgres
psql

Then I will set the password to postgres user to make sure the database that is assigned to postgres user is accessed by password. I will set the password by command:

\password postgres

I will create a database name demo. Do not forget the ; at the end of the command.

CREATE DATABASE demo;

Okay, we have a user with a password and database assigned to that user. Now, we will custom Postgres server to publicize this database outside the VPS.

4. Customize Postgres config and public database to the internet.

Checking status of Postgres database is active or inactive? Use the command:

systemctl status postgresql

Customize the address listener of Postgres in VPS. Use the command:

vim /etc/postgresql/10/main/postgresql.conf

I use vim editor. You can use any editor which you want. I press / to find

listen_addresses = '[localhost](http://localhost)'
then open the comment by removing # and editing localhost to '*'.

listen_address = '*'

Update the IP address which can access only Postgres. I use 0.0.0.0/0 is the whitelist IP address to which any user can access this database on the internet:

vim /etc/postgresql/10/main/pg_hba.conf

Finding:

# IPv4 local connections:
host all all 127.0.0.1/32 md5

And replace to:

# IPv4 local connections:
host all all 0.0.0.0/0 md5

Then save it.

5. ufw ****- Uncomplicated Firewall.

I will use ufw to enable port 5432 - default port of Postgres, to user outside can only access to database via port 5432.

apt-get install ufw
ufw enable
ufw allow 5432/tcp

Here is the result after running the command:

Rule added
Rule added (v6)

Finally, I will reset the Postgres service to apply all the changes:

systemctl restart postgresql
// don't forget check the status of postgres.
systemctl status postgresql

1. Test - try to connect to VPS with pgAdmin4

Hope, you can get something in this post.

Thank you for reading.