Ubuntu: Disable Automatic Updates

UPDATE: What outlined below did not work, so here is what I ended up doing:

Change directory to:

$ cd /etc/apt/apt.conf.d

Edit the 20auto-upgrades, and change the Lists setting from 1 to 0:

$ sudo vi 20auto-upgrades
APT::Periodic::Update-Package-Lists “0”;
APT::Periodic::Unattended-Upgrade “1”;

Now, with the list disabled the service should never find anything to update.

Another option is just get rid of the service:

$ sudo apt remove unattended-upgrades

Note: The following did not work in Ubuntu 18.x:

For reason, I don’t know if this an Azure deployment issue or an Ubuntu decision, but I have found automatic updates turned on by default in a couple VMs. Not sure why you would ever want your production server automatically updating, so:

Check to status of the unattended-upgrades service:

$ sudo systemctl status unattended-upgrades

Stop the service:

$ sudo systemctl stop unattended-upgrades

Disable the service. NOTE: Services that have this as a dependency will start the service despite it being disabled. I am not aware of any for this service, but just putting it out there.

$ sudo systemctl disable unattended-upgrades

To prevent the unintended situation above, mask will create a link to /dev/null to prevent the service from being started whether there are dependencies or not.

$ sudo systemctl mask unattended-upgrades

SQLite3 and OpenVPN

I needed to cleanup an issue with an OpenVPN user. I cleaned was using a multi-factor solution with OpenVPN. The multi-factor solution was case sensitive, while the VPN was not. To clean up the mess, I needed to remove the certificates from the certs SQLite database.

Database location:

# cd /usr/local/openvpn_as/etc/db

Connect to the database:

# sqlite3 certs.db

List the tables in the database:

sqlite> .tables

List column names in the table:
sqlite> pragma table_info(certificates);

List all the common_name records in the database:
sqlite> select common_name from certificates ;

Delete records with the common name somename:
sqlite> delete from certificates where common_name=’somename‘;

This worked well to clean up the database, and get the user working with a correct ovpn file.

Note: You could also edit the ovpn with data from the database as well.

Here is how you display a table (log in this instance) layout:

sqlite> .schema log

I used the following to query the login activity. You have to convert the timestamp in the start_time column. Also, the duration is stored in seconds, so I converted it to minutes.

sqlite> select username,strftime(‘%m-%d-%Y’, datetime(start_time, ‘unixepoch’)),duration/60 from log;

To run it from a script, save your query in a file (zreport) and run the following:

sqlite3 log.db < zreport

Docker Basics

OS: CentOS 7
Install Docker:

# yum install docker-ce-cli docker-ce

Start Docker:

# systemctl start docker

Help:

# docker –help

You can tack on a –help on the end of any command to get help of that specific option:

# docker pull –help

Pull in an image from hub.docker.com:

# docker pull ubuntu

To list your installed images and obtain the IMAGE ID:

# docker images

To run an image:

# docker run -it -d ubuntu

To view running images:

# docker ps

To stop a running container:

# docker stop ‘CONTAINTER ID

To execute a command in a container (In this case, bash in the ubuntu image.):

# docker exec -it ‘CONTAINTER ID‘ bash

To view all container, including those that are not running:

# docker ps -a

To remove a container:

# docker rm ‘CONTAINTER ID

To remove an image:
# docker rmi ‘IMAGE ID

To stop and remove a running container:

# docker rm -f ‘CONTAINTER ID

To save changes to a container:

# docker commit ‘CONTAINTER ID‘ ‘NEW IMAGE NAME

To remove all running containers:

# docker rm -f $(docker ps -a -q)

NOTE: INSTANCE ID can refer an image or container. The next two command will return the information in json format.
To view a container’s history:

# docker history ‘INSTANCE ID

To view information about a container:

# docker inspect ‘INSTANCE ID

If you want to upload your container to hub.docker.com the container must have a name in the following format:

# docker commit ‘CONTAINTER ID‘ ‘dockerhubuserid’/’newimagename’

To run a container and map a host port to the container port:

# docker run -it -d -p 82:80 jgz/apache2

Then, in this case, I would access my container web server from my LAN by accessing my docker host name and the port 82. So, if my hostname is jims.domain.local, the I would browse to http://jims.domain.local:82.

A Dockerfile is how you can create a container and run some commands when you start it up:
Building a Dockerfile (YAML file):

# mkdir /usr/local/docker
# cd /usr/local/docker/
# vi Dockerfile

