I'm sure everybody is aware you can have PostgreSQL fetch data live across the internet (or locally of course) as part of your queries and use the data directly. In fact there are a large number of drivers available to access different kinds of data for download. But in the simplest case, we can also just use the file_fdw wrapper that's included in the standard PostgreSQL packages, together with everybody's http-swiss-army-knife, curl.

In attempting to adapt this post for the public, what more time-relevant dataset to work off in these pandemic-affected times than the open data provided by the ECDC, being both open data and very current. In particular for this example, they provide public datasets with COVID numbers from across the world (actual public data, requiring no registration to read, and actual data, not just a limited API).

So, let's see how we can access this data from PostgreSQL:

CREATE EXTENSION file_fdw;

CREATE SERVER curly FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE _rawdata (
 daterep text not null,
 day int not null,
 month int not null,
 year int not null,
 cases int not null,
 deaths int not null,
 countries text not null,
 geoid text not null,
 countrycode text null,
 popdata int null,
 continent text not null,
 cumulative14days float null
)
SERVER curly
OPTIONS (
 PROGRAM 'curl -s https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/',
 FORMAT 'csv',
 HEADER 'on'
);

And to use this we can simply query the foreign table, which will then trigger a http GET of the data:

covid=# SELECT count(*) FROM _rawdata;
 count 
-------
 41837
(1 row)

However, there is an unfortunate interaction with LIMIT. So if we for example try to get just the first 10 rows:

covid=# SELECT * FROM _rawdata LIMIT 10;
ERROR:  program "curl -s https://opendata.ecdc.europa.eu/covid19/casedistribution/csv/" failed
DETAIL:  child process exited with exit code 23

I’m sure everybody is aware you can have PostgreSQL fetch data live across the internet (or locally of course) as part of your queries and use the data directly. In fact there are a large number of drivers available to access different kinds of data for download. But in the simplest case, we can also just use the file_fdw wrapper that’s included in the standard PostgreSQL packages, together with everybody’s http-swiss-army-knife, curl.

In attempting to adapt this ...

So, you have a partitioned table. And you want to change your mind. Re-partitioning is "easy" if you can take downtime -- just create a new table with a new name and copy all the data over. But what if we want to try to do it without downtime? Logical replication enhancements in PostgreSQL 13 brings us some new options for this!

But first a disclaimer -- this is definitely not pretty! And does not take into consideration things like foreign keys and similar. But sometimes a quick hack can be the best hack.

So let's go!

As always, scammers and phishers use newsworthy events to their advantage. The coronavirus pandemic is no exception. All over the worlds, security researchers observe phishing and scam attempts. Samples for studying and for awareness training are collected at various sites, including https://coronavirusphishing.com/. A large number of security researchers have joined forces to establish a cyber […]

When we introduced the network configuration using Ansible and AWX at a customer, we gradually extended the configuration scope. Over time, more and more configuration got added into the configuration pool and this lead to longer and longer run-times for the playbooks.

While the job-execution got really simple by using AWX instead of the plain CLI method for Ansible, the time to finish drew heavily on that benefit.

A complete job-run over the network infrastructure took at least ...

We’ve been to FOSDEM in Belgium this year. A couple of Many of Redpill’s Agents (so called: Consultants) have made the trip to Belgium to join the annual conference taking place at Université libre de Bruxelles (ULB).

This year the conference was held during the first weekend in February, at the 1st and 2nd.

For those who don’t know: FOSDEM is a free software developer conference where you can attend talks about various different tools, processes and ideas and ...

One of my honeypots runs INetSim which, among many other services, emulates an SMTP server. The honeypot is frequently used by spammers who think they’ve found a mail server with easily guessed usernames and passwords. Obviously I’m logging the intruders’ activities, so I’m shipping the logs to Elasticsearch using Filebeat. Shipping the regular INetSim activity […]

If you are using Azure PostgreSQL and have upgraded your client side libpq to version 12 (which can happen automatically for example if you use the PostgreSQL apt repositories), you may see connection attempts fail with symptoms like:

$ psql -hZZZZZZ.postgres.database.azure.com -dpostgres -UXXXXX_dba@ZZZ-db01
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

With no log information whatsoever available. This can happen if your client is in a Kerberos environment and has valid Kerberos credentials (which can be verified with the klist command). In this case, PostgreSQL 12 will attempt to negotiate GSSAPI encryption with the server, and it appears the connection handler in Azure PostgreSQL is unable to handle this and just kills the connection.

