Category Archives: Server Admin

Server Config, Operations, Tips and Tricks

This server setup

I use a pre-crafted image from my Cloud Provider, so need to handle for iptables rules first.

sudo sh -c "iptables -I INPUT -p tcp -m tcp --dport 80 -j ACCEPT && iptables -I INPUT -p tcp -m tcp --dport 443 -j ACCEPT && service iptables save"

1. GRUB

GRUB_CMDLINE_LINUX_DEFAULT="quiet splash nopti noibrs noibpb nospec nospectre_v2 nospec_store_bypass_disable mitigations=off"

2. FSTAB

/etc/fstab
LABEL=cloudimg-rootfs / ext4 noatime,defaults 0 1

3. SYSCTRL

/etc/sysctl.conf

vm.swappiness = 1
vm.dirty_background_ratio = 10
vm.dirty_ratio = 5
fs.inotify.max_user_watches = 524288
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv6.conf.lo.disable_ipv6 = 1

sudo crontab -e
@reboot /sbin/sysctl --load=/etc/sysctl.conf

4. IPTables

/etc/iptables.conf

-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT
-A INPUT -p tcp -m state --state NEW -m tcp --dport 443 -j ACCEPT

5. MariaDB

sudo apt-get install software-properties-common dirmngr apt-transport-https
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el,s390x] https://mirrors.xtom.com.hk/mariadb/repo/10.7/ubuntu focal main'

sudo apt update
sudo apt install mariadb-server
ALTER USER 'root'@'localhost' IDENTIFIED BY 'wong2903';
CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'wong2903';
GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'phpmyadmin'@'localhost';

6. PHP + Nginx

sudo apt install php php-cli php-fpm php-json php-common php-mysql php-zip php-gd php-mbstring php-curl php-xml php-pear php-bcmath
sudo apt install nginx php-curl php-gd php-intl php-mbstring php-soap php-xml php-xmlrpc php-zip
See nginx.conf and site-available/default

7. Certbot

mkdir -p .xxxxx
touch .xxxxx/cloudflare.ini
chmod 600 .xxxxx/cloudflare.ini
sudo snap install core; sudo snap refresh core
sudo snap install --classic certbot
sudo snap set certbot trust-plugin-with-root=ok
sudo snap install certbot-dns-cloudflare
sudo certbot run --dns-cloudflare --dns-cloudflare-credentials ~/.xxxxx/cloudflare.ini -d *.jimmysyss.com -i nginx

8. PHPMyAdmin

# Create a symlink in /var/www/html to use phpmyadmin
sudo apt-get --no-install-recommends install phpmyadmin
sudo mkdir /var/www/html/phpmyadmin
sudo ln -s /usr/share/phpmyadmin phpmyadmin

In PHPMyAdmin, create a new user phpmyadmin with schema, and then select that schema, Operation, create configuration.

9. Wireguard with Algo VPN

git clone https://github.com/trailofbits/algo.git
sudo apt install -y --no-install-recommends python3-virtualenv
cd algo

python3 -m virtualenv --python="$(command -v python3)" .env &&
source .env/bin/activate &&
python3 -m pip install -U pip virtualenv &&
python3 -m pip install -r requirements.txt

10. PostgreSQL

# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql
curl -L https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo sh -c "echo 'deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main' > /etc/apt/sources.list.d/timescaledb.list"
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo apt update
apt install timescaledb-2-postgresql-14

11. Docker

sudo apt-get install ca-certificates curl gnupg lsb-release
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt-get update
sudo apt-get install docker-ce docker-ce-cli containerd.io
sudo usermod -aG docker $USER

Example of NGINX reverse proxy config

Common Reverse Proxy

upstream foundation {
    ip_hash;
    server localhost:18080;
    keepalive 8;
}


server {
        listen 443 ssl http2;
        listen [::]:443 ssl http2;

        server_name xxx.jimmysyss.com;

        root /var/www/pwa;
        index index.html;

	location /foundation/ {
		proxy_set_header Connection "";
		proxy_set_header Host $http_host;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		proxy_set_header X-Forwarded-Proto $scheme;
		proxy_set_header X-Frame-Options SAMEORIGIN;
		proxy_pass http://foundation/;
        }

	location / {
                try_files $uri $uri/ /index.html;
#		try_files $uri $uri/ =404;
        }
}
proxy_cache_path /var/cache/nginx levels=1:2 keys_zone=mattermost_cache:10m max_size=3g inactive=120m u$

