Database Set up

Overview

The applicaton requires a PostgreSQL database, and this section covers the installation and setting up PostgreSQL, creating the databases and users, configuring access, and ensuring that the setup is secure and accessible.

Installation and Configuration

Install PostgreSQL along with its additional features

sudo apt install postgresql postgresql-contrib

Switch to the PostgreSQL user to perform the needed administrative tasks

sudo -i -u postgres

# opening  the sql prompt
psql

Within the PostgreSQL prompt, create the required databases for the production, staging and development environments

CREATE DATABASE langlearnai_be_staging_db;
CREATE DATABASE langlearnai_be_main_db;
CREATE DATABASE langlearnai_be_dev_db;

Create Users and assign passwords for each enviroment

CREATE USER langlearnai_be_staging_user WITH ENCRYPTED PASSWORD 'staging_password';
CREATE USER langlearnai_be_main_user WITH ENCRYPTED PASSWORD 'main_password';
CREATE USER langlearnai_be_dev_user WITH ENCRYPTED PASSWORD 'dev_password';

Grant the necessary Privileges to Users

GRANT ALL PRIVILEGES ON DATABASE langlearnai_be_staging_db TO langlearnai_be_staging_user;
GRANT ALL PRIVILEGES ON DATABASE langlearnai_be_main_db TO langlearnai_be_main_user;
GRANT ALL PRIVILEGES ON DATABASE langlearnai_be_dev_db TO langlearnai_be_dev_user;

GRANT ALL PRIVILEGES ON SCHEMA public TO langlearnai_be_staging_user;
GRANT ALL PRIVILEGES ON SCHEMA public TO langlearnai_be_main_user;
GRANT ALL PRIVILEGES ON SCHEMA public TO langlearnai_be_dev_user;

Exit the PostgreSQL prompt:

\q

Configure and modified the Postgres configuration files to allow PostgreSQL to Listen on External IP

sudo vim /etc/postgresql/13/main/postgresql.conf

Locate the listen_addresses line and change to:

listen_addresses = "*"

Open the pg_hba.conf file

sudo vim /etc/postgresql/13/main/pg_hba.conf

Add the following lines under IPV4 local connections to allow external access. i.e These lines configure PostgreSQL to accept connections from the specified IP address

# IPv4 local connections
host    all             all             0.0.0.0/0               md5
host    postgres        postgres        <server-ip-address>/32      md5
host    langlearnai_be_dev_db  langlearnai_be_dev_user  <server-ip-address>/32    md5
host    langlearnai_be_main_db  langlearnai_be_main_user  <server-ip-address>/32    md5
host    langlearnai_be_staging_db  langlearnai_be_staging_user  <server-ip-address>/32    md5

Restart to apply changes and enable the PostgreSQL service to start on system boot

sudo systemctl restart postgresql
sudo systemctl enable postgresql 

Allow connections on port through firewall

sudo ufw allow 5432/tcp

Accessing the Database

To connect to the PostgreSQL database remotely, use the following command

psql -h your_server_ip -U your_database_username -d your_database_name