When running the same thing against a local PostgreSQL server prior to version 12, a message like the following will show up in the log:

2020-02-20 10:48:08 CET [35666]: [2-1] client=1.2.3.4 FATAL:  unsupported frontend protocol 1234.5680: server supports 2.0 to 3.0

This is a clear indicator of what's going on, but unfortunately the information isn't always available when connecting to a managed cloud service, such as Azure PostgreSQL. The hard error from Azure also prevents libpq from retrying without GSSAPI encryption, which is what would happen when connecting to a regular PostgreSQL backend or for example through pgbouncer.

The fix/workaround? Disable GSSAPI encryption in the client:

$ export PGGSSENCMODE=disable
$ psql -hZZZZZZ.postgres.database.azure.com -dpostgres -UXXXXX_dba@ZZZ-db01
Password for user XXXXX_dba@ZZZ-db01:
psql (11.6 (Ubuntu 11.6-1.pgdg16.04+1), server 9.5.20)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

If you have this type of issue, it's probably worth putting this environment variable in your startup scripts. It can also be set using the gssencmode parameter as part of the connection string, in environments where this is more convenient.

While the AWS console gives you a nice point and click interface, and really helps you explore the vast service catalog of AWS, the use of the CLI should not be neglected.

Some of the advantages of the CLI:

• Reusable, can the same command multiple times, perhaps with slight modification for quickly creating multiple instances of similar resources.
• Reproducible, can run the same command, to reproduce exactly the same kind of resource as has been created before.
...

A customer of my employer Redpill Linpro was recently the target of a DDoS attack. While investigating the attack, we found a large number of HTTP requests with the User-Agent named CITRIXRECEIVER. The clients performed GET requests to multiple URLs on the customer’s web site at the rate of several thousand packets per second. The […]

This year we intend to upgrade all the routers in our network backbone to a brand new platform based on open networking devices from Edge-Core running Cumulus Linux. In this post - replete with pictures - we will take a close look at the new routers and the topology of our new network backbone.

Why upgrade?

Our network backbone is today based on the Juniper MX 240 routing platform. Each of them occupy 5

Sometimes I need to quickly remove one of our data centre switches from production. Typically this is done in preparation of scheduled maintenance, but it could also be necessary if I suspect that it is misbehaving in some way. Recently I stumbled across an undocumented feature in Cumulus Linux that significantly simplified this procedure.

The key is the file /cumulus/switchd/ctrl/shutdown_linkdown. This file does normally not exist, but if it is created with the contents 1, it changes ...

Some time back in 2019, Varnish Software and the Varnish Cache project released a new LTS upstream version 6.0.5 of Varnish Cache. I updated the fedora 29 package, and added a modularity stream varnish:6.0 for fedora 31. I have also built el6 and el7 packages for the varnish60 copr repo, based on the fedora package. A snapshot of matching varnish-modules, and a selection of other misc vmods are also available.

Packages may be fetched from https://copr.fedorainfracloud.org/coprs/ingvar/varnish60/.

vmods included in varnish-modules:
vmod-bodyaccess
vmod-cookie
vmod-header
vmod-saintmode
vmod-tcp
vmod-var
vmod-vsthrottle
vmod-xkey

vmods packaged separately:
vmod-blobsynth
vmod-rfc6052
vmod-querystring
vmod-blobdigest
vmod-memcached
vmod-digest
vmod-geoip
vmod-basicauth
vmod-curl
vmod-uuid

A version of this text was presented as the lecture for Creation Day, Holmlia Church, 2019-06-19.

[Introduction: Excerpts from The Ainulindalë accompagnied by folk music improvisation on organ and violin]

Some of you may know that I’m a Tolkien enthusiast. I give away Tolkien books on my own birthday. Sometimes I feel like going door-to-door with The Lord of the Rings and its gospel; *Ding* *dong* Goood Morning! Did you know that Tolkien’s books may change your life? (What is that? Yes, Good Morning in all meanings of that expression, thank you). Now, as I can present this before you here in church, I probably won’t have to.

For many, the language professor John Ronald Reuel Tolkien only means his books The Hobbit and The Lord of the Rings. Some have even not read any of his books, but may have seen films with strange wizards, orcs, elves, and a good deal of fighting. But this is Creation Day, so in this small lecture there will be less orcs, Gandalf, Bilbo, Frodo, and the Ring. Instead I will talk a bit about Tolkien’s thoughts on God as the Creator, his Creation, and Men, as God’s sub-creators.

