Frequency of occurrence analysis in LibreOffice

Tuesday, November 19, 2019 

One fairly common analytic technique is finding out, for example, the rate at which something appears in a time referenced file, for example a log file.

Lets say you’re looking for the rate of some reported failure to determine, say, whether a modification or update had made it better or worse.  There are log analysis tools to do this (like Splunk), but one way to do it is with a spreadsheet.

Assuming you have a table with time in a column (say A) and some event text in another (say B) like:

11-19 16:51:03 a bad error happened
11-19 16:51:01 something minor happened
11-19 12:51:01 cat ran by

you might convert that event text to a numerical value (into, say, column C) for example by:


FIND returns true if the text (“error”) is found, but returns an error if not. ISERROR inverts that and returns logical values for both. The IF ISERROR construction allows one to specify values if the text is found or not – a bit complex but the result in this case will be “” (blank) if “error” isn’t found in B1 and 1 if “error” is found.

Great, fill down and you have a new column C with blank or 1 depending if “error” was found in column B. Summing column C yields the total number of lines in which the substring “error” occurred.

But now we might want to make a histogram with a sum count of the occurrences within a specific time period, say “errors/hour”.

Create a new column, say column D, and fill the first two rows with date/time values separated by the sampling period (say an hour), for example:

11/19/2019 17:00:00
11/19/2019 16:00:00

And fill down; there’s a quirk where LibreOffice occasionally loses one second, which looks bad. It probably won’t meaningfully change the results, but just edit the first error, then continue filling down.

To sum within the sample period use COUNTIFS in (say) column E to count the occurrences in entire columns that meet a string of criterion: in this case three criterion have to be met: the value of C is 1 (not “”), The value of A (time) is before the start of the sampling period (D1) and after the end (D2). That is:


Filling this formula down populates the sampling periods with the count per sampling period, effectively the occurrence rate per period, in our example errors/hour.

Posted at 08:36:41 GMT-0700

Category: HowTotechnology

Lets encrypt with security/dehydrated (acme-client is dead)

Thursday, June 27, 2019 

Well….  security/acme-client is dead.  That’s sad.

Long live dehydrated, which uses the same basic authentication method and is pretty much a drop in replacement (unlike scripts which use DNS authentication, say).

In figuring out the transition, I relied on the following guides:

If you’re migrating from acme-client, you can delete it (if you haven’t already)

portmaster -e acme-client

And on to installation.  This guide is for libressl/apache24/bash/dehydrated.  It assumes you’ve been using acme-client and set it up more or less like this.

Installation of what’s needed

if you don’t have bash installed, you will. You can also build with ZSH but set the config before installing.

cd /usr/ports/security/dehydrated && make install clean && rehash


portmaster security/dehydrated

This guide also uses sudo, if it isn’t installed:

cd /usr/ports/security/sudo && make install clean && rehash


portmaster /security/sudo

Set up directories and accounts

mkdir -p /var/dehydrated
pw groupadd -n _letsencrypt -g 443
pw useradd -n _letsencrypt -u 443 -g 443 -d /var/dehydrated -w no -s /nonexistent
chown -R _letsencrypt /var/dehydrated

If migrating from acme-client this should be done but:

mkdir -p -m 775 /usr/local/www/.well-known/acme-challenge
chgrp _letsencrypt /usr/local/www/.well-known/acme-challenge

# If migrating from acme-client

chmod 775 /usr/local/www/.well-known/acme-challenge
chown -R _letsencrypt /usr/local/www/.well-known

Configure Dehydrated

ee /usr/local/etc/dehydrated/config


014 DEHYDRATED_USER=_letsencrypt

017 DEHYDRATED_GROUP=_letsencrypt

044 BASEDIR=/var/dehydrated

056 WELLKNOWN="/usr/local/www/.well-known/acme-challenge"

065 OPENSSL="/usr/local/bin/openssl"


save and it should run:

su -m _letsencrypt -c 'dehydrated -v'

You should get roughly the following output:

# INFO: Using main config file /usr/local/etc/dehydrated/config
Dehydrated by Lukas Schauer

Dehydrated version: 0.6.2
GIT-Revision: unknown

OS: FreeBSD 11.2-RELEASE-p6
Used software:
bash: 5.0.7(0)-release
curl: curl 7.65.1
awk, sed, mktemp: FreeBSD base system versions
grep: grep (GNU grep) 2.5.1-FreeBSD
diff: diff (GNU diffutils) 2.8.7
openssl: LibreSSL 2.9.2

File adjustments and scripts

by default it will read /var/dehydrated/domains.txt for the list of domains to renew

Migrating from acme-client? Reuse your domains.txt, the format is the same.

mv /usr/local/etc/acme/domains.txt /var/dehydrated/domains.txt

Create the deploy script:

ee /usr/local/etc/dehydrated/

