Category Archives: sysadmin

Resetting the mysql password

Start server

$ sudo service mysql start

Go to sock folder

$ cd /var/run

Back up the sock

$ sudo cp -rp ./mysqld ./mysqld.bak

Stop server

$ sudo service mysql stop

Restore the sock

$ sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe

$ sudo mysqld_safe --skip-grant-tables --skip-networking &

Init mysql shell

mysql -u root

Change password

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';

Stop and stop mysql

sudo service mysql stop
sudo service mysql start

This is based on the combination of a few suggestions from https://stackoverflow.com/questions/41984956/cant-reset-root-password-with-skip-grant-tables-on-ubuntu-16

WSL boot shell script

I’ve created this script to help me get what I need running on my WSL Ubuntu VMs

I’m calling it ~/.mrc_boot_script and I also have it saved it to: https://gist.github.com/markcerv/7ba608b69bf6edf57db456187ad8a4ff

#If we made it in here, then that's a good thing

read -r -t 15 -p "Run the only on boot commands? [y/N] " response
response=${response,,}    # tolower

if [[ "$response" =~ ^(yes|y)$ ]]
then
    echo "All of these commands need sudo, so be prepared to enter in a password"
    sleep 2

    #Need to do this to get screens running cleanly
    echo "Screen cleanup"
    sudo /etc/init.d/screen-cleanup start

    #Let's also make sure postgres is running
    echo "Fire up postgresql"
    sudo service postgresql start

    #Let's also make sure mysql is running
    echo "Fire up mysql"
    sudo service mysql start

    #Let's also make sure ssh is running
    echo "Fire up ssh"
    sudo service ssh --full-restart

    #Let's also make sure redis is running (for celery)
    echo "Fire up redis-server"
    sudo service redis-server start
else
    echo "Doing nothing"
fi

Quick mysql commands

# configure mysql 1st time
mysql -u root --skip-password
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxx';
# create databases
mysqladmin -u root create wp_somename;

# now run mysql as root
mysql -u root mysql -p


CREATE USER 'example_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

CREATE USER 'example_user'@'localhost' IDENTIFIED BY 'password';  /* in case above doesn't work */



GRANT ALL ON example_database.* TO 'example_user'@'localhost';

flush privileges;

exit;

Heroku updates to python

If you see a message like this during one of your heroku builds/pushes, here’s what you should do:

remote: -----> Python app detected
remote:  !     Python has released a security update! Please consider upgrading to python-3.7.7

Take a look at what your runtime.txt file looks like. Mine looks like:

$ more runtime.txt
python-3.7.6

So, fire up your favorite editor and make it match.

Then commit that file, and push it back up to your repo and heroku.

$ git push heroku master
Counting objects: 3, done.
Delta compression using up to 8 threads.
Compressing objects: 100% (2/2), done.
Writing objects: 100% (3/3), 300 bytes | 12.00 KiB/s, done.
Total 3 (delta 1), reused 0 (delta 0)
remote: Compressing source files... done.
remote: Building source:
remote:
remote: -----> Python app detected
remote: -----> Found python-3.7.6, removing
remote: -----> No change in requirements detected, installing from cache
remote: -----> Installing python-3.7.7
remote: -----> Installing pip
remote: -----> Installing dependencies with Pipenv 2018.5.18…
remote:        Installing dependencies from Pipfile.lock (a33c91)…

Certbot: WWW and non-WWW certificates

Certbot is an awesome way to make sure your websites are secured with encryption and HTTPS.

It’s considered a best practice to have only 1 canonical version of a URL. But when you do that, you can run into issues where you get certificate mis-match errors. Here is an easy way to fix/prevent that.

sudo certbot certonly  -d originaldomain.com -d www.originaldomain.com

However, if you forgot do add in the www the first time, or if you want to create other subdomains that might answer on the same virtualhost, you’ll need to expand your original certificate.

--expand tells Certbot to update an existing certificate with a new certificate that contains all of the old domains and one or more additional new domains.

sudo certbot certonly --expand -d originaldomain.com -d www.originaldomain.com -d new.originaldomain.com -d new2.originaldomain.com -d new3.originaldomain.com --dry-run

NOTE: use the –dry-run flag for testing if it gives you the correct output without errors then re-run the same command and remove –dry-run from the end.

Another instance of Certbot is already running

I’ve written before about Certbot and how wonderful it is. Recently I’ve come across a situation where certbot renewals aren’t going thru:

root@host:~# certbot renew --force-renewal
Another instance of Certbot is already running.