FROM ubuntu
RUN apt-get update
RUN apt-get -y install apache2
ADD . /var/www/html
ENTRYPOINT apachectl -D FOREGROUND
ENV name jgz

This will build the container and execute the RUN commands and ENTRYPOINT command:

# docker build . -t new_dockerfile

Ubuntu/Mint Allow Non-Privileged User To Connect To New WiFi SSID

# cd /usr/share/polkit-1/actions/

Make a backup copy of the org.freedesktop.NetworkManager.policy file:

# cp -p org.freedesktop.NetworkManager.policy org.freedesktop.NetworkManager.policy.orig

Edit the org.freedesktop.NetworkManager.policy file:

# vi org.freedesktop.NetworkManager.policy

In the <action id=”org.freedesktop.NetworkManager.settings.modify.system”> section, look for
<allow_active>auth_admin_keep</allow_active> toward the end of the section.

Change that line to:
<allow_active>yes</allow_active>

# shutdown -r now

Windows 10 (post October 2018 Update) RSAT

As usual with Microsoft, it seems that have come up with a “better” idea for how to install the Remote Server Administration Tools for Windows 10 versions later than the October 2018 Update.

In the search, enter “Manage optional features” and click “Add a feature.”

And then the brilliant engineers at Microsoft, thought it would be so much better if we had install each component one at a time. So, pick and chose at your will. Click an RSAT option and then click Install. At least, they will queue. To get a status, just go back to the previous screen.

You need to reboot once you have all the features installed.

To install (or reinstall) the ActiveDirectory module, I think the only feature required it the “RSAT: Active Directory Domain Services and Lightweight Directory Services Tools”. It was not in “RSAT: Server Manager.”

Then, from a administrator Powershell windows, install the module:
PS> Install-Module ActiveDirectory

I had to re-install RSAT and the ActiveDirectory module after update to version 1909.

I have yet to come across the powershell equivalent to just install the tools. I will update this if I find it.

This is the best way to install the powershell ActiveDirectory module, because it actually works:

$JGZSession = New-PSSession -ComputerName domaincontroller
Export-PSsession -Session $JGZSession -Module ActiveDirectory -OutputModule RSATADModule
Remove-PSSession -Session $JGZSession
Import-Module RSATADModule

Query Microsoft SQL from CentOS7

Install the Microsoft repository into your yum configuration:

# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

Disable the repository:

vi /etc/yum.repos.d/mssql-release.repo

enabled=0

Remove the unixODBC packages if applicable:

# yum remove unixODBC-utf16 unixODBC-utf16-devel

Install the driver and command line tools (if wanted):

# yum –enablerepo packages-microsoft-com-prod install msodbcsql17
# yum –enablerepo packages-microsoft-com-prod install mssql-tools

Add the tools directory to your PATH variable as required:

vi ~/.bash_profile ~/.bashrc

export PATH=”$PATH:/opt/mssql-tools/bin”
..

Add the tools to your current session:

export PATH=”$PATH:/opt/mssql-tools/bin”

Test with sqlcmd:

sqlcmd -U username -P password -S server -d database

Email Subject decoding using base64 in linux

I did a little exercise to figure out a way to decode the encoded subject of some email messages.

Here is the decoded subject I was using:

Alaska is cool, go whale watching and glacier gazing (from 75%-off)

Here is the fully encoded subject of that message:

=?UTF-8?B?QWxhc2thIGlzIGNvb2wsIGdvIHdoYWxlIHdhdGNoaW5nIGFuZCBnbGFjaWVyIGdhemluZyAoZnJvbSA3NSUtb2ZmKQ==?=

Here is the command I used to decode the string:

$ echo QWxhc2thIGlzIGNvb2wsIGdvIHdoYWxlIHdhdGNoaW5nIGFuZCBnbGFjaWVyIGdhemluZyAoZnJvbSA3NSUtb2ZmKQ== | base64 -d
Alaska is cool, go whale watching and glacier gazing (from 75%-off)

Note: the leading and trailing did need to be stripped off for this to work successfully. It is perfectly within the standards to encode the email subjects like this. Unfortunately, spammers have known this for many years.

MSSQL Notes

To determine a view definition:

SELECT DEFINITION FROM sys.sql_modules WHERE object_id = OBJECT_ID(‘viewschema.viewname‘)

To list all the tables in a database:

SELECT * FROM INFORMATION_SCHEMA.TABLES

To list all the columns and tables in a database:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

OUTER JOIN example:

SELECT xx.column1, yy.column2
FROM table1 xx
LEFT OUTER JOIN DB2.schema1.table2 yy ON xx.column1 = yy.column2

Change the time format:
Some CONVERT statements to the time format from DATETIME to something else:

DECLARE @SOMETIME DATETIME
SET @SOMETIME = ‘2020-01-31 11:00:00 AM’
SELECT CONVERT(VARCHAR(20), @SOMETIME, 22)
01/31/20 11:00:00 AM

This one has the four digit year:

DECLARE @SOMETIME DATETIME
SET @SOMETIME = ‘2020-01-31 11:00:00 AM’
SELECT CONVERT(VARCHAR(20), DATEADD(Hour, -8, @RECEIPTDATE), 101) + ‘ ‘ + CONVERT(VARCHAR(20), DATEADD(Hour, -8, @RECEIPTDATE), 8)
01/31/2020 11:00:00

Selecting records based on time example:
This will returns records (in this case just a bunch of time variables), if the the @SOMEDATETIME variable is between now and one hour ago:

DECLARE @SOMEDATETIME DATETIME
DECLARE @CURRENTDATETIME DATETIME
DECLARE @CURRENTDATETIMEMINUS DATETIME
SET @SOMEDATETIME = ‘2020-01-31 15:30:00.000’
SET @CURRENTDATETIME = CONVERT(VARCHAR(20), GETDATE(), 101) + ‘ ‘ + CONVERT(VARCHAR(20), GETDATE(), 8)
SET @CURRENTDATETIMEMINUS = CONVERT(VARCHAR(20), DATEADD(Hour, -1, @CURRENTDATETIME), 101) + ‘ ‘ + CONVERT(VARCHAR(20), DATEADD(Hour, -1, @CURRENTDATETIME), 8)

SELECT @SOMEDATETIME AS SOMEDATETIME
, @CURRENTDATETIMEMINUS AS CURRENTDATETIMEMINUS
, @CURRENTDATETIME AS CURRENTDATETIME
WHERE @SOMEDATETIME
BETWEEN @CURRENTDATETIMEMINUS AND @CURRENTDATETIME

SPF Verification in Postfix

OS: CentOS7

You should have your TXT record set in your DNS prior to implementing this on your server:
“v=spf1 mx ip4:aaa.bbb.ccc.ddd ip4:eee.fff.ggg.hhh -all”

Here is link I found useful to understand the options for the DNS record:
https://support.dnsimple.com/articles/spf-record/

You need to have the EPEL repository enabled to install the pypolicyd-spf package using the methodology I have outlined here.

To install and enable the EPEL repository:

# yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm

Install the package Python package:

# yum install pypolicyd-spf

Modify the main.cf to add SPF verifcation to postix:
The master.cf and main.cf need to modified:

# cd /etc/postfix/
# cp -p master.cf master.cf.20190716
# vi master.cf

policy unix – n n – 0 spawn user=nobody argv=/bin/python /usr/libexec/postfix/policyd-spf

# cp -p main.cf main.cf.20190716
# vi main.cf

smtpd_recipient_restrictions =
permit_sasl_authenticated,
reject_unauth_destination
check_policy_service unix:private/policy

Note: the policy defined in the master.cf does not need to be named “policy”. However, if you change that, you need to also change it in your “check_policy_service” statement in the main.cf.

Restart postfix to implement changes:
# systemctl restart postfix

Adding a static routes in MacOS Mojave

To add a permanent static route in MacOS Mojave, open up a terminal session.

List all of your available network services. Services really just seem to be aliases for network interfaces.

$ sudo networksetup -listallnetworkservices
An asterisk (*) denotes that a network service is disabled.
USB-Serial Controller D
iPad USB
iPhone USB
Wi-Fi
Bluetooth PAN
Thunderbolt Bridge

You will be adding the route to a service, therefore, you will need to know which service to apply the route to.

In my case, I wanted to add the route to my wireless interface:

$ sudo networksetup -setadditionalroutes Wi-Fi network-destination destination-mask gateway-to-use
$ sudo networksetup -setadditionalroutes Wi-Fi 10.1.1.0 255.255.255.0 192.168.0.254

List all of your current routes:

$ sudo networksetup -getadditionalroutes Wi-Fi
10.1.1.0 255.255.255.0 192.168.0.254

To remove static routes from a network service just remove the parameters from the command you used to set the route:

$ sudo networksetup -setadditionalroutes Wi-Fi

Return top

INFORMATION