The following seems to be sufficient


/usr/local/sbin/apachectl graceful

and make executable

chmod +x /usr/local/etc/dehydrated/

Give the script a try:


This will test your apache config and that the script is properly set up.

There’s a bit of a pain in the butt in as much as the directory structure for the certs changed. My previous guide would put certs at /usr/local/etc/ssl/acme/, this puts them at /var/dehydrated/certs/

Check the format of your certificate references and use/adjust as needed. This worked for me – note you can set your key locations to be the same in the config file, but the private key directory structure does change between acme-client and dehydrated.

sed -i '' "s|/usr/local/etc/ssl/acme/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-vhosts.conf

Or if using httpd-ssl.conf

sed -i '' "s|/usr/local/etc/ssl/acme/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-ssl.conf

And privkey moves from /usr/local/etc/ssl/acme/private/ to /var/dehydrated/certs/ so….

sed -i '' "s|/var/dehydrated/certs/private/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-vhosts.conf

# or

sed -i '' "s|/var/dehydrated/certs/private/|/var/dehydrated/certs/|" /usr/local/etc/apache24/extra/httpd-ssl.conf

Git sum certs

su -m _letsencrypt -c 'dehydrated --register --accept-terms'

Then get some certs

su -m _letsencrypt -c 'dehydrated -c'

-c is “chron” mode which is how it will be called by periodic.

and “deploy”


If you get any errors here, track them down.

Verify your new certs are working

cd /var/dehydrated/certs/
openssl x509 -noout -in fullchain.pem -fingerprint -sha256

Load the page in the browser of your choice and view the certificate, which should show the SHA 256 fingerprint matching what you got above.  YAY.

Automate Updates

ee /etc/periodic.conf

insert the following


note the flag is –keep-going (-g) Keep going after encountering an error while creating/renewing multiple certificates in cron mode

Posted at 11:38:45 GMT-0700

Category: FreeBSDSecuritytechnology

Update Waterfox with the new PPA on Mint 19.1

Wednesday, June 19, 2019 

The Waterfox PPA changed recently. The following let me update from 56.2.8 to 56.2.10 (between which the old PPA was removed).

First remove the old hawkeye PPA from your sources list, then:

echo "deb /" | sudo tee -a /etc/apt/sources.list
wget -nv -O Release.key
sudo apt-key add - < Release.key
sudo apt update
sudo apt upgrade

Note Ubuntu 18.04 = Mint 19/19.1 the 18.10 deb fails.

Read more…

Posted at 11:07:01 GMT-0700

Category: Linux

1976 GMC Suburban

Friday, May 17, 2019 

When I was a young child, my dad bought a brand new 1976 GMC Suburban. Yellow. No extras at all – no head liner, plastic seats, manual everything, 305 V8.

It became my car in high school, survived that. Came out to California with me; ended up in the service of SRL, survived that too.

Eventually, it escaped.

Posted at 13:18:33 GMT-0700

Category: photoSRL

Ruby config options fail

Sunday, January 27, 2019 

Ruby is a horrible nightmare language, like almost all modern languages. They try to be so clever and modular, but end up making a maintenance hassle as various modules come and go, dependencies break, and the developer community moves on to the next shiny thing that claims to be the best thing to happen to programming since C.

Oh well.

If you get a bunch of "invalid option: --no-rdoc" errors, it is because sometime in the last few years --no-rdoc and --no-ri were depreciated in favor of --no-document. And, apparently, just recently builds started barfing on the deprecated errors. Building universally with these options is a pretty standard thing as it vastly improves build time and the rdoc system is a whole big kettle of annoying weirdness you just don’t need to wade through.

Now Ruby, being oh-so-clever and friendly, has all sorts of places where these might be set universally or semi-universally. The references will tell you about /.gemrc and /etc/gemrc, but only by doing a grep -FrHIis 'no-rdoc' * at / did I find these sneaky little bastards:

basejail/usr/ports/sysutils/vagrant/Makefile:RUBYGEM_ARGS=      --no-ri --no-rdoc -l --no-update-sources \
basejail/usr/ports/Mk/Uses/ --no-rdoc --no-ri
basejail/usr/ports/devel/ruby-gems/Makefile:DOCS_VARS_OFF= RUBY_SETUP_OPTIONS+="--no-ri --no-rdoc"
basejail/usr/ports/devel/rubygem-io-like/Makefile:DOCS_VARS_OFF= RUBYGEM_ARGS+=--no-rdoc

After converting those to the “new” “better” “shiny” version of the config option string did my gems build.

Posted at 06:33:18 GMT-0700

Category: uncategorized

Getting the postfixadmin 3.2 update to work with FreeBSD

Monday, September 17, 2018 

Postfixadmin is a very nice tool for managing a mail server via a nice web interface that just went through an update to add some security and compatibility features, but at the current revision there are a few bugs (the maintainer says these will all be cleared up in the next release).  A few work-arounds:

If you get:

pkg-static: Unable to access file /var/ports/usr/ports/mail/postfixadmin/work/stage/usr/local/share/postfixadmin/ such file or directory 

Then run

 # make config

and enable DOCS.

If you get

PHP Fatal error:  Uncaught exception 'PharException' with message 'phar "/usr/local/www/postfixadmin/lib/random_compat.phar" openssl signature could not be verified: openssl not loaded' in /usr/local/www/postfixadmin/lib/random_compat.phar:8\nStack trace:\n#0 /usr/local/www/postfixadmin/lib/random_compat.phar(8): Phar::webPhar(NULL, 'index.php')\n#1 /usr/local/www/postfixadmin/common.php(72): require_once('/usr/local/www/...')\n#2 /usr/local/www/postfixadmin/public/common.php(2): require_once('/usr/local/www/...')\n#3 /usr/local/www/postfixadmin/public/setup.php(27): require_once('/usr/local/www/...')\n#4 {main}\n  thrown in /usr/local/www/postfixadmin/lib/random_compat.phar on line 8

There’s a dependency that’s not built into the makefile yet.


 # portmaster security/php56-openssl

(adjust the PHP version to match, or the install command to suit your environment).  Remember to run # apachectl restart.

Also note that the directory the code is served from has been moved to the subdirectory /public for security. This may require updating URLs, DocumentRoot, or modrewrite as appropriate to the webserver environment to get to the login page.

After updating, navigate to public/upgrade.php to update the database automatically.

And because this is open source and not some horrible closed source product, it took a whole 2 hours for a fix.


Posted at 02:20:58 GMT-0700

Category: FreeBSD

How to stick with a decent version of Firefox (pre-Quantum)

Friday, June 1, 2018 

Firefox (52) remains my browser of choice – entirely because of plug-ins. When Firefox completely destroyed the UI/UX with “Australis,” a horrific UI change that basically made Firefox into a crappy clone of Chrome, the only thing that made Firefox usable was “Classic Theme Restorer.” Apparently, unsatisfied with the damage Mozilla had managed to wreak on their user-base with idiotic UI decisions, over the past year or so, a new version called “Quantum” (57) was rolled out that broke the functionality of almost every important plug-in.

This utterly disastrous and truly unforgivable transgression against the user-base was only slightly mitigated by sustaining 52-ESR, at least until the Sept of this year. After that, everyone who cares about having a decent alternative to Chrome will have to migrate to Waterfox.

In the mean time, one really important thing you have to remember to do if you stuck reinstalling your system on Linux (e.g. Linux Mint) is to immediately uninstall Firefox before using it even once.  Then change your install version to ESR and install. If you let Quantum run even once, it will mark all your good plugins as disabled and you need to reinstall them one at a time to get them working again.

sudo add-apt-repository ppa:jonathonf/firefox-esr
sudo apt-get update
sudo apt-get install firefox-esr

Well, that’s the end of Firefox….  Sad to see it go after all these years, but the new plugin concept has made Firefox a subordinate version of Chrome rather than a powerful, customizable tool.


I followed these fine directions and now have waterfox running.

echo 'deb release main' | sudo tee /etc/apt/sources.list.d/waterfox.list
curl | sudo apt-key add -
sudo apt-get update
sudo apt upgrade
sudo apt install waterfox

Just import your firefox preferences on starup and walla, you instant happiness with all the plugins that Quantum broke restored, including such absolute essentials as “Classic Theme Restorer” (which undoes the absolutely horrible UI changes that Mozilla adopted) and downthemall, privacy plugins, etc. If you’ve updated some plugins to be Quantum compatible, you’ll have to back those up (for me that’s things like FoxClocks and Noscript, which managed to hack together semi-viable Quantum compatible plugins after slogging through Mozilla’s buggy WebExtensions API and HTML5 quirks).

This whole translation is quite unfortunate. Waterfox is dependent on the Firefox code base, so this solution may have a finite lifespan, but for now it works and undoes the horror of Firefox 57+/Quantum.

Posted at 11:43:58 GMT-0700

Category: Neutralreviewstechnology

Rubygem passenger flavors in FreeBSD

Saturday, February 3, 2018 

The latest bit of code to adopt the trendy new “flavors” model is passenger.  As “flavors” aren’t supported in Portmaster, this means the transition results in errors  like:

/bin/rmdir /var/ports/usr/ports/www/rubygem-passenger/work-apache/stage/usr/local/lib/ruby/gems/2.4/extensions 2> /dev/null || true
( cd /var/ports/usr/ports/www/rubygem-passenger/work-apache/passenger-5.1.12 &&  /bin/sh -c '(/usr/bin/find -Ed $1 $3 | /usr/bin/cpio -dumpl $2 >/dev/null 2>&1) &&  /usr/bin/find -Ed $1 $3 \(   -type d -exec /bin/sh -c '\''cd '\''$2'\'' && chmod 755 "$@"'\'' . {} +  -o -type f -exec /bin/sh -c '\''cd '\''$2'\'' && chmod 0644 "$@"'\'' . {} + \)' COPYTREE_SHARE buildout /var/ports/usr/ports/www/rubygem-passenger/work-apache/stage/usr/local/lib/ruby/gems/2.4/gems/passenger-5.1.12 )
find: buildout: No such file or directory
find: buildout: No such file or directory

You can make with flavors manually or (assuming your current version is 5.1.12, adjust as needed):

# portmaster -o www/rubygem-passenger rubygem-passenger-5.1.12
Posted at 04:58:54 GMT-0700

Category: FreeBSDtechnology

The Daily, from the NYT, weirdly slow

Saturday, December 30, 2017 

From my distant location overseas, listening to the news via podcasts is a great way to keep up: something I’m quite grateful to have access to on demand and via the internets.   Until the end of Net Neutrality means that only “Verizon Insights” and “Life at AT&T” are still accessible, I enjoy a range of news sources on a daily basis using a podcatcher called Beyond Pod.  One of the essential features it has is the ability to speed up the tempo of podcasts, some of which are a bit slow as recorded.  But one…. one is like dripping molasses on a winter day: The Daily from the NYT by Michael Barbaro.  I’m pretty sure silences are inserted in editing to draw out the drama to infuriating lengths and the tempo of the audio is selectively slowed to about half normal speed.  Nobody can actually talk that slowly.  I mean listen and try – like actually try to draw out a word that might take 1 second to pronounce to two full seconds.   It is a pretty good news summary and has some useful information, but there’s no way I’d suffer through it without setting the tempo to 2x.

Every time I accidentally stream the podcast, rather than downloading and playing, the tempo control is disabled and while I scramble to skip to the next podcast before my I start questioning reality I often wonder for a moment just how bad the pauses are.  Here’s my analysis:

I consider the BBC Global News to be a very professional, truly “broadcast quality” podcast.  The announcers are clear, comprehensible, and speak at a pace that is appropriate for a news broadcast.  I still speed it up because daily life is like that now, but if I listen at normal pace, it isn’t even slightly annoying.

The Economist Radio is fairly typical of a print publication’s efforts at presenting print journalists as audio stars.  it doesn’t always work out perfectly and the pacing varies a lot by who is speaking and the rather eclectic line-up.  In general it is annoyingly slow, but not interminably so.  It comes across as a bit amateur by broadcast standards, but well done and very informative.

But then there’s The Daily from the NYT.  This podcast was the reason I took the time to figure out how to speed up playback.  There was no other choice: either unsubscribe or speed it up to something not aneurysm inducing.  Looking at the waveforms, I suspect they might actually be inserting silences of around 500msec between words, perhaps for dramatic effect (there’s way too much dramatic effect in a lot of the stories, which speeding up only hastens rather than fully alleviating—never have you heard so many interviewees break into uncomfortable tears as they’re overwhelmed by whatever the day’s tragedy is, an artifice only slightly less annoying than broadcasting the sound of someone eatingOMG, that’s real.  Rule 34.)

Read more…

Posted at 07:42:26 GMT-0700

Category: Audiotechnology

Let’s Encrypt….

Sunday, December 10, 2017 

Let’s encrypt, why not?

Wanna know how I did it for FreeBSD/Apache/acme-client, jump below.

Let’s encrypt is a service from the fine people at Mozilla, who, when they’re not trying to prove that Firefox can be a Chrome clone, do some really good stuff. Certificates are what give you the little warm fuzzy feeling of a green lock icon, and when properly configured, avoid giving you that terrifying feeling that something horrible is about to happen if you visit a site with an expired one or a self-signed one.

There are some huge structural problems in the certificate concept that seem to exist only to validate the certificate mafia, that can charge $100s per year for a validated certificate, as if executing the script to issue one was somehow expensive. It is not, you can generate one yourself that provides exactly the same security as one provided by a big company that gets their root certs distributed in a browser, but browsers reject these with scary messages so webmasters have to keep buying them.

Now there’s a theory behind why they’re ripping you off: the premise is that the certificate verifies the site is who it says it is – that if you go to, you’re actually visiting your real bank, not being redirected by a man-in-the-middle attack to some fake landing page to harvest your passwords, log into your account, and steal all your cats.  There are a few problems with this:

  • Nobody actually checks a URL so while a certificate sort of adds some weight to the probability that is owned by mybank, not some hacker a few tables over ARP poisoning the cafe wifi, it doesn’t do anything if you click on a link to
  • The companies that claim to check IDs and verify owners, do not.  That would cost money. You think they’re gonna actually do that?  No… (CAcert actually does, but they don’t get a root cert because… they do it for free. And don’t have Mozilla’s money and clout.)
  • Stealing a root cert private key can generate significant LOLZ; it happens a lot.
  • Law enforcement the world over has “lawful intercept” certs.  You’re probably on some country’s poop list if you have ever used social media. Their laws permit intercepting your communications.  Some country’s laws somewhere certainly do no matter who you are.
  • But dang, those annoying warnings that do nothing to secure you mean that people who publish a website just for the good of the planet either have to pay up, go through a lot of hassle, or leave their user’s content streams exposed to the world’s prying eyes…

