Debian Jessie: Upgrade PostgreSQL 9.4 to 9.6

Shutdown all service that may access PostgreSQL, e.q. odoo.

Install PostgreSQL 9.6 from backport using:
# apt-get -t jessie-backports install postgresql-9.6 postgresql-client-9.6

To view PostgreSQL cluster:
# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

"There already is a cluster "main" for 9.6 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.4/main when 9.6/main also exists. The recommended procedure is to remove the 9.6 cluster with pg_dropcluster and then upgrade with pg_upgradecluster."

To remove cluster 9.6 created during installation:
# pg_dropcluster 9.6 main --stop
Redirecting stop request to systemctl

Now upgrade our cluster 9.4 to 9.6:
# pg_upgradecluster 9.4 main
Stopping old cluster...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for stop operation
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Redirecting start request to systemctl
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433
Disabling connections to the new cluster during upgrade...
Redirecting start request to systemctl
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Fixing hardcoded library paths for stored procedures...
Upgrading database YOUR_DB...
Analyzing database YOUR_DB...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Stopping target cluster...
Redirecting stop request to systemctl
Stopping old cluster...
Redirecting stop request to systemctl
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Redirecting start request to systemctl
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

  pg_dropcluster 9.4 main

After upgrade list cluster becomes like this:
# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5433 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

After everything work fine, we can drop old cluster i.e. 9.4
# pg_dropcluster 9.4 main
# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

We can remove and purge postgresql 9.4
# apt-get --purge remove postgresql-9.4 postgresql-client-9.4
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
  postgresql* postgresql-9.4* postgresql-client* postgresql-client-9.4*
0 upgraded, 0 newly installed, 4 to remove and 0 not upgraded.
After this operation, 25.3 MB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 97410 files and directories currently installed.)
Removing postgresql (9.4+165+deb8u2) ...
Removing postgresql-9.4 (9.4.10-0+deb8u1) ...
Purging configuration files for postgresql-9.4 (9.4.10-0+deb8u1) ...
Removing postgresql-client (9.4+165+deb8u2) ...
Removing postgresql-client-9.4 (9.4.10-0+deb8u1) ...
Processing triggers for postgresql-common (178~bpo8+1) ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:

References:

• https://backports.debian.org/Instructions/
• https://gist.github.com/dmitrykustov/27c673ec4f7abd716912e4c830910019

Centos ConfigServer Security & Firewall

Download latest CSF & LFD from https://download.configserver.com/csf.tgz, extract it and install it:

# wget https://download.configserver.com/csf.tgz 

# tar -xzf csf.tgz

# cd csf

# ./install.sh

Test CSF & LFD installation

Testing

# perl /usr/local/csf/bin/csftest.pl

Testing ip_tables/iptable_filter...OK

Testing ipt_LOG...OK

Testing ipt_multiport/xt_multiport...OK

Testing ipt_REJECT...OK

Testing ipt_state/xt_state...OK

Testing ipt_limit/xt_limit...OK

Testing ipt_recent...OK

Testing xt_connlimit...OK

Testing ipt_owner/xt_owner...OK

Testing iptable_nat/ipt_REDIRECT...OK

Testing iptable_nat/ipt_DNAT...OK

RESULT: csf should function on this server

Remove other firewall that may conflict by running script

# /usr/local/csf/bin/remove_apf_bfd.sh

Removing apf and/or bfd...

/usr/local/csf/bin/remove_apf_bfd.sh: line 5: apf: command not found

error reading information on service apf: No such file or directory

error reading information on service apf: No such file or directory

...Done

Removing "Testing" mode

# vi /etc/csf/csf.conf

...

TESTING = "0"

...

Block country by adding 2 letters country code in csf.conf and restart

# vi /etc/csf/csf.conf

...

CC_DENY = "CN,VN"

...

# csf -r

Country code can be found https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2

References:

• https://download.configserver.com/csf/install.txt 

Fail2ban: save your log into mysql and show it

Required:

• fail2ban 0.9
• mysql
• web server with php (apache with php and mysql library)

Create user and its database in mysql. Give all privilege to its database for its user. You can use any existing database, here is only sample of database structures:
database name : myf2b
table name: kci_logipv4

No	field	datatype
1	logdate	datetime
2	logipv4	int(11)
3	logmsg	varchar(1000)
4	kci_category	int(11)
5	id	(int11)
6	codecontinent	char(2)
7	codecontinent2	char(2)
8	codecontinent3	char(3)

table name: kci_category

No	field	datatype
1	id	int(11)
2	category	varchar(20)

Note:

1. We store IPv4 in long.
2. Field with underline is primary key 

Table kci_logipv4 will be used to store any log from trapped in fail2ban, and table kci_category will be used to categorize all log in type of attack. Populate kci_category with your wish, this is my category for example: 

