configure postfix to send mail using gmail or PHP alternative, send email for low disk space alert

Found this article describing all the steps needed to make postfix send emails via Google's SMTP servers,
https://www.linode.com/docs/guides/configure-postfix-to-send-mail-using-gmail-and-google-workspace-on-debian-or-ubuntu/
or
https://devanswers.co/configure-postfix-to-use-gmail-smtp-on-ubuntu-16-04-digitalocean-droplet/

But the implementation on our server used the simpler PHPMailer method.
https://phppot.com/php/send-email-in-php-using-gmail-smtp/

One of the use cases was to alert when disk space reaches critically low levels - https://stackoverflow.com/questions/60264983/email-notification-when-disk-drive-reaches-a-certain-percentage-laravel

caution with Moodle VMSS

The Azure VMSS (Virtual Machine Scale Set) Moodle deployment in combination with automated site backups has to be used with caution. The object storage filesystem instructs the administrator to create SAS keys with only read and write permissions, and it looks like this would cause the moodle scripts which try to do deletes to fail. So, in our case, when automatic site backups were enabled, blob storage usage began to balloon.

Also, occasionally, the backup would cause some issue due to which the object storage filesystem would stop writing to blob storage and would instead fill up the local hard disk, causing moodle to complain "session partition full". One quick check and the first place to delete would be the temp directory, especially if backup is turned on. 

Even after running some scripts (a bash script is mentioned in this forum thread) and deleting all the unnecessary backups and orphaned files, blob capacity did not come down, only container size came down. So, my brute-force solution was to avoid blob storage and use local hard disk storage instead. 

Chrome blocking http links

Google Chrome browser now blocks http links from pages served over https. Even if the http linked pages have https redirect enabled, Chrome continues to block. Only way out would be to replace all http links with https links. 

HTTP redirect was with the rule which was created by certbot etc,

RewriteEngine on
RewriteCond %{SERVER_NAME} =our.server.name
RewriteRule ^ https://%{SERVER_NAME}%{REQUEST_URI} [END,NE,R=permanent]
</VirtualHost>

backing up to Google drive with rclone

Backing up from a server to google drive using rclone directly on the server, using screen so that I don't have to be logged in  for the entire multi-hour process - 1 GB takes around 2 hours. So it will take another couple of days for the process to be completed.

Commands were like

rclone copy srcDirectory gdrivearchivercloneremotename:"backupDir/destDirectory"

and so on. 

Example output:

Transferred:        7.493G / 7.493 GBytes, 100%, 396.555 kBytes/s, ETA 0s
Transferred:        46441 / 46441, 100%
Elapsed time:   5h30m20.6s

finding which files are causing disk to get full from linux command line

One of our servers ran out of disk space, so in order to diagnose the issue from the terminal, used the technique mentioned in this askubuntu post, that is,
du -cha --max-depth=1 / | grep -E "M|G"

Then, to reduce log file usage by journald, as this thread says,

edit /etc/systemd/journald.conf
Activate the SystemMaxUse= option there, e.g. as SystemMaxUse=100M to only use 100 MB.
service systemd-journald restart to activate the changed configuration.