server {
        listen 443 ssl http2;
        listen [::]:443 ssl http2;

        server_name mattermost.jimmysyss.com;
        index index.html;

   location ~ /api/v[0-9]+/(users/)?websocket$ {
       proxy_set_header Upgrade $http_upgrade;
       proxy_set_header Connection "upgrade";
       client_max_body_size 50M;
       proxy_set_header Host $http_host;
       proxy_set_header X-Real-IP $remote_addr;
       proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
       proxy_set_header X-Forwarded-Proto $scheme;
       proxy_set_header X-Frame-Options SAMEORIGIN;
       proxy_buffers 256 16k;
       proxy_buffer_size 16k;
       client_body_timeout 60;
       send_timeout 300;
       lingering_timeout 5;
       proxy_connect_timeout 90;
       proxy_send_timeout 300;
       proxy_read_timeout 90s;
       proxy_pass http://localhost:8065;
   }

   location / {
       client_max_body_size 50M;
       proxy_set_header Connection "";
       proxy_set_header Host $http_host;
       proxy_set_header X-Real-IP $remote_addr;
       proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
       proxy_set_header X-Forwarded-Proto $scheme;
       proxy_set_header X-Frame-Options SAMEORIGIN;
       proxy_buffers 256 16k;
       proxy_buffer_size 16k;
       proxy_read_timeout 600s;
       proxy_cache mattermost_cache;
       proxy_cache_revalidate on;
       proxy_cache_min_uses 2;
       proxy_cache_use_stale timeout;
       proxy_cache_lock on;
       proxy_http_version 1.1;
       proxy_pass http://localhost:8065;
   }
}

Ansible First Impression

I have tested out Ansible recently for Server admin, the experience is nice but it may take time to understand the whole eco-system.

https://github.com/jimmysyss/ansible-home

There are a few concepts to understand before hand

Ansible uses SSH to execute Python Code in the Client machine, therefore, the Client needs to install OpenSSH and Python by default. It looks up Python 2 by default, a custom parameter is needed to instruct Ansible to use Python 3 instead. Everything makes sense if follows this direction.

Ansible Playbook is the script that drives the client machines to work, there are a lot of build in modules in Ansible. Worst case scenario is using the shell script module to write all commands. In this case, the script may only execute once, and may have side effect if execute more than once.

Ansible Collections and Roles are pre-built extensions for Ansible, a role usually serve one function, for example, managing sysctl.conf service or managing Grub. A collection is a collection of Role that may have multiple functions.

Ansible Galaxy is the marketplace of Ansible Collections and Ansible Roles. However, only those frequently downloaded scripts are useful in general. Others are just for a niche use cases.

Finally, the Ansible comes with Ubuntu 18.04 APT repo are outdated(Version 2.5), you need version 2.8 or 2.9 to be useful. You can install by installing the via installing the Ansible APT library.


sudo apt update
sudo apt install software-properties-common
sudo apt-add-repository --yes --update ppa:ansible/ansible
sudo apt install ansible

When u are using a passphase protected private key, you need to start the local ssh agent and add the private key to the agent.

eval "$(ssh-agent -s)"
ssh-add ~/.ssh/id_rsa

Installing OracleXE 18 on CentOS on Proxmox VE LXC

Installing Oracle used to be a tedious and error prone operation, it takes my inexperienced colleague a week, but still cannot fix it. I have fixed the server and would make note on the procedures to install Oracle so that I won’t spend too much time on it next time.

Basically I follow the RHEL steps in the following links. CentOS on LXC is exactly the same in the following link.
https://www.oracle.com/database/technologies/appdev/xe/quickstart.html

I need to prepare the environment first as followed. Everything is done by “root”

  • Host Table – Hard Code the hostname in /etc/hosts, with the following line
    10.168.10.90    OracleCentOS
  • Disable IPv6 – seems IPv6 doesn’t work very well in Oracle XE 18, I can only connect locally (with port forwarding) but not from another machine. 
    edit /etc/sysctl.conf , amend the following two lines at the end of filenet.ipv6.conf.all.disable_ipv6 = 1
    net.ipv6.conf.default.disable_ipv6 = 1
  • Install curl for subsequent installation steps
    yum install curl
  • Restart server to make every thing effective. 
    shutdown -r now

