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:
- Connect to VPS.
- Update Ubuntu and install Postgres Database.
- Change the password and create a database for
postgres
users. - Customize Postgres config and public database to the internet.
-
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
- Test - try to connect to VPS with
pgAdmin4
Hope, you can get something in this post.
Thank you for reading.
Top comments (0)