In the introduction, we heard lines from Tolkien’s creation myth, the Ainulindalë, that is, The Music of the Ainur: God gives the Ainur, that is, his angels, a theme to improvise over. Then he lets the song unfold, and when the song is finished, he shows them what they have sung. He says: Ëa! Let this world be! And the song is the World. When the song is sung, its life is the history of the World unfolding. Isn’t that just incredibly beautiful?

The Ainur enjoys the high mountains and the deep valleys, and the sea, and the elves, and the trees, and the flowers, and the animals they have sung about. But in the middle of the harmonies, Melkor’s dissonance is heard. The mightiest of the angels sets his own thoughs above God’s thoughs, and wants to rule, and in pride, fill the void with subjects under his dominion. But what first sounds like destroying God’s theme, is itself taken up in the song, and makes it even more fulfilled.

In the motion of the sea, the song is most clearly heard. Now further in the Ainulindalë, we hear how Melkor in his rebellion makes extreme cold, freezing the water, and uncontrolled heat, boiling it to steam. But in the midst of the freezing cold, we get beautiful snowflakes, and from the heat and steam, there are clouds and life-giving rain. Tolkien shows us that even when the Creation is challenged by evil, God can always turn the evil to something good in the end. God doesn’t want evil to happen, but when it happens, hope is always there. And when Time comes to its end, and the final chord is sung, we may see that hope and faith in the middle of evil, gave the most beautiful music played in God’s honor.

Those reading Tolkien’s books will soon observe his joy of nature. The books are swarming of life. There are bushes and flowers and trees of all kinds, and everything has value; from pipe weed to oak trees. There are insects and foxes, eagles and ravens, bears and elephants, and even the simplest flower may be important and save lives. Tolkien loved the landscape were he grew up, with meadows, woods, small rivers, hills, and the other crossroads with an inn with good beer. But he also loved the snow in the high mountains, the mighty large rivers, the deep cloven valleys, the sun in the sky, the stars of Elbereth, thunder claps and storm over mountains, and the wind of the sea. There is a lot of God’s creation wihin Tolkien’s Middle Earth.

Tolkien criticize those who says that fairy-tales and fantastic stories are just escapism, and have nothing to do with reality. In one of his most known lectures, he turns this upside-down: In a World of evil, somebody wants to tell that there is Light in the darkness and make stories of Hope. What is wrong with that? And Escaping means getting from prison to freedom. That is a Good Thing!

Tolkien says that one of the most important features of a fairy-tale, is to experience anew the small and large wonders of the World. When in The Lord of the Rings we read about Frodo coming to the elven wood Lothlórien; For the first time in his life, he realizes what a Tree really is. He feels the bark, the trunk, the branches, and the leaves. They are full of color and smell and sound and Life. The Ents, the sheperds of the Trees, that watches over the woods of Fangorn Forest, sing and talk to their trees, and mourns them when they die. Trees are so much more than something that’s just there. Go and watch and smell and enjoy the life of the trees in the grove you pass on the way to work every day.

Aragorn and his rangers have watched over Hobbiton and Bree, and held evil forces away, without the people living there knowing about this. When you get to live in freedom and peace, remember in thankfulness who built the peace, and who is watching over it. After reading about the faithful friendship between Sam and Frodo, find again the joy in the relations to your friends. When the story about Aragorn and Arwen’s long awaited marriage is told, or Faramir’s spontanous proposal to Eowyn, or Rose and Sam’s happy wedding, renew the joy of your partner, and delight in your choice. Fantasy and fairy-stories gives us the opportunity to recovery, to find again the fantastic from the domestic.

Man is special in God’s creation. Tolkien meant that God has put a spark of his creating power within us, making us more than animals. In telling myth and stories, we make new things that weren’t there before. We are sub-creators.

When we make new stories, or tell or retell myths, they are of course not the Truth. But as the light is spread through a prism making a spectrum of colors, our stories are created from the True Light. Thus, Myth and stories may show us a glimpse of the Truth. This is good, and not only because they come of God’s true Light. When light is broken into colors, they are no longer perfect white: Some becomes red, some blue, some yellow, some violet. But in this spectrum of colors, something new has been created, that earlier was not. And it has value in itself.

Unfortunately, we can not all write like Tolkien. There are those that try, and you get … things … like Game of Thrones and other garbage. But when we use our talents, we are sub-creators too. If that is being a priest, or taking pictures, or making music, or doing accounting, or sports, or teaching, or baking, or programming, or carpentry; That is fullfilment of the potential of God’s light through us. With all our strange shapes and colors, we bring fourth a richness that would not exist without us. And though our sub-creation is not perfect, it still has its source in God’s unbroken bright light.