…Until Let’s Encrypt came along.  It is a lovely little set of tools and services that not only issue browser-accepted certs (see the green lock?) but also automate renewal.  They basically check that you have enough control over your website to let a script write a file that that they can read back and verify, and if so, you’re who you say you are: the person with write access to the server powering the website they’re giving the certificate too.  That’s all anyone can really do, and is as secure as any other cert there is for identification of a site: that is except for stolen certs, url typos, law enforcement certs, or malicious code on your computer, if you visit and you don’t get any warnings, you’re probably reading data coming off my computer and not some hacker pretending to be me.

I got Let’s Encrypt to work, but it took some modifications of the existing guides, and I think the service is a good thing that more people should use, so in the spirit of investing some of my resources into the great shared experiment that is Open Source, here’s my How To:

Upstream Guides:

I found these two guides extremely helpful.

Step 1: Installing the certificate generation tool

There are a few different software tools to manage the Let’sEncrypt process.  I elected to use Kristaps Dzonsons acme-client, ported to FreeBSD by Bernard Spil.

I was using OpenSSL on my site.  Bernard and Kristaps have some strong opinions on OpenSSL and heartbleed and a few other problems and therefore require LibreSSL.   If you’re using it already, great.  If not, you’ll have to install it.  It wasn’t too terrible, but I ran into a few issues:
Or, easy peasy

# ee /etc/make.conf
set  DEFAULT_VERSIONS+= ssl=libressl
# portmaster -od security/libressl security/openssl
# portmaster -rd security/libressl

if that fails with

===>>> The argument to -r must be a package name, or a glob pattern

Then try:

# pkg version -v | grep libre
libressl-2.6.3 = up-to-date with index
# portmaster -rd libressl-2.6.3
or for a complete refresh
# portmaster -Rafd

Curl will probably fail with LibreSSL (and with the latest, if it has brotli support enabled).  Check the google to see if these fixes are still needed, or just:

# cd /usr/ports/ftp/curl
# make config

disable TLS-SRP

ftp/curl 7.75.0 has an issue with pied piper brotli, which requires modifying the makefile to build --without-brotli as indicated in comment #2 

(Sunpoet, the curl port maintainer, got back to me with an update: when PR/223966 is integrated in Brotli, he will add an optional Brotli support flag and it should work fine at that point without the Makefile edit.)

Step 2: Actually installing acme-client

The really easy part: you should be able to

# portmaster security/acme-client

and be on your way to configuration heaven.

Step 3: Initial configuration

The defaults for acme-client expect certain directories to exist and the installer doesn’t create them.

# mkdir -pm750 /usr/local/www/.well-known && chown -R www:www /usr/local/www/.well-known
# mkdir -pm750 /usr/local/www/.well-known/acme-challenge && chown -R www:www /usr/local/www/.well-known/acme-challenge

The how-to’s seemed to forget the last one.

And make a modification to your httpd.conf file to permit the Let’s Encrypt servers to have access to these folders:

# ee /usr/local/etc/apache24/httpd.conf

add the following:

# Lets Encrypt challenge directory configured per 
<Directory "/usr/local/www/.well-known/">
        Options None
        AllowOverride None
        Require all granted
        Header add Content-Type text/plain

And, for each VHOST that is going to get a cert:

# ee /usr/local/etc/apache24/extra/httpd-vhosts.conf

add to each non-ssl VHOST definition the following:

Alias /.well-known/ /usr/local/www/.well-known/

such that you end up with something like (yours may be different, especially watch out for BasicAuth or ModRewrite, addressed further down):

<VirtualHost IP.NU.MB.ER:80>
    DocumentRoot /usr/local/www/data-dist/domain-root
    ServerAlias *
    Alias /.well-known/ /usr/local/www/.well-known/
    ErrorLog /var/log/domain-error_log
    CustomLog /var/log/domain-access_log combined
    ScriptAlias /cgi-prg /www/cgi-prg

Don’t forget!

# apachectl restart

Step 4: First Try

At this point the system should be configured sufficiently to do a trial run with a single domain from the command line. Later on there are some scripts that will automate the process of both converting a large number of VHOSTed domains on a server to Let’s Encrypt and for maintaining them and getting email notifications if anything goes wrong in the, hopefully, fully automatic renewal process.

