Update One column with result from another

Problem

Needed to update one column with the results of another column.

Solution

Create a derived table to get the results that you want and then join the id of the result with that of the table as shown:

update payments
set total = amounts.grandtotal
from
(
select co.orderid, co.grandtotal
from orders co
    inner join customer cu on co.customer_id = cu.customer_id
where co.status = 1 and co.amt > 0
) as amounts
where payments.orderid=amounts.orderid;

Reference

updating table rows in postgres using subquery

Apache Reverse Proxy on Multiple ports

Problem

I needed to share logs with a colleague on one url that is used to access an application. In the backend, apache proxies requests to one application and the logs are reserved by a different application

Solution

  • Open the apache site host configuration file and then add the other application in the ProxyPass directive.
  • Note the order in which you add the directive because if for example the application is served in the / directory and the logs are served in the /log directory then make sure /log comes before / as shown below:
root@whiscardz:# vim /etc/apache2/sites-available/app.whiscardz.org.conf
<VirtualHost *:80>
        ServerName app.whiscardz.org
        ProxyPass        /logs http://localhost:1223/
        ProxyPassReverse /logs http://localhost:1223/

        ProxyPass        / http://localhost:1221/
        ProxyPassReverse / http://localhost:1221/
        ProxyPassReverseCookieDomain localhost app.whiscardz.org
        ProxyPreserveHost On
</VirtualHost>

References

reverse proxy on multiple ports

Breakdown disk usage on Linux server

Problem

Have an Ubuntu server whose disk space keeps growing over time with no clear visible culprits causing the same.

Need a way to be able to sort the directories by usage.

Solution

  • Login as the root user and then run the following command:
root@whiscardz:/# du -h -a / | sort -h | tail -n 15
28G     /home/whiscardz/idempiere/do/database/dump
29G     /home/teamcity
38G     /home/vagrant/VMs/default_1587717642629_75966
38G     /home/vagrant/VMs/default_1587717642629_75966/ubuntu-bionic-18.04-cloudimg.vdi
43G     /opt
46G     /home/whiscardz
57G     /var/lib/docker/aufs
57G     /var/lib/docker/aufs/diff
94G     /var/lib/docker
98G     /var/lib
102G    /var
111G    /home/vagrant/VMs
114G    /home/vagrant
212G    /home
371G    /
  • The above estimates space usage of all files in the root(/) directory in human readable form then sorts them and lists the last 15 of the summary
  • After getting the above summary if you want to drill down further into a directory, then run the following:
root@whiscardz:~# sudo du -h /home/teamcity/ | sort -rh | head -5                                                                                                        
29G     /home/teamcity/
15G     /home/teamcity/.BServer
8.7G    /home/teamcity/bAgent
8.6G    /home/teamcity/.BServer/backup
6.7G    /home/teamcity/bAgent/system

References

13 du (Disk Usage) Command Examples In Linux

How to Check Disk Space Usage in Linux

3 Simple Ways to Get the Size of Directories in Linux

How to Get the Size of a Directory in Linux

Install Fail2Ban using UFW and Custom ssh port Ubuntu

Problem

Noticed automated ssh brute-force login attempts in the auth logfile on a server

Jun 22 12:49:55 Whiscardz sshd[29114]: Received disconnect from 18.2.17.3 port 47636:11: Normal Shutdown, Thank you for playing [preauth]

Solution

Fail2ban attempts to alleviate these issues by providing an automated way of not only identifying possible break-in attempts, but acting upon them quickly and easily in a user-definable manner.

Fail2ban scans log files and detects patterns which correspond to possible break-in attempts and then performs actions such as adding a new rule in a firewall chain and sending an e-mail notification to the system administrator.

Installation and Configuration

  • Install fail2ban
imela@whiscardz:~$ sudo apt update
imela@whiscardz:~$ sudo apt install fail2ban
  • Enable the ufw firewall that comes with most ubuntu distros. This is what fail2ban will use to block ips that fail2ban finds:
imela@whiscardz:~$ sudo ufw default deny incoming
imela@whiscardz:~$ sudo ufw default allow outgoing
imela@whiscardz:~$ sudo ufw allow ssh
imela@whiscardz:~$ sudo ufw allow 3322
imela@whiscardz:~$ sudo ufw enable
Firewall is active and enabled on system startup
imela@whiscardz:~$ sudo ufw status verbose
Status: active
  • If you use ssh on a custom port, then create ufw app profile:
imela@whiscardz:~$ sudo vim /etc/ufw/applications.d/openssh-server

  1 [OpenSSH]
  2 title=Secure shell server, an rshd replacement
  3 description=OpenSSH is a free implementation of the Secure Shell protocol.
  4 ports=22/tcp
  5 
  6 [OpenSSH-3322]
  7 title=Secure shell server, an rshd replacement
  8 description=OpenSSH is a free implementation of the Secure Shell protocol.
  9 ports=3322/tcp