I read Tolkien’s “Canon”, that is, The Hobbit, The Lord of the Rings, and The Silmarillion, every year about Christmas. So also this year.

What was Bilbo up to after he left Hobbiton, and until Frodo met him again in Rivendell. While there are few explicit mentions, there are some cues that we may explore.

First, when Bilbo was packing and leaving Bag End after his long expected party, he was again going with dwarves. They are not named, but it seems likely that they are the same who delivered goods from Dale to the party, and have probably stayed in the guest rooms of Bag End since. No dwarves were mentioned at the party, and I guess they would have, had they been present. So Bilbo goes with the dwarves, and as he tells to Frodo later, he goes on his last journey all the way to The Mountain, that is, Erebor, and to Dale. He comes too late to visit his old friend Balin – he had left for Moria. Then Bilbo returned to Rivendell. No more is told about his travels back, though it is easy to speculate. When he left the Mountain, returning homewards the previous time, he was invited to the halls of his friend the Elven King, that is Thranduil of Mirkwood/Greenwood the Great, but gently rejected the offer. It would be natural to pay him a visit on his second return westwards. The elves would give him safe journey through the forest. By legend, he was probably well known to the Beornings too, and I would guess he got a safe and well escorted journey back over the Misty Mountains.

Back in Rivendell, Frodo got acquainted to Aragorn the Ranger. If Bilbo uses one year on his journey to Erebor and back to Rivendell, he is 112, and Aragorn would be at the frisky age of 71. While Aragorn is often away, helping in the watch of the Shire, or on errantry for Gandalf, like going hunting for Gollum, he is probably often back in Rivendell. Bilbo speaks of him as his good friend, the Dùnadan, and when they sneak away in the Hall of Fire, it sounds like it is not the first time they redraw to look over his verses.

So what has Bilbo done over the next 16 years? Like the Asbjørnsen and Moe, or the Grimm brothers, he has literally collected fairy tales. The Red Book of Westmarch that goes from Bilbo and Frodo to Sam at the end of the story, contains several long stories and verse translated from Elvish by Bilbo. Within this frame, this is what we may call the Silmarillion Traditions. And based on this, he may have written quite a few verses of his own. When he recites for Erestor and other elves in the Hall of Fire, it is clear that this is not the first time he does this, though he does not often get asked for a second hearing.

Finally in Rivendell, Bilbo got his own parlor. After Frodo’s reception dinner, and all the singing and reciting of verse in the Hall of Fire, we are told that Frodo and Bilbo retreats to Bilbo’s room, where they can exit to a veranda that looks out over a garden and the river. We know Bilbo was always fond of his garden, and it is nice to know that the elves of Rivendell provided him with one just outside his room.

If I had to grow old in solitude, I’d like a room at the Rivendell Resort for the Resting, please.

I read Tolkien’s “Canon”, that is, The Hobbit, The Lord of the Rings, and The Silmarillion, every year about Christmas. So also this year.

There is said so much about this book already, so instead of adding more non-interesting chatter to the World, I’d rather again this year show off my latest acquisition to my Hobbit collection: The annotated Hobbit:

This is a true treasure for Hobbit fans. In addition to the actual text, it contains tons of information, like the contemporary context for the book, different versions and updates among the many editions, possible inspirations and related texts, fun facts, illustrations from Hobbit variants of the World,

notes on the meaning of names and places, and so much more.

It even contains the full text of The Quest of Erebor, that was meant as an appendix for The Lord of the Rings, but was cut before its release.

This is the revised and expanded version of The Annotated Hobbit. We owe great thanks to Douglas A. Anderson who must have gone to extremes while researching for this edition.

This book is greatly recommended for those who enjoy being immersed in footnotes, distractions, and fun facts while reading. Ah, that would be the typical Tolkien fan, I guess.

It is another great addition to my ever growing list of Hobbits.

(Update: This post has been updated to reflect changing backup tool from WAL-E to WAL-G. WAL-G is a more modern and faster implementation of cloud backups for postgreSQL)

Several Redpill Linpro customers are now in the kubernetes way of delivery. Kubernetes has changed the way they work, and is acting as an effective catalyst empowering their developers. For these customers, the old-school way of running PostgreSQL is becoming a bit cumbersome:

The typical PostgreSQL installation has been based on bare ...