# acme-client -mvnNC /usr/local/www/.well-known/acme-challenge

This should create all the directories still needed and populate them, then check with the lets encrypt server and get a certificate and install it in the right place.  Inshalla.

If you get something like

acme-client: transfer buffer: [{ "type": "urn:acme:error:malformed","detail": "Provided agreement URL[]does not match current agreement URL[]","status": 400 }] (267 bytes)

That means the lets encrypt agreement has changed.  You can’t do much but write the port maintainer or wait for an update.  It will get fixed quickly and should only happen once a year.  I don’t think you’ll get it at all unless you’re unlucky enough to try to update when it is changing.  I was.

More likely you’ll get something like

acme-client: transfer buffer: [{ "type": "http-01", "status": "invalid", "error": { "type": "urn:acme:error:unauthorized", "detail": "Invalid response from (etc...)

This means there’s a problem accessing the /.well-known/ directory by the server.  There can be a lot of reasons for this:

  • You didn’t restart apache # apachectl restart
  • There was an error in the config file (look at the output of the restart) and therefore apache didn’t actually reaload with your new config.
  • DNS isn’t pointing where you think it is pointing.  Check with nslookup/whois to make sure.  Really.
  • You have the directories protected in some way – like with .htaccess.  (see below)

But if it goes well, you’ll get something like:

acme-client: /usr/local/etc/acme/ account key exists (not creating)
acme-client: /usr/local/etc/ssl/acme/private/ domain key exists (not creating)
acme-client: directories
acme-client: DNS:
acme-client: DNS: 2001:418:142b:290::3d5
acme-client: DNS: 2001:418:142b:28d::3d5
acme-client: req-auth:
acme-client: /usr/local/www/.well-known/acme-challenge/_ffVe6jHNHbIG1XKAeoqQmmtryWMGCKsfHIWWkl5lJw: created
acme-client: challenge
acme-client: status
acme-client: certificate
acme-client: full chain
acme-client: DNS:
acme-client: DNS:
acme-client: DNS: 2001:5a8:100::b817:9fb0
acme-client: DNS: 2001:5a8:100::b817:9fb1
acme-client: /usr/local/etc/ssl/acme/ created
acme-client: /usr/local/etc/ssl/acme/ created
acme-client: /usr/local/etc/ssl/acme/ created

Yay, you’ve got certs!  Now update your vhosts file to point to the certs you just created.  You may need to add a 443 container or, if it exists, update it to point to the new certs and restart apache.

# ee /usr/local/etc/apache24/extra/httpd-vhosts.conf

<VirtualHost IP.NU.MB.ER:443>
      DocumentRoot /usr/local/www/domainroot
      SSLCertificateFile /usr/local/etc/ssl/acme/
      SSLCertificateKeyFile /usr/local/etc/ssl/acme/private/
      SSLCertificateChainFile /usr/local/etc/ssl/acme/
      Header set Strict-Transport-Security "max-age=31536000; includeSubDomains"
      ErrorLog /var/log/domain-error_log
      CustomLog /var/log/domain-access_log combined

save and restart, look for any errors (typos on directory paths etc. will be detected and apache won’t restart, but be aware, it won’t quit either).

# apachectl restart
 Performing sanity check on apache24 configuration:
 Syntax OK
 Stopping apache24.
 Waiting for PIDS: 81160.
 Performing sanity check on apache24 configuration:
 Syntax OK
 Starting apache24.

Navigate to and check out your new green lock.  Check security and you should find:


Acme-Client Options

# man acme-client has all the deets, but we’re using:

  • -m to append the domain name to paths, use this and always use it or never.
  • -v for verbose output so we can see what is going on.
  • -n to check if an account key exists and create if not (no reason to omit)
  • -N to check if a domain key exists and create if not (also no reason to omit)
  • -C to specify the path to the challenge dir.  These guides all assume a centralized challenge dir outside the main serving path, and to which we redirect via an alias directive.
  • -F which forces the recreation of certs even if they haven’t expired (this counts against your 10 per 3 hours limit)
  • -s which redirects the process to the Let’s Encrypt staging server, which has no volume limits but also doesn’t create certs browsers accept.  (Using this is fine, but requires cleanup to switch to the production server, see below)
  • -e which is used to add a SAN to the certificate.  Removing one is a bit more involved (see below).

Automating Registration

Lets say you have a lot of domains, you might want to automate the process.  I modified the renewal script to automate the registration process.  This saved some time, but one quirk is you can only register 10 domains (certificates, including SANs, basically 10 lines of the domains list) per 3 hours (they say-I found it takes more like 12 hours to be allowed to register more).

First create  a file with all the domains you want to register for a Let’s Encrypt certificate in the same format as the renewal script uses (it can be the same file, but I made it different as I was experimenting)

# ee /usr/local/etc/acme/newdomains.txt 

# ee /usr/local/etc/acme/


# This script was adapted by Richard Fassett from
# by Bernard Spil
# See
# and updated again from richard fassett's script at
# this requires a file called /usr/local/etc/acme/newdomains.txt of the format
# domain.tld sub.domain.tld alt.domain.tld
# domain2.tld 
# domaind3.tld sub.domain3.tld 
# etc
# This should only be run to bulk-add domains.

# Define location of dirs and files

# Loop through the newdomains.txt file with lines like
cat ${DOMAINSFILE} | while read domain subdomains ; do

  # Create the cert directory with the command
  # acme-client -mvnNC /usr/local/www/.well-known/acme-challenge (domain subdomains)
  acme-client -mvnN -C "${CHALLENGEDIR}" ${domain} ${subdomains}


# chmod +x /usr/local/etc/acme/

A few fixes/recoveries that might be useful at this point: add SAN, remove SAN, switch from staging to production Let’s Encrypt servers.

Automation can break things, you might find you adjusted a few domains incorrectly or want to add a SAN later.

If you need to redo a domain from scratch, for example if you use the “s” option which created a cert from the staging server that doesn’t have volume limits (maybe you’re testing a lot of domains or trying to debug a particularly tricky .htaccess or DNS condition) – you might create a domain with acme-client -mvnsNC /usr/local/www/.well-known/acme-challenge and then want to generate the production cert.  You also need to do this to remove a SAN.  If you try without deleting the directories, you’ll get something like unknown SAN entry. (You replace “” with your domain.)

# setenv DD
# rm -r /usr/local/etc/ssl/acme/private/$DD && rm -r /usr/local/etc/acme/$DD && rm -r /usr/local/etc/ssl/acme/$DD && acme-client -mvnFNC /usr/local/www/.well-known/acme-challenge $DD www.$DD

If you need to add a new SAN to an existing domain

acme-client -mvneFNC /usr/local/www/.well-known/acme-challenge

it is the -e that “extends” the certificate.

Step 5: Automating Renewal

You might notice that the duration of the certificate is rather short: 3 months.  You really don’t want to be responding to certificate expired errors every 3 months, so let’s automate the renewal process.  For this you can create two files and store them on your server.  One is the renewal script itself and the other is a list of domains to renew.  This assumes you have more than one domain.  If you only have one domain, this is a bit overkill, but it will work, so why not?  You might get more domains in the future.   Everyone does.

First create a file with your list of domains, call it something creative like “domains.txt”  This is really a certificate request list with the “primary” domain and Subject Alternative Names (SANs) each on a single line.  In theory the SANs can be all over the place and Let’s Encrypt allows up to 100 per certificate (quite a lot), so the implication of “domains.txt” naming is a bit inaccurate, but that’s what everyone is using so we won’t be contrary.  You have to make sure that all the subdomains resolve—the Let’s Encrypt servers are going to look them up via DNS and if there aren’t working entries, this will fail with one of the errors above.  Check first.  I have not tested whether, if for example, you own,, and and they all point to the same directory, you can use one cert with different TLDs (or domains) as SANs; you should be able to, but I didn’t try.

# ee /usr/local/etc/acme/domains.txt

Now that you’ve saved that, the following script is adapted from a few at the references listed above and works on my server.  I made a few adjustments and corrections (there was a name change for acme-client which hasn’t quite propagated through all the HowTos yet).

# ee /usr/local/etc/acme/


# This script was adapted from by Bernard Spil
# See
# ... and further modified by David Gessel  
# This script will fail if the directories haven't been set up and
# domains in domain.txt have been successfully verified

# Define location of dirs and files

# is changed to 1 if any domains expired and were renewed

# Loop through the domains.txt file with lines like
cat ${DOMAINSFILE} | while read domain subdomains ; do

    # acme-client returns RC=2 when certificates 
    # weren't changed; use set +e to capture the return code
    set +e
    # Renew the key and certs if required
    acme-client -mvb -C "${CHALLENGEDIR}" ${domain} ${subdomains}
   # now that we have the return code, set script to exit if 
   # nonzero is returned
   set -e

   # if anything is expired, we'll want to do something 
   # (e.g., restart HTTPS)
   if [ $RC -ne 2 ] ; then

if [ "$CHECKEXPIRATION" -ne "0" ] ; then
        service apache24 restart

# chmod +x /usr/local/etc/acme/

This works quite well and will walk through your domains and renew as needed.

I have 36 domain/certificate lines in my “domains.txt” file and timing this script it takes 2.13 seconds to execute on my server.  There’s no real problem running it every night and if you have a lot of domains, you should remember you can only get 10 certs at a time and they won’t renew for about a week before expiry, a limitation I ran into in the bulk setup process.  You can spread your domain renewals out over the three months by force renewing blocks of them if you have more than about 60 per server.

You probably want to automate the process as a cron job. But before we do, lets address one more little problem: one of the shortcomings of the script process below is that the output messages of the script are output to stdout and only cron’s stderr is emailed to the admin. If your shell environment is wrong or the path to the script is wrong, cron will tell you, but if your domains don’t resolve or the script can’t reach /.well-known/, you will not get any warnings. That’s might be a bummer. So I redirect the output of the script to a log file. It gets overwritten with each execution, so it doesn’t need to be rotated – it is just the output of the last execution. It should be filled with lines including “adding SAN” (which it tells you for each domain) and “certificate valid” which it tells you for each cert that doesn’t need to be renewed. But it might tell you something else, like it barfed trying to reach the /.well-known/ directory because, say, you messed around with .htaccess or forgot to renew your domain and it is being redirected to parking or something. The following script first checks to see if there are any lines in /var/log/lets-encrypt-renew other than the expected, and if so, emails just those lines. You shouldn’t get anything until renewal time or if there’s an error. If you don’t care about renewal notices, you can edit the script to ignore those too.

# ee /usr/local/etc/acme/


# this script scans the log file created by the renewal execution cron job
# then removes any lines containing "adding SAN" or "certificate valid", which
# are normal messages, and mails whatever is left over using the "mail" command
# check full paths (or use relative) but full paths can avoid some errors
# use "# which grep" and "# which mail" on your system to check.


/usr/bin/grep -v "adding SAN" /var/log/lets-encrypt-renew | \
/usr/bin/grep -v "certificate valid" | /usr/bin/cat | \
{ while read status

  if [ "$PROBLEM" -ne "0" ] ; then
        /usr/bin/grep -v "adding SAN" /var/log/lets-encrypt-renew | \
        /usr/bin/grep -v "certificate valid" | \
        /usr/bin/mail -s "Lets Encrypt Errors" $1

# chmod +x /usr/local/etc/acme/

My cron configuration is set up as

# crontab -e

#*     *     *   *    *        command to be executed
#-     -     -   -    -
#|     |     |   |    |
#|     |     |   |    +----- day of week (0 - 6) (Sunday=0)
#|     |     |   +------- month (1 - 12)
#|     |     +--------- day of        month (1 - 31)
#|     +----------- hour (0 - 23)
#+------------- min (0 - 59)

# expanded path
# Let's Encrypt renewal check
*       3       *       *       *        /usr/local/etc/acme/ > & /var/log/lets-encrypt-renew
*       4       *       *       *        /usr/local/etc/acme/

Note that this requires that mail works. On servers that aren’t serving email, I use SSMTP and configured it more or less following this guide and and this especially the tip about using # chpass to change the default Full Name for root from “Charlie &” to something useful like “ServerName Root.”

You can test the mail function by adding a random word (or domain) to your domains.txt file and then executing

# /usr/local/etc/acme/ > & /var/log/lets-encrypt-renew
# /usr/local/etc/acme/

If everything is set up right, you’ll get an email complaining about your random word not being valid.  If you restore the correct domains.txt file and execute the above two commands you should not get an email at all.

# more /var/log/lets-encrypt-renew

should show only lines with “adding SAN” and “certificate valid” in them. If you execute # /usr/local/etc/acme/ you shouldn’t get any message.

.htaccess Problems

If you’re controlling access to a directory or have some non-HTML style process listening, you might run into challenges giving the Let’s Encrypt server access to the /.well-known/ directory.  I found the following formulation worked:

AuthType Basic
     AuthName "Please login."
     AuthUserFile "/xxx/.htpasswd"
     # the directive below also "requires" that the requested URL include /.well-known/
        Require expr %{REQUEST_URI} =~ m#^/.well-known/.*#
        Require valid-user

Basically the script above allows (requires) a “valid-user” (one with an entry in the AuthUserFile and valid matching password) and also requires (allows) a URL that is going to /.well-known/ and subdirectories thereof.  This also works in /usr/local/etc/apache24/httpd.conf and /usr/local/etc/apache24/extra/httpd-vhosts.conf

modRewrite to HTTPS problems

You can also create problems by rewriting to HTTPS.  You might want to do this now that you have certs that will auto-renew and you can provide a secure experience for everyone.   In order to get to the /.well-known/ directory, you have to add an exception to the mod-write rule for traffic to this subdirectory like so:

	RewriteEngine on
           RewriteCond %{REQUEST_URI} !^/\.well\-known/acme\-challenge/
	RewriteCond %{HTTPS} off
            RewriteRule (.*) https://%{SERVER_NAME}/$1 [R=301,L]

Also, if you redirect on a 404, some formulations cause problems. This one does not seem to:

ErrorDocument 404 /index.php

Posted at 06:43:58 GMT-0700

Category: FreeBSDHowToSecuritytechnology