root@host:~# ps waux | grep certbot
root     20947  0.0  0.0   4500   744 ?        Ss   16:52   0:00 /bin/sh -c /usr/local/sbin/certbot-renew.sh
root     20949  0.0  0.0   4500   736 ?        S    16:52   0:00 /bin/sh /usr/local/sbin/certbot-renew.sh
root     20953  0.3  4.4 148704 45440 ?        S    16:52   0:00 /usr/bin/python3 /usr/bin/certbot renew --post-hook touch /var/lib/letsencrypt/updated
root     21049  0.0  0.0  12940  1012 pts/4    S+   16:54   0:00 grep --color=auto certbot

root@host:~# certbot --version
certbot 0.31.0

If I check the logfile /var/log/letsencrypt/letsencrypt.log I can see that it keeps on trying and trying to do renewals.

grep "random delay" /var/log/letsencrypt/letsencrypt.log

2020-04-14 16:16:02,526:INFO:certbot.renewal:Non-interactive renewal: random delay of 5 seconds
2020-04-14 16:17:02,135:INFO:certbot.renewal:Non-interactive renewal: random delay of 409 seconds
2020-04-14 16:24:01,684:INFO:certbot.renewal:Non-interactive renewal: random delay of 290 seconds
2020-04-14 16:29:01,744:INFO:certbot.renewal:Non-interactive renewal: random delay of 190 seconds
2020-04-14 16:33:02,261:INFO:certbot.renewal:Non-interactive renewal: random delay of 283 seconds
2020-04-14 16:38:01,813:INFO:certbot.renewal:Non-interactive renewal: random delay of 350 seconds
2020-04-14 16:44:01,613:INFO:certbot.renewal:Non-interactive renewal: random delay of 117 seconds
2020-04-14 16:46:02,187:INFO:certbot.renewal:Non-interactive renewal: random delay of 309 seconds
2020-04-14 16:52:01,834:INFO:certbot.renewal:Non-interactive renewal: random delay of 453 seconds

Here’s what I do next:

  1. find any running certbot processes.
  2. kill them (with -9) one by one
  3. find and lock files
  4. remove them
root@host:~# ps waux | grep cert
root     20947  0.0  0.0   4500   744 ?        Ss   16:52   0:00 /bin/sh -c /usr/local/sbin/certbot-renew.sh
root     20949  0.0  0.0   4500   736 ?        S    16:52   0:00 /bin/sh /usr/local/sbin/certbot-renew.sh
root     20953  0.1  4.4 148704 45440 ?        S    16:52   0:00 /usr/bin/python3 /usr/bin/certbot renew --post-hook touch /var/lib/letsencrypt/updated
root     21152  0.0  0.0  12940   936 pts/4    S+   16:57   0:00 grep --color=auto cert

root@host:~# kill -9 20947
root@host:~# kill -9 20949
root@host:~# kill -9 20953
root@host:~# find / -type f -name ".certbot.lock"
/var/lib/letsencrypt/.certbot.lock
/var/log/letsencrypt/.certbot.lock
/etc/letsencrypt/.certbot.lock

root@host:~# rm /var/lib/letsencrypt/.certbot.lock  /var/log/letsencrypt/.certbot.lock /etc/letsencrypt/.certbot.lock

Next, I want to uninstall it.

root@host:~# apt list --installed | grep certbot

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

certbot/xenial,now 0.31.0-1+ubuntu16.04.1+certbot+1 all [installed]
python3-acme/xenial,now 0.31.0-2+ubuntu16.04.6+certbot+2 all [installed,automatic]
python3-asn1crypto/xenial,now 0.22.0-2+ubuntu16.04.1+certbot+1 all [installed,automatic]

Next, I want to uninstall it:

root@host:~#  apt-get remove certbot
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  python3-acme python3-certbot python3-configargparse python3-funcsigs python3-future python3-icu python3-josepy
  python3-mock python3-parsedatetime python3-pbr python3-requests-toolbelt python3-rfc3339 python3-tz
  python3-zope.component python3-zope.event python3-zope.hookable python3-zope.interface
Use 'apt autoremove' to remove them.
The following packages will be REMOVED:
  certbot
0 upgraded, 0 newly installed, 1 to remove and 9 not upgraded.
After this operation, 39.9 kB disk space will be freed.
Do you want to continue? [Y/n]

Type in Y to continue, then after that, get rid of code you no longer need

root@mhs02:~#  apt-get remove certbot
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  python3-acme python3-certbot python3-configargparse python3-funcsigs python3-future python3-icu python3-josepy
  python3-mock python3-parsedatetime python3-pbr python3-requests-toolbelt python3-rfc3339 python3-tz
  python3-zope.component python3-zope.event python3-zope.hookable python3-zope.interface
Use 'apt autoremove' to remove them.
The following packages will be REMOVED:
  certbot