And then we could follow the above link “Red Hat compatible Linux distribution” section to install the Oracle

We can get an up-and-running Oracle up to now, however, it can only be connect by sqlplus, you cannot connect to it from external. It is due to the listener settings is not correct yet. So, we need to go through a few post installation configuration steps.

  • Setup the Oracle default DB
    /etc/init.d/oracle-xe-18c configure
  • Modify tnsname.ora and listener.ora, use hostname OracleCentOS and port 1521
    vi /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora

tnsnames.ora Network Configuration File: /opt/oracle/product/18c/dbhomeXE/network/admin/tnsnames.ora
Generated by Oracle configuration tools.
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OracleCentOS)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
LISTENER_XE =
(ADDRESS = (PROTOCOL = TCP)(HOST = OracleCentOS)(PORT = 1521))

  • vi /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = OracleCentOS)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

  • Restart the oracle service by the following command 
    /etc/init.d/oracle-xe-18c restart
  • You can verify port 1521 opens with the following command
    netstat -anp | grep 1521

You should be able to connect to the Oracle via IP as sysdba in SQLDeveloper

it is strange that I cannot simply use SQLDeveloper to create user, we need to modify the final SQL to create user a bit. https://stackoverflow.com/questions/33330968/error-ora-65096-invalid-common-user-or-role-name-in-oracle

Therefore, what we do is to add this line in the SQL tab during create user.
alter session set "_ORACLE_SCRIPT"=true;  

PostgreSQL + TimescaleDB + MadLib installation

PostgreSQL is a very very powerful database engine by itself, there are many advanced functions which is far beyond the standard SQL query.

The following combo are great as a starting point with the following functionality.

TimescaleDB – Time Series Database, support time based partitioning of data sharding.
https://docs.timescale.com/latest/getting-started

MadLib – a DB extension to carry out common Machine learning program within the DB.
https://cwiki.apache.org/confluence/display/MADLIB/Architecture

Here are the installation steps.

# install PostgreSQL and PGXN client
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-10 libpq-dev postgresql-server-dev-10 postgresql-plpython-10 pgxnclient cmake g++ m4

# Include the TimescaleDB PPA
sudo add-apt-repository ppa:timescale/timescaledb-ppa

sudo apt-get update

# Install the TimescaleDB
sudo apt install timescaledb-postgresql-10
sudo timescaledb-tune --quiet --yes

# Install MADLib
sudo pgxnclient install madlib
sudo pgxnclient load madlib 

# Install CStore
sudo apt-get install bison flex git libreadline-dev libz-dev git libpq-dev libprotobuf-c0-dev make protobuf-c-compiler
sudo pgxn install cstore_fdw
sudo pgxn load cstore_fdw

After you installed all of them, you may play around with the example.
https://docs.timescale.com/latest/tutorials/tutorial-hello-nyc

For each newly created db, we need to enable the extensions, for example.

CStore Reference
https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/
https://info.citusdata.com/rs/235-CNE-301/images/Columnar_Store_for_PostgreSQL_Using_cstore_fdw_Webinar_Slides_0915.pdf

CREATE USER jimmy WITH PASSWORD 'xxxxxx';
CREATE DATABASE testdb OWNER=jimmy LC_COLLATE='C' LC_CTYPE='C' template=template0;
GRANT ALL ON DATABASE testdb to jimmy;
\c testdb
CREATE EXTENSION plpythonu;
CREATE EXTENSION madlib;
CREATE EXTENSION timescaledb;
CREATE EXTENSION cstore_fdw;
CREATE SERVER cstore_server FOREIGN DATA WRAPPER cstore_fdw;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to jimmy;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to jimmy;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to jimmy;

Setup IBM MQ on Ubuntu

My recently is recently integrating with a banking solution via IBM MQ, I need to install an IBM MQ for testing. Here are my steps for the installation

PS. IBM MQ 9.1 requires IPv6 for all communication, so, I fall back to IBM MQ 8.0

1. Download IBM MQ binary from IBM Website
https://developer.ibm.com/messaging/mq-downloads/

2. Follow the following link for the installation
https://www.ibm.com/support/knowledgecenter/en/SSFKSJ_9.1.0/com.ibm.mq.ins.doc/q008640_.htm

