In this step, you will be installing Postgres on your server. The first thing to do is SSH into your server by running:
ssh user@ip
Next, update your server packages and dependencies by running:
sudo apt update
Install Postgres by running:
sudo apt install postgresql postgresql-contrib
This will install Postgres along with its associated dependencies. When the process is complete, switch the user to postgres to be able to execute Postgres commands with Postgres default user by running:
su - postgres
The server user will be switched from to postgres. You can access the Postgres shell by running:
psql
You will be shown something similar to this:
postgres@ubuntu:~$ psql
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1))
Type "help" for help.
postgres=#
In this step, you will be creating a new user that will be used to access your Postgres database remotely. To create a new user, exit the Postgres shell by executing:
\q
While still being logged in as postgres run the following command to create a new user:
createuser --interactive --pwprompt
A prompt will be shown to you asking you to input your desired user role, name, password, and if you want the user to be a superuser. Here is an example:
Enter name of role to add: john
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
I named my user role john and I made my user a superuser. A superuser is a user that has all the privileges available on a Postgres instance. Next, we will be assigning cleopatra to a database. To do this, run the following command:
createdb -O john doe
This command above will create a new database named doe and assign john to be the database user.
In this step, we will look at how to configure Postgres to accept external connections. To begin, open the configuration file with your preferred editor:
sudo nano /etc/postgresql/<version>/main/postgresql.conf
Look for this line in the file:
#listen_addresses = 'localhost'
Uncomment, and change the value to '*', this will allow Postgres connections from anyone.
listen_addresses = '*'
Save and exit the file. Next, modify pg_hba.conf to also allow connections from everyone. Open the file with your preferred editor:
sudo nano /etc/postgresql/<version>/main/pg_hba.conf
Modify this section:
# IPv4 local connections:
host all all 127.0.0.1/32 md5
To this:
# IPv4 local connections:
host all all 0.0.0.0/0 md5
This file stores the client authentication, each record specifies an IP address range, database name, username, and authentication method. In our case, we are granting all database users access to all databases with any IP address range, thus, letting any IP address connect. Save and exit the file. Next, allow port 5432 through the firewall by executing:
sudo ufw allow 5432/tcp
Finally, restart Postgres to apply all the changes you have made to its configuration by running:
sudo systemctl restart postgresql
You may connect remotely using server ip, port number 5432 and credential that has been created.