0 upgraded, 0 newly installed, 1 to remove and 9 not upgraded.
After this operation, 39.9 kB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 158315 files and directories currently installed.)
Removing certbot (0.31.0-1+ubuntu16.04.1+certbot+1) ...
Processing triggers for man-db (2.7.5-1) ...
root@host:~# apt autoremove
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
  python3-acme python3-certbot python3-configargparse python3-funcsigs python3-future python3-icu python3-josepy
  python3-mock python3-parsedatetime python3-pbr python3-requests-toolbelt python3-rfc3339 python3-tz
  python3-zope.component python3-zope.event python3-zope.hookable python3-zope.interface
0 upgraded, 0 newly installed, 17 to remove and 9 not upgraded.
After this operation, 6,352 kB disk space will be freed.
Do you want to continue? [Y/n] y
(Reading database ... 158306 files and directories currently installed.)
Removing python3-certbot (0.31.0-1+ubuntu16.04.1+certbot+1) ...
Removing python3-acme (0.31.0-2+ubuntu16.04.6+certbot+2) ...
Removing python3-configargparse (0.11.0-1+certbot~xenial+1) ...
Removing python3-mock (1.3.0-2.1ubuntu1) ...
Removing python3-funcsigs (0.4-2) ...
Removing python3-parsedatetime (2.4-3+ubuntu16.04.1+certbot+3) ...
Removing python3-future (0.15.2-4+ubuntu16.04.1+certbot+3) ...
Removing python3-icu (1.9.2-2build1) ...
Removing python3-josepy (1.1.0-2+ubuntu16.04.1+certbot+1) ...
Removing python3-pbr (1.8.0-4ubuntu1) ...
Removing python3-requests-toolbelt (0.8.0-1+ubuntu16.04.1+certbot+1) ...
Removing python3-rfc3339 (1.0-4+certbot~xenial+1) ...
Removing python3-tz (2014.10~dfsg1-0ubuntu2) ...
Removing python3-zope.component (4.3.0-1+ubuntu16.04.1+certbot+3) ...
Removing python3-zope.event (4.2.0-1) ...
Removing python3-zope.hookable (4.0.4-4+ubuntu16.04.1+certbot+1) ...
Removing python3-zope.interface (4.3.2-1+ubuntu16.04.1+certbot+1) ...

Check to see if that removed the process

root@host:~# ps waux | grep cert
root     21304  0.0  0.0   4500   748 ?        Ss   17:01   0:00 /bin/sh -c /usr/local/sbin/certbot-renew.sh
root     21306  0.0  0.0   4500   776 ?        S    17:01   0:00 /bin/sh /usr/local/sbin/certbot-renew.sh
root     21310  0.2  4.7 157436 48228 ?        S    17:01   0:00 /usr/bin/python3 /usr/bin/certbot renew --post-hook touch /var/lib/letsencrypt/updated
root     22017  0.0  0.1  12940  1092 pts/4    S+   17:08   0:00 grep --color=auto cert
root@host:~# ls /usr/local/sbin
certbot-renew.sh

root@host:~#  mv /usr/local/sbin/certbot-renew.sh   /usr/local/sbin/certbot-renew.sh.hide

Triple check nothings running

root@mhs02:~# ps waux | grep cert
root     22118  0.0  0.1  12940  1020 pts/4    S+   17:09   0:00 grep --color=auto cert

Follow the instructions at:
https://certbot.eff.org/instructions on how to install certbot for your installation.

sudo apt-get install certbot python-certbot-apache

Finally, let’s check the server uptime, check the expiration date of a site running on this server, gracefully restart apache, then check the expiration date again:

root@host:~# apachectl status | grep uptime
   Server uptime: 11 days 22 hours 45 minutes 34 seconds

root@host:~# echo | openssl s_client -servername www.example.org -connect www.example.org:443 2>/dev/null | openssl x509 -noout -dates
notBefore=Mar  3 16:27:55 2020 GMT
notAfter=Jun  1 16:27:55 2020 GMT

root@host:~# apachectl graceful
[Tue Apr 14 17:17:24.937566 2020] [so:warn] [pid 26644] AH01574: module security2_module is already loaded, skipping

root@host:~# apachectl status | egrep 'uptime|Restart'
   Restart Time: Thursday, 02-Apr-2020 18:29:10 UTC
   Server uptime: 11 days 22 hours 52 minutes 3 seconds


root@host:~# echo | openssl s_client -servername www.example.org -connect www.example.org:443 2>/dev/null | openssl x509 -noout -dates
notBefore=Apr 14 16:13:15 2020 GMT
notAfter=Jul 13 16:13:15 2020 GMT

Success! We can see that the new renewal dates are in place.

To make sure that renewals happen automatically, I like to have this crontab in place:

# Let's keep let's encrypt ssl certs up to date.
31 3,15 * * * /usr/bin/certbot renew --quiet

