Open Source Data Solution Example (OSDSE)

Working example for my Open Source Data Solution Example. Hosted VM with digitalocean.com (if interested see my referral link for an offer benefitting both of us)

Check with other notes on Server Configuration - Virtual Host

Host Setup

Hostname: osdse.roman-halliday.com

################################################################
# As root : or sudo su
 
apt-get update && apt-get upgrade -y
# Kept new version (root login key only)
 
################################################################
# create a user for me
adduser david
# Add my user to sudo
usermod -a -G sudo david
 
#Setup keys
su david
# As david (paste key to new authorized_keys file)
mkdir -p ~/.ssh
vi ~/.ssh/authorized_keys
 
################################################################
# sshd config (stop root login, all authentication by keys only is now default)
vi /etc/ssh/sshd_config
systemctl restart ssh

Firewall

See Firewall

# Allow SSH before we turn on
sudo ufw allow 22
# Enable Firewall
sudo ufw enable
# View status
ufw status

GitHub

As user (if interaction with git repos is required):

ssh-keygen -t ed25519 -C "david@osdse.roman-halliday.com"
 
vi ~/.ssh/id_ed25519.pub

Python

If you need multiple versions/more control, go for pyenv. But in most cases the latest stable will be fine (right how that's 3.12).

Basic (one python)

Ubuntu searching python versions

apt-cache search python3 | grep 'python3 '
apt-cache search python3 | grep -e 'python3\.[0-9][0-9]-full'

Just get the latest full package for this version of Ubuntu (more than we need, but it's not like it's expensive)

sudo apt-get install python3-full
 
# Test
python3 --version

Optional (make the word python still point to python3):

sudo apt-get install python-is-python3
 
# Test
python --version

pyenv (multiple python versions)

Prerequisite for pyenv to build some things:

sudo apt-get install build-essential

Install pyenv (not an ubuntu package):

curl https://pyenv.run | bash

Make the changes to .bashrc as suggested.

Then install the python version you wish to have:

pyenv install 3.11

View python versions:

pyenv versions

PostgreSQL

see: PostgreSQL

apt-cache search postgres | grep '^postgresql-*[0-9]* '
sudo apt-get install postgresql

Config

sudo vi /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;

sudo vi /etc/postgresql/16/main/pg_hba.conf

#host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

/etc/init.d/postgresql restart

Firewall:

sudo ufw allow 5432

Configure simple shop: https://github.com/d-roman-halliday/simple_shop_data_generator/blob/main/database_setup/database_and_users_postgres.sql

MySQL

See: mysql

Apache

See: Apache2 Configuration

sudo apt-cache search apache2 | grep php
sudo apt-cache search php | grep mysql
sudo apt-cache search php | grep pgsql

Apache, PHP and connectivity for MySQL & PostgreSQL:

sudo apt-get install apache2 libapache2-mod-php php-mysql php-pgsql

Python: wsgi

sudo apt-cache search apache2 | grep wsgi

Apache, Python:

sudo apt-get install apache2 libapache2-mod-wsgi-py3

sudo ufw app list
 
sudo ufw allow 'Apache Full'
 
sudo ufw status

# certbot                : Generates certificates
# python3-certbot-apache : Automates the apache part
sudo apt-get install certbot python3-certbot-apache
 
sudo certbot --apache

Website & Data Viewer

cd /var/www/html
sudo vi index.html

index.html

<!DOCTYPE html>
<html lang="en">
 
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Home: osdse.roman-halliday.com</title>
  <link rel="stylesheet" href="styles.css">
</head>
 
<body>
  <h1>Home : osdse.roman-halliday.com</h1>
  <p>See the sample data:</p>
  <ul>
          <li>Explore: <a href="sample_data_explore/">explore</a> (username: <code>simple_shop_adminer_reader</code> password: <code>simple_shop_adminer_reader_password</code>)</li>
          <li>Docs: https://github.com/d-roman-halliday/simple_shop_data_generator</li>
  </ul>
</body>
 
</html>

Adminer User on DB:

sudo -i -u postgres psql

CREATE USER simple_shop_adminer_reader  WITH ENCRYPTED PASSWORD 'simple_shop_adminer_reader_password';
 
\c simple_shop;
 
-- Grant privileges for simple_shop_adminer_reader (read-only access)
GRANT CONNECT ON DATABASE simple_shop TO simple_shop_adminer_reader;
GRANT USAGE ON SCHEMA public TO simple_shop_adminer_reader;
GRANT pg_read_all_data TO simple_shop_adminer_reader;

Adminer:

sudo mkdir -p sample_data_explore
cd sample_data_explore
sudo wget https://github.com/adminerevo/adminerevo/releases/download/v4.8.4/editor-4.8.4.php
sudo vi index.php

index.php

<?php
function adminer_object() {
 
  class AdminerSoftware extends Adminer {
 
    function name() {
      // custom name in title and heading
      return 'Postgres Test Adminer Editor';
    }
 
    function database() {
      // database name, will be escaped by Adminer
      return 'simple_shop';
    }
 
    function loginForm() {
      echo "<table cellspacing='0' class='layout'>\n";
      echo $this->loginFormField('username', '<tr><th>' . lang('Username') . '<td>', '<input type="hidden" name="auth[driver]" value="pgsql"><input type="hidden" name="auth[server]" value="127.0.0.1"><input name="auth[username]" id="username" value="' . h($_GET["username"]) . '" autocomplete="username" autocapitalize="off">' . script("focus(qs('#username'));"));
      echo $this->loginFormField('password', '<tr><th>' . lang('Password') . '<td>', '<input type="password" name="auth[password]" autocomplete="current-password">' . "\n");
      echo "</table>\n";
      echo "<p><input type='submit' value='" . lang('Login') . "'>\n";
      echo checkbox("auth[permanent]", 1, $_COOKIE["adminer_permanent"], lang('Permanent login')) . "\n";
    }
 
  }
 
  return new AdminerSoftware;
}
 
include './editor-4.8.4.php';

Junk

Probably ignore this

Link

<a href="https://www.digitalocean.com/?refcode=bb281f2fae53&utm_campaign=Referral_Invite&utm_medium=Referral_Program&utm_source=badge"><img src="https://web-platforms.sfo2.cdn.digitaloceanspaces.com/WWW/Badge%201.svg" alt="DigitalOcean Referral Badge" /></a>
[![DigitalOcean Referral Badge](https://web-platforms.sfo2.cdn.digitaloceanspaces.com/WWW/Badge%201.svg)](https://www.digitalocean.com/?refcode=bb281f2fae53&utm_campaign=Referral_Invite&utm_medium=Referral_Program&utm_source=badge)

Table of Contents