3. After installation, add ubuntu user (current user) to the “mqm” group

sudo usermod -aG mqm $USER

4. Up to now, the installation should be completed.

5. Setup Env and Queue Manager

There is a default MQ Installation (Installation1) setup, you can use the installation directly.

The program directory is /opt/mqm , you can find all command in /opt/mqm/bin , all subsequent execution are in that folder.

6. Create the MQ Manager

crtmqm QM1
strmqm QM1

7. Download the sample script from the following link, it will create some default queue and settings.

https://raw.githubusercontent.com/ibm-messaging/mq-dev-samples/master/gettingStarted/mqsc/mq-dev-config.mqsc

Modify the script, change the user from “app” to “ubuntu”

DEFINE CHANNEL(‘DEV.APP.SVRCONN’) CHLTYPE(SVRCONN) MCAUSER(‘ubuntu’) REPLACE

Execute the script

./runmqsc QM1 < ~/mq-dev-config.mqsc

8. MQ Server uses Linux user and user group for authentication and authorization, so, we need to assign more MQ Server privilege to Linux user group (mqm)

./setmqaut -m QM1 -t qmgr -g mqm +connect +inq
./setmqaut -m QM1 -n DEV.** -t queue -g mqm +put +get +browse +inq

9. Finally, since IBM MQ 7.5, Channel would also require authentication, but I have no idea on how it works yet, so the simplest solution is disabled that.

./runmqsc QM1
ALTER QMGR CHLAUTH(DISABLED)

PS. We should figure out how to work properly with the channel authentication.

http://www-01.ibm.com/support/docview.wss?uid=swg21577137

10. Testing with MQ Explorer

Install IBM MQ Explorer from the following link

https://www-01.ibm.com/support/docview.wss?uid=swg24021041

You can use MQ Explorer to connect to .39 MQ Queue Manager.

ActiveMQ Artemis quick start notes

I have played around with Artemis for a few days, here are the useful command I used. The exact config is stored at my github.

https://github.com/jimmysyss/artemis-config

1. Create 2 brokers with port offset
artemis create mybroker1 –port-offset 0
artemis create mybroker2 –port-offset 0

2. Testing the producer and consumer
./artemis producer –url tcp://localhost:61616
./artemis consumer –url tcp://localhost:61616

3. Enabled clustered
See jgroup.xml and broker.xml , and

4. Load balancing
See broker.xml, Add in

5. Enable SSL for HornetQ OpenWire protocol
a. Provide keystore.jks and truststore.jks
b. Config the acceptor in broker.xml

MiniDLNA configuration

MiniDLNA is a uPnP server that allow you to play Video, Picture and Music over LAN via IP broadcasting protocol.

I have installed it in my Raspberry Pi as a mini-server at home, to stream video to my TV and my XiaoMi TVBox.

By default, it runs with “minidlna” user, it may not be so convenient for use because it is not a usual user, I need to manually copy files to the Pi and subsequently to the folder accessible by minidlna. It is related to the user privilege issue. Funny enough, simply change on folder to 666 doesn’t solve the problem because minidlna requires to access the folder along the path from “/”

Therefore, the only solution is to configure MiniDLNA to run on another user account rather than the default minidlna user.

The minidlna.conf is as followed


# Specify the user name or uid to run as.
user=pi
....
#media_dir=/var/lib/minidlna
media_dir=/home/pi/DLNA
....
#db_dir=/var/cache/minidlna
db_dir=/home/pi/.dlnacache
....
#log_dir=/var/log
log_dir=/home/pi/.dlnalog

Install Superset with Ubuntu 16.04 with venv


sudo apt-get install build-essential libssl-dev libffi-dev python-dev python-pip libsasl2-dev libldap2-dev python3-dev

python3 -m venv superset-venv

source superset-venv/bin/activate

pip install --upgrade setuptools pip

pip install superset

# The following copy from https://superset.incubator.apache.org/installation.html

# Create an admin user (you will be prompted to set username, first and last name before setting a password)
fabmanager create-admin --app superset

# Initialize the database
superset db upgrade

# Load some data to play with
superset load_examples

# Create default roles and permissions
superset init

# Start the web server on port 8088, use -p to bind to another port
superset runserver