Whose IP address is it?

When looking at logs (web, email, ssh) at you don’t recognize an IP address, what are some easy ways to find out if it’s a “friendly” IP address (someone using Comcast wifi from different locations) versus a hacker from another country.

http://whois.arin.net/ui/ – Look up who owns the bigger netblock

https://search.arin.net/rdap/ – More useful for finding out where in the world that subnet might be

Certbot renewal script

By now you know that your website needs to run over HTTPS. One of the easiest and cheapest ways to do this is by using
https://certbot.eff.org/ which in turn deploys https://letsencrypt.org/ certificates.

Once you have it installed, you’ll want to install a crontab entry that will run the updates 2x per day:

23 3,15 * * * /usr/local/sbin/certbot-renew.sh
#!/bin/sh
#
# Save this file as /usr/local/sbin/certbot-renew.sh
#
UPDATE_FLAG_FILE=/var/lib/letsencrypt/updated

rm -f $UPDATE_FLAG_FILE
/usr/local/bin/certbot-auto renew --post-hook "touch $UPDATE_FLAG_FILE"

if [ $? -gt 0 ]; then
  exit $?
fi

if [ ! -f $UPDATE_FLAG_FILE ]; then
  exit 0
fi


service apache2 status

if [ $? -eq 0 ]; then
  service apache2 reload
  if [ $? -gt 0 ]; then
    >&2 echo failed to reload apache2
    exit 1
  fi
fi

Limiting Access with SFTP Jails on Debian and Ubuntu

(taken from: Linode guide to Limiting Access with SFTP Jails on Debian and Ubuntu

As the system administrator for your Linode, you may want to give your users the ability to securely upload files to your server. The most common way to do this is to allow file transfers via Secure File Transfer Protocol (SFTP), which uses SSH to provide encryption. This requires that you give your users SSH logins. However, by default SSH users are able to view your Linode’s entire filesystem, which may not be desirable.

Limiting Access with SFTP Jails on Debian and Ubuntu

This guide will help you configure OpenSSH to restrict users to their home directories, and to SFTP access only. Please note that these instructions are not intended to support shell logins; any user accounts modified in accordance with this guide will have the ability to transfer files, but not the ability to log into a remote shell session.

These instructions will work for Ubuntu 9.04, Debian 5, and later. Unfortunately, the version of SSH packaged with Ubuntu 8.04 is too old to support this configuration.

Configure OpenSSH

  1. Edit your /etc/ssh/sshd_config file with your favorite text editor:

    vim /etc/ssh/sshd_config
    
  2. Add or modify the Subsystem sftp line to look like the following:

    /etc/ssh/sshd_config
    1
    
    Subsystem sftp internal-sftp
  3. Add this block of settings to the end of the file:

    /etc/ssh/sshd_config
    1
    2
    3
    4
    5
    
    Match Group filetransfer
        ChrootDirectory %h
        X11Forwarding no
        AllowTcpForwarding no
        ForceCommand internal-sftp

    Save the changes to your file.

  4. Restart OpenSSH:

    service ssh restart
    

    OpenSSH has been successfully modified.

Modify User Accounts

This section will set up the correct groups, ownership, and permissions for your user accounts.

  1. Create a system group for users whom you want to restrict to SFTP access:

    addgroup --system filetransfer
    
  2. Modify the user accounts that you wish to restrict to SFTP. Issue the following commands for each account, substituting the appropriate username. Please keep in mind that this will prevent these users from being able to log into a remote shell session.

    usermod -G filetransfer username
    chown root:root /home/username
    chmod 755 /home/username
    

    These users will now be unable to create files in their home directories, since these directories are owned by the root user.

  3. Next, you need to create new directories for each user, to which they will have full access. Issue the following commands for each user, changing the directories created to suit your needs:

    cd /home/username
    mkdir docs public_html
    chown username:filetransfer *
    

    Your users should now be able to log into their accounts via SFTP and transfer files to and from their assigned subdirectories, but they shouldn’t be able to see the rest of your Linode’s filesystem.

Use SFTP

  1. Use sftp from the terminal:

    sftp username@<Your_Linodes_IP>
    

    You can use the help command to see what commands you have access too within the SFTP shell. You have the ability to pwd, cd and ls, for instance. There are also commands like lpwd, that will print the local working directory. In the local home directory type touch test.txt

  2. Transfer local files to the remote system:

    cd docs
    put test.txt
    
  3. Transfer files to the local system from the remote system:

    get test.txt
    
  4. You can test the file permissions by navigating to a different directory within the SFTP shell, and trying to transfer a file.

    sftp> put test.txt /tmp/
    Uploading test.txt to /tmp/
    remote open("/tmp/"): Failure
    
  5. Exit the session with the exit command.