id	category
10	SSH
20	FTP
30	HTTP/HTTPS
40	SMTP/POP/IMAP/POP3/S

We need a small application to store any log trapped in fail2ban. I use PHP to do that. Here is kci_log.php source code https://github.com/dedetok/fail2ban-to-mysql/blob/gh-pages/kci_log.php
That's all. Now you create a custom action mlocaldb.conf for fail2ban to call kci_log.php. Put mlocaldb.conf in /etc/fail2ban/action.d/, here is mlocaldb.conf https://github.com/dedetok/fail2ban-to-mysql/blob/gh-pages/mlocaldb.conf
Note: You need to change this part 'http://[your_domain]/kci_log.php' >> /home/[user]/logs/curlfail2ban.log

• 'http://[your_domain]/kci_log.php' where kci_log.php reside
• /home/[user]/logs/curlfail2ban.log where the log will be store. You can remove it after you confidence.

The final step, edit your /etc/fail2ban/jail.conf and add a line to use mlocaldb at the end of action, for example:

...
[sshd]

port    = ssh
logpath = %(sshd_log)s
backend = %(sshd_backend)s
enabled = true
filter = sshd
action = iptables-ipset-proto4[name=sshd]
        mlocaldb[category=10]
        abuseipdb[category=4,18,22] 

...
Note change category with id you inserted into table kci_category. For example 20 for proftpd.
Show it in your web. This is kci_logread.php source code to show the log, feel free to modify it . https://github.com/dedetok/fail2ban-to-mysql/blob/gh-pages/kci_logread.php

See on Github https://github.com/dedetok/fail2ban-to-mysql

Running java class from CLI in Debian

Prerequisite:

To set Java Environment for all users, add/edit /etc/environment:

JAVA_HOME="/usr/lib/jvm/java-8-openjdk-amd64"
CLASSPATH=".:/usr/share/java/mysql.jar:/usr/share/java/postgresql-jdbc4.jar"

I have a java class BlockedSSH.class. It required mysql.jar library. To run BlockedSSH.class from CLI and including all java library on runtime environment, use option -classpath:

$ /usr/bin/java -classpath $CLASSPATH:/root/java/ BlockedSSH

Java will find any java library that already added in /etc/environment. To check classpath

$ set | grep CLASSPATH
CLASSPATH=.:/usr/share/java/mysql.jar:/usr/share/java/postgresql-jdbc4.jar

JDK 8: executing command from java

To execute system/external command we need to use Process class. There are 2 ways to get this instance: 

1. Using static method Runtime.getRuntime()
2. Using ProcessBuilder

This is the example code to call ping or whois under Debian TestShell.java:

import java.util.Map;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.IOException;
import java.io.BufferedReader;
/*
Ref:
http://docs.oracle.com/javase/8/docs/api/java/lang/ProcessBuilder.html
http://www.javatips.net/blog/java-processbuilder-exampl
http://www.mkyong.com/java/how-to-execute-shell-command-from-java/

*/
class TestShell {
  public static void main(String[] args) {
    System.out.println("Creating ProcessBuilder Object");
    //ProcessBuilder pb = new ProcessBuilder("whois", "garasiku.web.id");
    ProcessBuilder pb = new ProcessBuilder("ping", "www.garasiku.web.id", "-c", "4");
    Map<String, String> env = pb.environment();
    System.out.println("size env: "+env.size());
    //Java 8 only, forEach and Lambda
    env.forEach((k,v)->System.out.println("Key : " + k + " Value : " + v));
    try {
      //Process p = Runtime.getRuntime().exec("ping www.garasiku.web.id -c 4");
      Process p = pb.start();
      System.out.println("dump standard output");
      InputStreamReader isr = new InputStreamReader(p.getInputStream());
      BufferedReader br = new BufferedReader(isr);
      String tmp="";
      while ((tmp = br.readLine()) != null) {
        System.out.println(tmp);
      }
      System.out.println("dump standard error");
      isr = new InputStreamReader(p.getInputStream());
      br = new BufferedReader(isr);
      tmp="";
      while ((tmp = br.readLine()) != null) {
        System.out.println(tmp);
      }
      // waitFor() method is used to wait till the process returns the exit value
      try {
        int exitValue = p.waitFor();
        System.out.println("Exit Value is " + exitValue);
      } catch (InterruptedException e) {
        e.printStackTrace();
      }
    } catch (IOException e) {
      System.out.println(e.toString());
    }  
  }
}

References:

• http://docs.oracle.com/javase/8/docs/api/java/lang/ProcessBuilder.html
• http://www.javatips.net/blog/java-processbuilder-example
• http://www.mkyong.com/java/how-to-execute-shell-command-from-java/