Publishing and set up Postgres in Ubuntu [Sharing knowledge]
Tags: deploy, postgres, ubunut, sharingknowledge
Published: 2023-02-26 13:11:23

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.

```bash
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@...`

```bash
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.

```bash
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:

```bash
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:

```bash
\password postgres
```

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

```bash
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.

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

Here is the result after running the command:

```bash
Rule added
Rule added (v6)
```

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

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

1. Test - try to connect to VPS with `pgAdmin4`

![Connect postgres database with pgAdmin4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jlu0kbls7wsfx6o7nr56.png)

Hope, you can get something in this post.

Thank you for reading.