====== 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)

{{ ::powered_by_digitalocean_3.svg |https://www.digitalocean.com/?refcode=bb281f2fae53&utm_campaign=Referral_Invite&utm_medium=Referral_Program&utm_source=badge}}

Check with other notes on [[server_configuration|Server Configuration - Virtual Host]]

===== Host Setup =====

Hostname: ''osdse.roman-halliday.com''

<code bash>
################################################################
# 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
</code>

==== Firewall ====
See [[server_configuration:firewall|Firewall]]
<code bash>
# Allow SSH before we turn on
sudo ufw allow 22
# Enable Firewall
sudo ufw enable
# View status
ufw status
</code>

==== GitHub ====
As user (if interaction with git repos is required):
  * https://docs.github.com/en/authentication/connecting-to-github-with-ssh/generating-a-new-ssh-key-and-adding-it-to-the-ssh-agent
  * https://docs.github.com/en/authentication/connecting-to-github-with-ssh/adding-a-new-ssh-key-to-your-github-account
<code bash>
ssh-keygen -t ed25519 -C "david@osdse.roman-halliday.com"

vi ~/.ssh/id_ed25519.pub
</code>


===== Python =====
If you need multiple versions/more control, go for [[https://github.com/pyenv/pyenv|pyenv]]. But in most cases the latest stable will be fine (right how that's 3.12).

==== Basic (one python) ====

Ubuntu searching python versions
<code bash>
apt-cache search python3 | grep 'python3 '
apt-cache search python3 | grep -e 'python3\.[0-9][0-9]-full'
</code>

Just get the latest full package for this version of Ubuntu (more than we need, but it's not like it's expensive)
<code bash>
sudo apt-get install python3-full

# Test
python3 --version
</code>

Optional (make the word python still point to python3):
<code bash>
sudo apt-get install python-is-python3

# Test
python --version
</code>

==== pyenv (multiple python versions) ====

Prerequisite for ''pyenv'' to build some things:
<code bash>
sudo apt-get install build-essential
</code>

Install ''pyenv'' (not an ubuntu package):
<code bash>
curl https://pyenv.run | bash
</code>

Make the changes to ''.bashrc'' as suggested.

Then install the python version you wish to have:
<code bash>
pyenv install 3.11
</code>

View python versions:
<code bash>
pyenv versions
</code>

===== PostgreSQL =====
see: [[server_configuration:postgres|PostgreSQL]]

<code bash>
apt-cache search postgres | grep '^postgresql-*[0-9]* '
sudo apt-get install postgresql
</code>

==== Config ====

<code bash>
sudo vi /etc/postgresql/16/main/postgresql.conf
</code>
<code>
listen_addresses = '*'          # what IP address(es) to listen on;
</code>
<code bash>
sudo vi /etc/postgresql/16/main/pg_hba.conf
</code>
<code>
#host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256
</code>

<code bash>
/etc/init.d/postgresql restart
</code>

Firewall:
<code bash>
sudo ufw allow 5432
</code>

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

===== MySQL =====
See: [[server_configuration:mysql|]]

===== Apache =====
See: [[server_configuration:apache|Apache2 Configuration]]

<code bash>
sudo apt-cache search apache2 | grep php
sudo apt-cache search php | grep mysql
sudo apt-cache search php | grep pgsql
</code>

Apache, PHP and connectivity for MySQL & PostgreSQL:
<code bash>
sudo apt-get install apache2 libapache2-mod-php php-mysql php-pgsql
</code>

Python: wsgi
<code bash>
sudo apt-cache search apache2 | grep wsgi
</code>

Apache, Python:
<code bash>
sudo apt-get install apache2 libapache2-mod-wsgi-py3
</code>

<code bash>
sudo ufw app list

sudo ufw allow 'Apache Full'

sudo ufw status
</code>

<code bash>
# certbot                : Generates certificates
# python3-certbot-apache : Automates the apache part
sudo apt-get install certbot python3-certbot-apache

sudo certbot --apache
</code>

==== Website & Data Viewer ====

<code bash>
cd /var/www/html
sudo vi index.html

</code>
<file 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>
</file>

Adminer User on DB:
<code bash>
sudo -i -u postgres psql
</code>
<code sql>
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;
</code>

Adminer:
<code bash>
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
</code>

<file 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';

</file>
====== Junk ======

Probably ignore this

===== Link =====


<code>
<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)
</code>

{{ ::powered_by_digitalocean_1.svg |https://www.digitalocean.com/?refcode=bb281f2fae53&utm_campaign=Referral_Invite&utm_medium=Referral_Program&utm_source=badge}}
{{ ::powered_by_digitalocean_2.svg |https://www.digitalocean.com/?refcode=bb281f2fae53&utm_campaign=Referral_Invite&utm_medium=Referral_Program&utm_source=badge}}
{{ ::powered_by_digitalocean_3.svg |https://www.digitalocean.com/?refcode=bb281f2fae53&utm_campaign=Referral_Invite&utm_medium=Referral_Program&utm_source=badge}}