imela@whiscardz:~$ sudo ufw app list 
[sudo] password for imela: 
Available applications:
  OpenSSH
  OpenSSH-3322
  • Customize the sshd jail:
imela@whiscardz:~$ sudo vim /etc/fail2ban/fail2ban.local

[sshd]
port = 3322
action = ufw[application="OpenSSH-3322", blocktype=reject]
logpath = %(sshd_log)s
backend = %(sshd_backend)s
  • Note that “action” pass the “application” parameter that corresponds to the app profile that we have created earlier. Reload fail2ban so that it recognizes the new jail configuration.
sudo fail2ban-client reload
  • Now you can test the jail. Try logging in to the box with invalid credentials a couple of times, and check the jail status
imela@whiscardz:~$ sudo fail2ban-client status sshd
    Status for the jail: sshd
    |- Filter
    | |- Currently failed: 0
    | |- Total failed: 10
    | `- File list: /var/log/auth.log
    `- Actions
    |- Currently banned: 1
    |- Total banned: 2
    `- Banned IP list: 14.24.xxx.yyy
  • Check whether the ufw filter has been added:
    To                         Action      From
    --                         ------      ----
    3222/tcp (OpenSSH-3222)    REJECT IN   14.24.xxx.yyy
    3222                       ALLOW IN    Anywhere
    8443                       ALLOW IN    Anywhere
    3222 (v6)                  ALLOW IN    Anywhere (v6)
    8443 (v6)                  ALLOW IN    Anywhere (v6)
  • The setup will result in failban inserting an ufw filter that block both ports configured on that particular profile.

References

fail2ban wiki

fail2ban, ufw, and sshd with custom port on Ubuntu

Securing Ubuntu 18.04 ssh server with ufw and fail2ban

potential ufw and fail2ban conflicts

A Tutorial for Using Fail2ban to Secure Your Server

Limiting failed ssh login attempts with fail2ban

WARN: Duplicate profile ‘Apache’, using last found (ufw)

Problem

When I run any ufw command on the terminal, I get the following error:

imela@whiscardz:~$ sudo ufw status verbose
WARN: Duplicate profile 'Apache', using last found
WARN: Duplicate profile 'Apache Secure', using last found
WARN: Duplicate profile 'Apache Full', using last found
Status: inactive

Solution

Check the following location, there are 2 duplicate apache configuration files with the same content

root@whiscardz:~/ufw/2020-07-17# less /etc/ufw/applications.d/apache2.2-common 
[Apache]
title=Web Server
description=Apache v2 is the next generation of the omnipresent Apache web server.
ports=80/tcp

[Apache Secure]
title=Web Server (HTTPS)
description=Apache v2 is the next generation of the omnipresent Apache web server.
ports=443/tcp

[Apache Full]
title=Web Server (HTTP,HTTPS)
description=Apache v2 is the next generation of the omnipresent Apache web server.
ports=80,443/tcp
root@whiscardz:~/ufw/2020-07-17# less /etc/ufw/applications.d/apache2-utils.ufw.profile 
[Apache]
title=Web Server
description=Apache v2 is the next generation of the omnipresent Apache web server.
ports=80/tcp

[Apache Secure]
title=Web Server (HTTPS)
description=Apache v2 is the next generation of the omnipresent Apache web server.
ports=443/tcp

[Apache Full]
title=Web Server (HTTP,HTTPS)
description=Apache v2 is the next generation of the omnipresent Apache web server.
ports=80,443/tcp

Now move one of the files to a specific location and now check the status of any ufw command:

root@whiscardz:~/ufw/2020-07-17# mv /etc/ufw/applications.d/apache2.2-common .
root@whiscardz:~/ufw/2020-07-17# ufw status verbose
Status: inactive

References

Unable to add firewall rule “Duplicate Profile”

Create readonly user postgres

Problem

I have a tool that only needs to connect to a postgres database to read data.

Solution

  • Connect to the database using the postgres super user

imela@whiscardz:~$ sudo -i -u postgres
postgres@whiscardz:~$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=#

  • Then create a user

postgres=# CREATE USER newuser WITH PASSWORD 'newuserpassword';
CREATE ROLE

  • Now connect using the databaseName user:

postgres@whiscardz:~$ psql -d databaseName -U postgres
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

databaseName=#

  • Now assign privileges to the user. This enables a user to login and then to select tables:

databaseName=# GRANT CONNECT ON DATABASE databaseName TO newuser;
GRANT
databaseName=# GRANT USAGE ON SCHEMA schemaName TO newuser;
GRANT
databaseName=# GRANT SELECT ON ALL TABLES IN SCHEMA schemaName TO newuser;
GRANT
databaseName=#

  • Now connect using the credentials from anothoer machine.

imela@whiscardz2:~$ psql newuser -h 192.168.2.2 -d databaseName
Password for user newuser:
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

databaseName=>

  • Now you can run your select query and see if you can be able to run a query:

databaseName=> select * from schemaName.system;

NB:
If you don’t include the schema that is if you select from just the table name then you will get the following error:

databaseName=> select * from system;
ERROR: relation "system" does not exist

References

https://w3resource.com/PostgreSQL/connect-to-postgresql-database.php
https://www.cyberciti.biz/faq/howto-add-postgresql-user-account/
https://riptutorial.com/postgresql/example/29300/create-read-only-user
https://stackoverflow.com/questions/2172569/how-do-i-login-and-authenticate-to-postgresql-after-a-fresh-install
https://stackoverflow.com/questions/6452956/how-to-create-a-user-with-readonly-privileges-for-all-databases-in-postgresql

Keep SSH connections Alive

Problem

I have a connection that keep timing out every few minutes

Solution

You can make changes on the server and on the client. I preferred to make the changes to the client since the server has more users and may not be not be desirable to make the changes across the board.

  • Create a ssh config file in you home .ssh directory e.g /home/whiscard/.ssh/config

$ vim .ssh/config

  • Add the server details in the file:

Host RemoteServer
HostName remote.server.org
Port 2211
User whiscard
ServerAliveInterval 60
ServerAliveCountMax 5

  • ServerAliveInterval: number of seconds that the client will wait before sending a null packet to the server (to keep the connection alive).
  • ServerAliveCountMax: If the client does not receive a response after five tries (as specified by the ServerAliveCountMax setting), it closes the connection
  • The Host value can be any name you want; it is simply a label for the other settings. The Hostname value is the remote host you want to access. Replace username with your own.
  • Save the file and make it only read and writeable to your user

$ chmod 600 .ssh/config

  • Connect to your account using SSH. To do this, simply type ssh RemoteServer where RemoteServer represents the Host value you specified above.

$ ssh RemoteServer

References

How to disable SSH timeout

How to keep SSH connections alive

Keep SSH session alive

VirtualBox is complaining that the kernel module is not loaded Ubuntu 18.04

Problem

Upgraded my ubuntu 18.04 and virtualbox stopped working. The following error was thrown:

The provider ‘virtualbox’ that was requested to back the machine
‘default’ is reporting that it isn’t usable on this system. The
reason is shown below:

VirtualBox is complaining that the kernel module is not loaded. Please
run `VBoxManage –version` or open the VirtualBox GUI to see the error
message which should contain instructions on how to fix this error.

Solution

  • Run VBoxManage –version

$ VBoxManage –version
WARNING: The vboxdrv kernel module is not loaded. Either there is no module
available for the current kernel (4.15.0-91-generic) or it failed to
load. Please recompile the kernel module and install it by

sudo /sbin/vboxconfig

You will not be able to start VMs until this problem is fixed.
6.1.4r136177

  • The recompile the virtualbox kernel module

$ sudo /sbin/vboxconfig
[sudo] password for jeremyo:
Sorry, try again.
[sudo] password for jeremyo:
vboxdrv.sh: Stopping VirtualBox services.
vboxdrv.sh: Starting VirtualBox services.
vboxdrv.sh: Building VirtualBox kernel modules.

 

vim search for ) replace with text then newline then )

Problem

I have a huge sql query with 574 lines and I needed to replace a closing bracket ) with text and then a new line with a closing bracket

Before:


) AS

After:


GROUP BY v.visit_id
) AS

Solution

Using vim’s search and replace feature


:%s/^)/GROUP BY v.visit_id\r)/gc

The above searches for the lines beginning with the closing bracket ) and then replaces it with text and then new line (\r) followed by a closing bracket )

References

How to replace a character by a newline in Vim

Add a newline after given patterns

delete gpg keys

Problem

My GPG Key Pair expired so needed to delete the existing one and replace with a new one

Solution

  • First list the gpg keys:

imela@whiscardz ~ $ gpg –list-keys
pub 4096R/5443F656 2018-09-06 [expired: 2019-09-06]
uid Whiscard imela (implementations encryption) <imela@whiscardz.org>

  • Delete the secret key first

imela@whiscardz ~ $ gpg –delete-secret-keys 5443F656
gpg (GnuPG) 1.4.20; Copyright (C) 2015 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

sec 4096R/5443F656 2018-09-06 Whiscard imela (implementations encryption) <imela@whiscardz.org>

Delete this key from the keyring? (y/N) y
This is a secret key! – really delete? (y/N) y

  • Then delete the public key

imela@whiscardz ~ $ gpg –delete-keys 5443F656
gpg (GnuPG) 1.4.20; Copyright (C) 2015 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

pub 4096R/5443F656 2018-09-06 Whiscard imela (implementations encryption) <imela@whiscardz.org>

Delete this key from the keyring? (y/N)y

References

https://serverfault.com/questions/214605/gpg-does-not-have-enough-entropy

https://www.howtoforge.com/helping-the-random-number-generator-to-gain-enough-entropy-with-rng-tools-debian-lenny

http://blog.chapagain.com.np/gpg-remove-keys-from-your-public-keyring/