Then, to clear up emails for root from /var/mail - they are mostly cron emails - various techniques discussed here, easiest would be to just empty it - but the safest way to do it might be using the mail command, 
sudo su -
mail
delete *
q
which immediately cleared up all the root emails.
(By default, if you press enter and read a mail, it gets saved to an mbox file in that user's home directory, so in this case, /root/mbox )

Then, redirecting the cron job emails to /dev/null to prevent this from happening again, by appending
> /dev/null 2>&1
to all those cron jobs. 

 

adding SSL (https) to http mp3 streams - icecast-kh

The earlier reverse-proxy solution had hiccups when thousands of listeners connected. So, explored directly using HTTPS on icecast. Then PB discovered icecast-kh which has improved SSL certificate handling, without needing a restart when the certificate is updated - 

an interesting fork of icecast2 called icecast-kh

https://github.com/karlheyes/icecast-kh

https://github.com/xiph/Icecast-Server/issues/20

https://github.com/AzuraCast/AzuraCast/issues/358 says the following:

8/5/2017

--autodetect SSL connections on incoming sockets. No need for in listen-socket now but is still there for compatability. (THIS IS BIG!!! We no longer need to use different ports for encrypted and unencrypted dramatically reducing complexity)

--add ssl-private-key in paths to allow for combined PEM or for separate SSL key/certificate files. (THIS IS BIG TOO! No longer need a separate process after updating let's encrypt to combine your fullchain and private cert into one pem file for icecast to read it!)

--select https/http URL in autogenerated m3u based on incoming request.

So, this was what was implemented, with a LetsEncrypt certificate. Compiled with

 $ cd <directory where you have extracted the tar.gz>
 $ ./configure --prefix=/home/ouruser/icecastkhSSL
 $ make
 $ make install

 The SSL certificate is updated through change_ssl_certificate.sh

cat /etc/ssl/private/ourserver.tld.key   /root/.acme.sh/ourserver.tld/fullchain.cer > /path/to/icecast.pem.letsencrypt
chown ouruser:ouruser /path/to/icecast.pem.letsencrypt
cp /path/to/icecast.pem   /path/to/icecast.pem.backup
#echo "Backed up icecast.pem to icecast.pem.backup..."
cp /path/to/icecast.pem.letsencrypt    /path/to/icecast.pem

Cron is run as root every day after acme script

43 0 * * * "/root/.acme.sh"/acme.sh --cron --home "/root/.acme.sh" > /dev/null

50 0 * * * /home/path/to/scripts/change_ssl_certificate.sh > /dev/null

server upgrade, certificates

We planned a streaming server upgrade. During the upgrade process, we would transfer the DNS to our web server, making it the temporary streaming server. 

According to one of the replies at

https://community.letsencrypt.org/t/how-to-set-up-lets-encrypt-https-after-moving-site-to-new-server/114776/2

the easiest way for temporary certificate transfer would be to just copy the certificates and icecast proxy server executable from our streaming server to our web server.

So,

1. copy the certificates and 2nd icecast server to the web server

2. check that the web server returns the correct certificate by locally setting /etc/hosts file DNS record of our stream url to the web server's IP address

3. on cloudflare, change the IP address from the streaming ip address to the web server's ip address for the stream url A record

After the upgrade is done, again do the steps 1, 2 and 3 for going from the web server to the streaming server. If everything goes well, the certificates and executable will remain on on the streaming server, and we will not need to copy them back. 

making GMail the default mail handler

Opening mailto links in GMail - 
https://developers.google.com/web/updates/2012/02/Getting-Gmail-to-handle-all-mailto-links-with-registerProtocolHandler

(Not sure if this 2012 technique still works for Firefox and Chrome).

Firefox has a support page for this -
https://support.mozilla.org/en-US/kb/change-program-used-open-email-links

As also Chrome - 
https://support.google.com/a/users/answer/9308783?hl=en

conditional formatting in Google sheets

Highlighting cells in Google sheets when they fulfil certain conditions - 
https://stackoverflow.com/questions/21899516/how-to-highlight-cell-if-value-duplicate-in-same-column-for-google-spreadsheet

Opening a pdf as a Google Document using google apps script

This link has the steps to make Google apps script do OCR on the PDF - 

const blob = DriveApp.getFileById(fileID).getBlob();

  const resource = {

    title: blob.getName(),

    mimeType: blob.getContentType()

  };

  const options = {

    ocr: true,

    ocrLanguage: "en"

  };

  // Convert the pdf to a Google Doc with ocr.

  const file = Drive.Files.insert(resource, blob, options);

But this generally gave pretty terrible results for me, and the formatting was completely lost.  

For my use case, found that the PDF was being created from HTML, so direct conversion from HTML to GDoc gave good results - 

assethtml += contentdata;
var ablob = Utilities.newBlob(assethtml, MimeType.HTML, "asset.html");
var AssetGDocId = Drive.Files.insert(
      { title: 'The name of the document', 
      mimeType: MimeType.GOOGLE_DOCS, parents: [{"id": destFolderID}] },
      ablob ).id;

appending Google docs and adding header and footer

In the current implementation, the adding of header and footer is done by making a copy of an existing template doc which has the required header and footer, and then appending the required contents inside it. 

Unfortunately, with the (slow) template method, Google Apps script seems to have difficulty with importing more than 2-3 chapters at a time, where each chapter consists of around 10-15 Google Docs which need to be concatenated. Each chapter takes 5 to 10 minutes, and we reach the limits of processing time + parsecsv's max char limit. 

https://tanaikech.github.io/2019/08/28/benchmark-importing-csv-data-to-spreadsheet-using-google-apps-script/

The appending-Google-doc code is from 
https://stackoverflow.com/questions/51252160/can-i-copy-the-contents-of-one-doc-to-a-specific-spot-in-another-doc-google-ap

glitch.com for building web apps quickly

An interesting service to build Node.js web apps by "remixing" available code - 

https://blog.glitch.com/post/google-docs-markdown-glitch

https://help.glitch.com/kb/article/17-what-are-the-technical-restrictions-for-glitch-projects/ - 1000 free project hours per month, 200 MB disk space, 4000 requests per hour.

https://flaviocopes.com/glitch/ has some interesting use cases - teaching, timed webhook and so on.

Getting started with Google Apps Script

A good example of using google apps scripts -
https://gist.github.com/colezlaw/29841885e7f2f49faa07
(also has link to explain google.script.run google script runner etc.)

Another excellent tutorial / collection of sample snippets is at
https://github.com/tanaikech/taking-advantage-of-Web-Apps-with-google-apps-script

There is also the google developers quick-start,
https://developers.google.com/docs/api/quickstart/apps-script

Delegating authority is mentioned at
https://developers.google.com/apps-script/guides/services/advanced#enable_advanced_services

Using service accounts to access api etc, step-by-step instructions are at
https://skaaptjop.medium.com/access-gsuite-apis-on-your-domain-using-a-service-account-e2a8dbda287c

and the gist is at
https://stackoverflow.com/questions/59435611/how-to-access-google-doc-api-with-service-account

calling a web app using curl in php

Just the syntax to use in php for using curl and making a GET or POST request to a google apps script - a slight modification from the answer given here, as curl_close was not needed for us - 

$url = 'https://script.google.com/macros/s/ID_GOES_HERE/exec?optionname=OptionValue';
$schName = 'Some test';
$url = $url . urlencode($schName);
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_CUSTOMREQUEST, 'GET');
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
curl_setopt($curl,CURLOPT_FOLLOWLOCATION, true);
$response = curl_exec($curl);
echo $response;

enabling desktop app notifications on linux

I had mistakenly clicked on "Don't show this message again" or something like that, and wanted to re-enable the notification shown when a VPN connection is made (or fails). The way to do it was mentioned at https://forums.linuxmint.com/viewtopic.php?t=89627

sudo gsettings set org.gnome.nm-applet disable-connected-notifications false sudo gsettings set org.gnome.nm-applet disable-disconnected-notifications false sudo gsettings set org.gnome.nm-applet disable-vpn-notifications false

file written to google drive by service account

When using a service account to create a new file and add it to Google drive, the file owner is the service account.

Can transfer ownership using Google Apps Script also.

https://stackoverflow.com/questions/46680352/how-can-i-change-the-owner-of-a-google-sheets-spreadsheet

https://stackoverflow.com/questions/65256980/google-drive-api-v3-php-client-transfer-file-ownership

restarting remote desktop via ssh or azure portal

Bs series burstable Azure VMs seem to have an endemic problem of the remote desktop service dying or refusing connections after the VM sees some heavy loads. Initially I had no idea why RDesktop was not connecting ... anyway, a couple of workarounds - 

1. install ssh server and leave the ssh service running
   ssh into the machine when RDP has issues,
   ssh  adminuser@server.tld -p port
   powershell
   get-service termservice
   get-service termservice -dependentservices
   
   stop-service UmRdpService
   restart-service termservice
   start-service UmRdpService
   
   
2. install Windows Admin Center on the VM and connect via the Azure portal, restarting the same services as above. We need to use the "Connect with Public IP address" method on the Azure portal. 
   
   
   
   
   
   
   
   
   

limits for google apps scripts execution

A six-minute script execution time is mentioned at
https://developers.google.com/apps-script/guides/services/quotas (also many more limits)
but if we run the script from the Script Editor, the limit is 30 minutes. Probably since our Google Workspace is a non-profit one, so the "paid" limit applies. And a programmatic workaround (which I've not used till now.)

handling duplicates in an sql query

There was a database in which we needed to join three tables, asset id -> subtheme id -> subtheme and the subtheme ids had duplicates as well as multiple subthemes mapped to an asset id. Getting a go-ahead from the content lead, the solution chosen was to just take a single subtheme, using select distinct - something like the first method at https://www.sisense.com/blog/4-ways-to-join-only-the-first-row-in-sql/  

left join (select distinct on (asset_id) * 
         from asset_subtheme_xref
     order by asset_id asc
) as asx on asx.asset_id = tma.asset_id
        left join subtheme_master lsm on lsm.subtheme_id = asx.subtheme_id

Edit: Later on, multiple subthemes were desired. Then, instead of using select distinct in this way, we would need some sort of subquery similar to the one used below for another field, using concat to add all the different subthemes together with CHR(10) - linefeed - between them - all enclosed in parentheses - 

(select string_agg(concat(btwo.name,',',stwo.name,',',sutwo.NAME,',',ctwo.name), CHR(10))
as "Asset tagged to Board Standard Chapter"
from asset_bssc_xref tabxr 
left join chapters ctwo on ctwo.chapter_id = tabxr.chapter_id
left join board_master btwo on btwo.board_id = ctwo.board_id 
left join standard_master stwo on stwo.standard_id = ctwo.standard_id
left join subject_master sutwo on sutwo.subject_id = ctwo.subject_id 
where 
tabxr.asset_id = tma.asset_id) as "Asset Tagged to"

installing ssh client and server on Windows Server 2016

https://hostadvice.com/how-to/how-to-install-an-openssh-server-client-on-a-windows-2016-server/

Or maybe needs this,

https://github.com/PowerShell/Win32-OpenSSH/wiki/Install-Win32-OpenSSH

or other solutions listed at

https://docs.microsoft.com/en-us/answers/questions/79182/sshd-and-sshd-agent-service-not-appearing-after-in.html

or maybe the powershell method listed at

https://docs.microsoft.com/en-us/windows-server/administration/openssh/openssh_install_firstuse