I recently needed to remove a couple of decommissioned switches from one of our data centres. This turned out to be quite an ordeal. The reason? The ill-conceived way the rack mount brackets used by most data centre switches are designed. In this post, I will use plenty of pictures to explain why that is, and propose a simple solution on how the switch manufacturers can improve this in future.

Rack switch mounting 101

(Note: this is a repost of an article from the Redpill Linpro techblog.)

We have all done it. When SSH asks us this familiar question:

$ ssh redpilllinpro01.ring.nlnog.net
The authenticity of host 'redpilllinpro01.ring.nlnog.net (2a02:c0:200:104::1)' can't be established.
ECDSA key fingerprint is SHA256:IM/o2Qakw4q7vo9dBMLKuKAMioA7UeJSoVhfc5CYsCs.
Are you sure you want to continue connecting (yes/no/[fingerprint])?

…we just answer yes - without bothering to verify the fingerprint shown.

Many of us will even automate answering yes to this question by adding StrictHostKeyChecking accept-new to our ~/.ssh/config file.

Sometimes, SSH will be more ominous:

$ ssh redpilllinpro01.ring.nlnog.net
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the ECDSA key sent by the remote host is
SHA256:IM/o2Qakw4q7vo9dBMLKuKAMioA7UeJSoVhfc5CYsCs.
Please contact your system administrator.
Add correct host key in /home/tore/.ssh/known_hosts to get rid of this message.
Offending ECDSA key in /home/tore/.ssh/known_hosts:448
ECDSA host key for redpilllinpro01.ring.nlnog.net has changed and you have requested strict checking.
Host key verification failed.

This might make us stop a bit and ask ourselves: «Has a colleague re-provisioned this node since the last time I logged in to it?»

Most of the time, the answer will be: «Yeah, probably», followed by something like sed -i 448d ~/.ssh/known_hosts to get rid of the old offending key. Problem solved!

These are all very understandable and human ways of dealing with these kinds of repeated questions and warnings. SSH certainly does «cry wolf» a lot! Let us not think too much about what happens that one time someone actually is «DOING SOMETHING NASTY», though…

Another challenge occurs when maintaining a large number of servers using automation software like Ansible. Manually answering questions about host keys might be impossible, as the automation software likely needs to run entirely without human interaction. The cop out way of ensuring it can do so is to disable host key checking altogether, e.g., by adding StrictHostKeyChecking no to the ~/.ssh/config file.

DNSSEC-validated SSH host key fingerprints in DNS

Fortunately a better way of securely verifying SSH host keys exists - one which does not require lazy and error-prone humans to do all the work.

This is accomplished by combining DNS Security Extensions (DNSSEC) with SSHFP resource records.

To make use of this approach, you will need the following:

1. The SSH host keys published in DNS using SSHFP resource records
2. Valid DNSSEC signatures on the SSHFP resource records
3. A DNS recursive resolver which supports DNSSEC
4. A stub resolver that is configured to request DNSSEC validation
5. A SSH client that is configured to look for SSH host keys in DNS

I will elaborate on how to implement each of these requirements in the sections below.

1. Publishing SSHFP host keys in DNS

The ssh-keygen utility provides an easy way to generate the correct SSHFP resource records based on contents of the /etc/ssh/ssh_host_*_key.pub files. Run it on the server like so:

$ ssh-keygen -r $(hostname --fqdn).
redpilllinpro01.ring.nlnog.net. IN SSHFP 1 1 5fca087a7c3ebebbc89b229a05afd450d08cf9b3
redpilllinpro01.ring.nlnog.net. IN SSHFP 1 2 cdb4cdaf7734df343fd567e0cab92fd6ac5f2754bfef797826dfd4bcf90f0baf
redpilllinpro01.ring.nlnog.net. IN SSHFP 2 1 613f389a36cf33b67d9bd69e381785b275e101cd
redpilllinpro01.ring.nlnog.net. IN SSHFP 2 2 8a07b97b96d826a7d4d403424b97a8ccdb77105b527be7d7be835d02fdb9cd58
redpilllinpro01.ring.nlnog.net. IN SSHFP 3 1 3e46cecd986042e50626575231a4a155cb0ee5ca
redpilllinpro01.ring.nlnog.net. IN SSHFP 3 2 20cfe8d906a4c38abbbe8f5d04c2cab8a00c8a803b51e252a1585f739098b02b

These entries can be copied and pasted directly into the zone file in question so that they are visible in DNS:

$ dig +short redpilllinpro01.ring.nlnog.net. IN SSHFP | sort
1 1 5FCA087A7C3EBEBBC89B229A05AFD450D08CF9B3
1 2 CDB4CDAF7734DF343FD567E0CAB92FD6AC5F2754BFEF797826DFD4BC F90F0BAF
2 1 613F389A36CF33B67D9BD69E381785B275E101CD
2 2 8A07B97B96D826A7D4D403424B97A8CCDB77105B527BE7D7BE835D02 FDB9CD58
3 1 3E46CECD986042E50626575231A4A155CB0EE5CA
3 2 20CFE8D906A4C38ABBBE8F5D04C2CAB8A00C8A803B51E252A1585F73 9098B02B

How to automatically update the SSHFP records in DNS when a node is being provisioned is left as an exercise for the reader, but one nifty little trick is to run something like ssh-keygen -r "update add $(hostname --fqdn). 3600". This produces output that can be piped directly into nsupdate(1).

If you for some reason can not run ssh-keygen on the server, you can also use a tool called sshfp. This tool will take the entries from ~/.ssh/known_hosts (i.e., those you have manually accepted earlier) and convert them to SSHFP syntax.

2. Ensuring the DNS records are signed with DNSSEC

DNSSEC signing of the data in a DNS zone is a task that is usually performed by the DNS hosting provider, so normally you would not need to do this yourself.

There are several web sites that will verify that DNSSEC signatures exist and validate for any given host name. The two best known are:

• DNSViz
• Verisign Labs’s DNSSEC Debugger

If DNSViz shows that everything is «secure» in the left column (example) and the DNSSEC Debugger only shows green ticks (example), your DNS records are correctly signed and the SSH client should consider them secure for the purposes of SSHFP validation.

If DNSViz and the DNSSEC Debugger give you a different result, you will most likely have to contact your DNS hosting provider and ask them to sign your zones with DNSSEC.

3. A recursive resolver that supports DNSSEC

The recursive resolver used by your system must be capable of validating DNSSEC signatures. This can be verified like so:

$ dig redpilllinpro01.ring.nlnog.net. IN SSHFP +dnssec
[...]
;; flags: qr rd ra ad; QUERY: 1, ANSWER: 7, AUTHORITY: 0, ADDITIONAL: 1
[...]

Look for the ad flag («Authenticated Data») in the answer, If present, it means that the DNS server confirms that the supplied answer has a valid DNSSEC signature and is secure.

If the ad flag is missing when querying a hostname known to have valid DNSSEC signatures (e.g., redpilllinpro01.ring.nlnog.net), your DNS server is probably not DNSSEC capable. You can either ask your ISP or IT department to fix that, or change your system use a public DNS server known to be DNSSEC capable.

Cloudflare’s 1.1.1.1 is one well-known example of a public recursive resolver that supports DNSSEC. To change to it, replace any pre-existing nameserver lines in /etc/resolv.conf with the following:

nameserver 1.1.1.1
nameserver 2606:4700:4700::1111
nameserver 1.0.0.1
nameserver 2606:4700:4700::1001

4. Configuring the system stub resolver to request DNSSEC validation

By default, the system stub resolver (part of the C library) does not set the DO («DNSSEC OK») bit in outgoing queries. This prevents DNSSEC validation.

DNSSEC is enabled in the stub resolver by enabling EDNS0. This is done by adding the following line to /etc/resolv.conf:

options edns0

5. Configuring the SSH client to look for host keys in DNS

Easy peasy: either you can add the line VerifyHostKeyDNS yes to your ~/.ssh/config file, or you can supply it on the command line using ssh -o VerifyHostKeyDNS=yes.

Verifying that it works

If you have successfully implemented steps 1-5 above, we are ready for a test!

If you have only done step 3-5, you can still test using redpilllinpro01.ring.nlnog.net (or any other node in the NLNOG RING for that matter). The NLNOG RING nodes will respond to SSH connection attempts from everywhere, and they have all DNSSEC-signed SSHFP records registered.

$ ssh -o UserKnownHostsFile=/dev/null -o VerifyHostKeyDNS=yes no-such-user@redpilllinpro01.ring.nlnog.net
no-such-user@redpilllinpro01.ring.nlnog.net: Permission denied (publickey).

Ignore the fact that the login attempt failed with «permission denied» - this test was a complete success, as the SSH client did not ask to manually verify the SSH host key.

UserKnownHostsFile=/dev/null was used to ensure that any host keys manually added to ~/.ssh/known_hosts at an earlier point in time would be ignored and not skew the test.

It is worth noting that SSH does not add host keys verified using SSHFP records to the ~/.ssh/known_hosts file - it will validate the SSHFP records every time you connect. This ensures that even if the host keys change, e.g., due to the server being re-provisioned, the ominous «IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY» warning will not appear - provided the SSHFP records in DNS have been updated, of course.

Trusting the recursive resolver

The setup discussed in this post places implicit trust in the recursive resolver used by the system. That is, you will be trusting it to diligently validate any DNSSEC signatures on the responses it gives you, and to only set the «Authenticated Data» flag if those signatures are truly valid.

You are also placing trust in the network path between the host and the recursive resolver. If the network is under control by a malicious party, the DNS queries sent from your host to the recursive resolver could potentially be hijacked and redirected to a rogue recursive resolver.

This means that an attacker with the capability to hijack or otherwise interfere with both your SSH and DNS traffic could potentially set up a fraudulent SSH server for you to connect to, and make your recursive resolver lie about the SSH host keys being correct and valid according to DNSSEC. The SSH client will not be able to detect this situation on its own.

In order to detect such attacks, it is necessary for your host to double-check the validity of answers received from the recursive resolver by performing local DNSSEC validation. How to set up this will be the subject of a future post here on the Redpill Lipro techblog. Stay tuned!

We have all done it. When SSH asks us this familiar question:

$ ssh redpilllinpro01.ring.nlnog.net The authenticity of host 'redpilllinpro01.ring.nlnog.net (2a02:c0:200:104::1)' can't be established. ECDSA key fingerprint is SHA256:IM/o2Qakw4q7vo9dBMLKuKAMioA7UeJSoVhfc5CYsCs. Are you sure you want to continue connecting (yes/no/[fingerprint])? 

…we just answer yes - without bothering to verify the fingerprint shown.

Many of us will even automate answering yes to this question by adding StrictHostKeyChecking accept-new to our ~/.ssh/config ...

These are essentially my notes on setting up a single-node Kubernetes cluster at home. Every time I set up an instance I have to dig through lots of posts, articles and documentation, much of it contradictory or out-of-date. Hopefully this distilled and much-abridged version will be helpful to someone else.

Recently, references to a "new PostgreSQL vulnerability" has been circling on social media (and maybe elsewhere). It's even got it's own CVE entry. The origin appears to be a blogpost from Trustwave.

So is this actually a vulnerability? (Hint: it's not) Let's see:

In our project, we have successfully implemented SAML (Security Assertion Markup Language) 2.0 with our Alfresco Content Service v5.2.0. We use AD(Active Directory) to sync users and groups into Alfresco System.

The Varnish Cache project recently released a new upstream version 6.2 of Varnish Cache. I updated the fedora rawhide package yesterday. I have also built a copr repo with varnish packages for el6 and el7 based on the fedora package. A snapshot of matching varnish-modules (based on Nils Goroll’s branch) is also available.

Packages are available at https://copr.fedorainfracloud.org/coprs/ingvar/varnish62/.

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

Recently, the Varnish Cache project released an updated upstream version 4.1.11 of Varnish Cache. This is a maintenance and stability release of varnish 4.1, which you may consider as the former “LTS” branch of varnish. I have updated my varnish 4.1 copr repo with packages for el6 and el7. A selection of matching vmods is also included in the copr repo.

Packages are available at https://copr.fedorainfracloud.org/coprs/ingvar/varnish41/

The following vmods are available:

Included in varnish-modules:
vmod_bodyaccess
vmod_cookie
vmod_header
vmod_saintmode
vmod_softpurge
vmod_tcp
vmod_var
vmod_vsthrottle
vmod_xkey

Packaged separately:
vmod-curl
vmod-digest
vmod-geoip
vmod-memcached
vmod-rfc6052
vmod-rtstatus
vmod-uuid
vmod-vslp

And varnish-agent is also thrown in.

Please test and report bugs. If there is enough interest, I may consider pushing these to fedora as well.

–

Varnish Cache is a powerful and feature rich front side web cache. It is also very fast, and that is, fast as in powered by The Dark Side of the Force. On steroids. And it is Free Software.

Redpill Linpro is the market leader for professional Open Source and Free Software solutions in the Nordics, though we have customers from all over. For professional managed services, all the way from small web apps, to massive IPv4/IPv6 multi data center media hosting, and everything through container solutions, in-house, data center, and cloud, contact us at www.redpill-linpro.com.

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.

When I read through the first chapter of The Fellowship of the Ring again, I stumbled over all those small things that remind about The Hobbit. Going through them more systematically, it is clear that Tolkien started out wanting to create a sequal, and he uses a lot of small details to bind the first chapters of the new book closely to the previous one.

Starting with the title, A long expected party, of course closely mimicking the Hobbit’s first chapter An unexpected party. During Bilbo’s feast, Gandalf shows off his firework display, as he did on the Old Tooks parties a long time ago, according to The Hobbit. The firework elements themselves reminiscing parts of the story of the Hobbit. The trees of Greenwood the Great (or Mirkwood if you like), complete with butterflies. Then there are the eagles, a thunderstorm, an embattled army of elves with silver spears, and of course, the mountain and the dragon as the Grand Finale. Then Bilbo holds his speech, reminding the bored guests about his coming to Esgaroth on his 50th birthday, before he makes his special exit.

After Bilbo has disappeared in a flash and a bang, and left 144 flabbergasted guests back in the pavillion, we follow him and Gandalf back into Bag End. Here we see him pulling out his old treasures from The Hobbit; His sword Sting, the green cloak and hood that he borrowed from Dwalin (rather too large for him), and of course, his journey’s diary, the actual Hobbit book itself, nicely written into the story, and, as he tells Gandalf, he has written an end for it: “And he lived happily ever after, to the end of his days”, like the book actually ends. Gandalf reminds Bilbo about the will – the contract with Frodo if you like, that should be put on the same place as Bilbo found his own contract 77 years earlier, by the clock on the mantlepiece. He then sets out with dwarves, again.

At Crickhollow, the evening before the hobbits set out together, Merry and Pippin has made a song mimicking the song the Dwarves sang before Thorin and company set out. Out on the road, Frodo and his merry followers visit a tavern, like Thorin’s travelling party is said to have done too. They enter the wilder region, and Frodo and company sees the hills with old ruins on them, just like Bilbo did. After crossing the same stone bridge, they even discover the trolls that Gandalf tricked to stay out until the dawn made them to stone. Finally, the second book of the Fellowship starts with a rest in Elrond’s house, as did Bilbo.

Tolkien’s eye for details gives the fans of The Hobbit great value for their money, and a world full of small well-known nuggets to get comfortable before the quest takes off into the parts of Middle-Earth where they have not travelled before.

Are there more hints of the Hobbit in The Fellowship of the Ring than those listed here? I probably missed a lot of them.

Last year I wrote a post about my difficulties getting IPv6 to work with the Huawei ME906s-158 WWAN module. I eventually gave up and had it replaced with a module from another manufacturer.

Not long ago, though, I received an e-mail from another ME906s-158 owner who told me that for him, IPv6 worked just fine. That motivated me to brush the dust off my module and try again. This time, I figured it out! Read on for the details.

The Carrier PLMN List

The ME906s-158 comes with a built-in list of nine different PLMN profiles. This list can be managed with proprietary AT command AT^PLMNLIST, which is documented on page 209 of the module’s AT Command Interface Specification.

To interact with the AT command interface, use the option driver. More details on that here.

This is the complete factory default list:

AT^PLMNLIST?

^PLMNLIST: "00000",00000,23106,26207,23802,23806
^PLMNLIST: "20205",26801,20205,26202,26209,27201,27402,50503,54201,53001,40401,40405,40411,40413,40415,40420,40427,40430,40443,40446,40460,40484,40486,40488,40566,40567,405750,405751,405752,405753,405754,405755,405756,20404,20601,20810,21401,21670,22210,22601,23003,23415,24405,24802,27602,27801,28001,28602,28802,29340,42702,60202,62002,63001,63902,64004,64304,65101,65501,90128,23201,28401,64710,46601,42602,22005,41302,29403,50213,50219,21910,25001,27077,52505,23801,40004,42403,46692,52503,73001,24602,24705
^PLMNLIST: "26201",26201,23001,20416,23203,23207,21901,21630,23102,29702,29401,26002,20201,23431,23432
^PLMNLIST: "21403",20610,20801,20802,21403,22610,23101,23430,23433,26803,26003
^PLMNLIST: "50501",50501,50571,50572
^PLMNLIST: "22801",22801,29501
^PLMNLIST: "21407",21405,21407,23402
^PLMNLIST: "99999",24491,24001,23820
^PLMNLIST: "50502",50502 

OK

Each ^PLMNLIST: line represents a single pre-defined PLMN profile, identified by the first MCCMNC number (in double quotes). The "26201" profile is for Deutsche Telekom, the "50501" profile is for Telstra Mobile, and so on.

The rest of the numbers on each line is a list of MCCMNCs that will use that particular profile. For example, if you have a SIM card issued by T-Mobile Netherlands (MCCMNC 20416), then the ME906s-158 will apply the Deutsche Telekom profile ("26201").

Unfortunately, the documentation offers no information on how the PLMN profiles differ and how they change the way the module work.

My provider (Telenor Norway, MCCMNC 24201) is not present in the factory default list. In that case, the module appears to use the "00000" PLMN profile («Generic») as the default, and that one disables IPv6! Clearly, Huawei haven’t read RFC 6540…

In any case, this explains why I failed to make IPv6 work last year, and why it worked fine for the guy who mailed me - his provider was among those that used the Deutsche Telekom PLMN profile by default.

Modifying the Carrier PLMN List

The solution seems clear: I need to add my provider’s MCCMNC to an IPv6-capable PLMN profile. The Deutsche Telekom one would probably work, but "99999" («Generic(IPV4V6)») seems like an even more appropriate choice.

Adding MCCMNCs is done with AT^PLMNLIST=1,"$PLMNProfile","$MCCMNC", like so:

AT^PLMNLIST=1,"99999","24201"

OK

(To remove an MCCMNC, use AT^PLMNLIST=0,"$PLMNProfile","$MCCMNC" instead.)

I can now double check that the "99999" PLMN profile includes 24201 for Telenor Norway:

AT^PLMNLIST="99999"

^PLMNLIST: "99999",24491,24001,23820,24201

OK

To make the new configuration take effect, it appears to be necessary to reset the module. This can be done with the AT^RESET command.

Confirming that IPv6 works

It is possible to query the module directly about IPv6 support in at least two different ways:

AT^IPV6CAP?

^IPV6CAP: 7

OK

AT+CGDCONT=?

+CGDCONT: (0-11),"IP",,,(0-2),(0-3),(0,1),(0,1),(0-2),(0,1)
+CGDCONT: (0-11),"IPV6",,,(0-2),(0-3),(0,1),(0,1),(0-2),(0,1)
+CGDCONT: (0-11),"IPV4V6",,,(0-2),(0-3),(0,1),(0,1),(0-2),(0,1)

OK

The ^IPV6CAP: 7 response means «IPv4 only, IPv6 only and IPv4v6» (cf. page 336 of the AT Command Interface Specification), and the +CDGCONT: responses reveal that the modem is ready to configure PDP contexts using the IPv6-capable IP types. Looking good!

Of course, the only test that really matters is to connect it:

$ mmcli --modem 0 --simple-connect=apn=telenor.smart,ip-type=ipv4v6
successfully connected the modem
$ mmcli --bearer 0                                                                                            
Bearer '/org/freedesktop/ModemManager1/Bearer/0'
  -------------------------
  Status             |   connected: 'yes'
                     |   suspended: 'no'
                     |   interface: 'wwp0s20f0u3c3'
                     |  IP timeout: '20'
  -------------------------
  Properties         |         apn: 'telenor.smart'
                     |     roaming: 'allowed'
                     |     IP type: 'ipv4v6'
                     |        user: 'none'
                     |    password: 'none'
                     |      number: 'none'
                     | Rm protocol: 'unknown'
  -------------------------
  IPv4 configuration |   method: 'static'
                     |  address: '10.169.198.77'
                     |   prefix: '30'
                     |  gateway: '10.169.198.78'
                     |      DNS: '193.213.112.4', '130.67.15.198'
                     |      MTU: '1500'
  -------------------------
  IPv6 configuration |   method: 'static'
                     |  address: '2a02:2121:2c4:7105:5a2c:80ff:fe13:9208'
                     |   prefix: '64'
                     |  gateway: '::'
                     |      DNS: '2001:4600:4:fff::52', '2001:4600:4:1fff::52'
                     |      MTU: '1500'
  -------------------------
  Stats              |          Duration: '59'
                     |    Bytes received: 'N/A'
                     | Bytes transmitted: 'N/A'

Success!

Recently, the Varnish Cache project released an updated upstream version 6.0.2 of Varnish Cache. This is a maintenance and stability release of varnish 6.0, which you may consider as the current “LTS” branch of varnish. I have updated the fedora rawhide package, and also updated the varnish 6.0 copr repo with packages for el6 and el7 based on the fedora package. A selection of matching vmods is also included in the copr repo.

Packages are available at https://copr.fedorainfracloud.org/coprs/ingvar/varnish60/

The following vmods are available:

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

Packaged separately:
vmod-curl
vmod-digest
vmod-geoip
vmod-memcached
vmod-querystring
vmod-uuid

Please test and report bugs. If there is enough interest, I may consider pushing these to fedora as well.

–

Varnish Cache is a powerful and feature rich front side web cache. It is also very fast, and that is, fast as in powered by The Dark Side of the Force. On steroids. And it is Free Software.

Redpill Linpro is the market leader for professional Open Source and Free Software solutions in the Nordics, though we have customers from all over. For professional managed services, all the way from small web apps, to massive IPv4/IPv6 multi data center media hosting, and everything through container solutions, in-house, data center, and cloud, contact us at www.redpill-linpro.com.

It's now almost a month since PGConf.EU 2018 in Lisbon. PGConf.EU 2018 was the biggest PGConf.EU ever, and as far as I know the biggest PostgreSQL community conference in the world! So it's time to share some of the statistics and feedback.

I'll start with some attendee statistics:

451 registered attendees 2 no-shows 449 actual present attendees

Of these 451 registrations, 47 were sponsor tickets, some of who were used by sponsors, and some were given away to their customers and partners. Another 4 sponsor tickets went unused.

Another 52 were speakers.

This year we had more cancellations than we've usually had, but thanks to having a waitlist on the conference we managed to re-fill all those spaces before the event started.

When developing software it makes sense to be able to work on local files, while the source code should be served from a controlled environment (a container) to prevent pollution of the developer workstation.

In this article I will describe the evolution of a development workflow for deploying applications on OpenShift. The ultimate goal is to make it possible to maximize dev/prod parity, while minimizing the idle time in the change/test cycle.

The 12-factor app presents a number of guidelines to achieve DevOps compliancy. One of the guidelines specifies dev/prod parity, which in OpenShift can be implemented by re-using a single container image for all steps within an applications lifecycle. Here we will describe how dev/prod parity can be achieved within OpenShift by using the pipeline support of the OpenShift BuildConfig object type.

I recently ran into the need with a customer to track the usage of a specific key throughout the schema. Basically, "what are all the tables and columns referencing this key, directly or indirectly". Luckily, with a little bit of catalog query, that's not hard:

WITH RECURSIVE what (tbl) AS (
   VALUES ('public.tt')
),
t (oid, key, constrid) AS (
 SELECT tbl::regclass::oid, conkey, NULL::oid
  FROM what INNER JOIN pg_constraint ON (contype='p' AND conrelid=tbl::regclass)
UNION ALL
 SELECT conrelid, conkey, c.oid
 FROM pg_constraint c
 INNER JOIN t ON (c.confrelid=t.oid AND c.confkey=t.key)
 WHERE contype='f'
)
SELECT nspname, relname, key, ARRAY(
    SELECT attname FROM pg_attribute a WHERE a.attrelid=t.oid AND attnum=ANY(key)
  )
FROM t
INNER JOIN pg_class cl ON cl.oid=t.oid
INNER JOIN pg_namespace n ON n.oid=cl.relnamespace

The output can be similar to:

 nspname | relname | key | array 
---------+---------+-----+-------
 public  | tt      | {1} | {ttt}
 public  | foo1    | {1} | {a}
 public  | foo2    | {3} | {z}

for a single column key (tt being the table with the primary key in, and the foo1 and foo2 tables referencing it directly or through the other one), or:

 nspname | relname |  key  | array 
---------+---------+-------+-------
 public  | m1      | {1,2} | {a,b}
 public  | m2      | {1,2} | {a,b}

for a multi-column foreign key.

In this particular use-case, it was an efficient way to track down key usage where naming standards for using the key had not always been followed. And of course, we also found a couple of cases where the column had the correct name but lacked the actual FOREIGN KEY definition, but that was done by just looking at the column names.

Recently, the Varnish Cache project released an updated upstream version 6.0.1 of Varnish Cache. This is a maintenance and stability release of varnish 6.0. I have updated the fedora rawhide package, and also updated the varnish 6.0 copr repo with packages for el6 and el7 based on the fedora package. A selection of matching vmods is also included in the copr repo.

Packages are available at https://copr.fedorainfracloud.org/coprs/ingvar/varnish60/

The following vmods are available:

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

Packaged separately:
vmod-curl
vmod-digest
vmod-geoip
vmod-memcached
vmod-querystring
vmod-uuid

Please test and report bugs. If there is enough interest, I may consider pushing these to fedora as well.

–

Varnish Cache is a powerful and feature rich front side web cache. It is also very fast, and that is, fast as in powered by The Dark Side of the Force. On steroids. And it is Free Software.

Redpill Linpro is the market leader for professional Open Source and Free Software solutions in the Nordics, though we have customers from all over. For professional managed services, all the way from small web apps, to massive IPv4/IPv6 multi data center media hosting, and everything through container solutions, in-house, data center, and cloud, contact us at www.redpill-linpro.com.

Summer vacation times are over. Well, for some of us at least, clearly some are still lucky enough to be off, which is showing itself a bit (see below). But as both conference organisation and participation throughout the rest of the year is starting to be clear, I figure it's time to share some updates around different ones.

Postgres Open SV

First of all - if you haven't already, don't forget to register for Postgres Open SV in San Francisco in two weeks time! Registration for the main US West Coast/California PostgreSQL community conference will close soon, so don't miss your chance. I'm looking forward to meeting many old and new community members there.

PostgreSQL Conference Europe

Next up after Postgres Open will be pgconf.eu, the main European PostgreSQL community conference of 2018. The planning for this years conference is at full speed, but unfortunately we are slightly behind. In particular, we were supposed to be notifying all speakers today if they were accepted or not, and unfortunately our program committee are a bit behind schedule on this one. We had over 200 submissions this year which makes their work even bigger than usual. But speakers will be receiving their notification over the upcoming couple of days.

Hopefully once all speakers have been able to confirm their attendance, we will also have a schedule out soon. Until then, you can always look at the list of accepted talks so far. This list is dynamically updated as speakers get approved and confirm their talks.

We have already sold approximately half of the tickets that we have available this year, so if you want to be sure to get your spot, we strongly recommend that you register as soon as you can! And if you want to attend the training sessions, you should hurry even more as some are almost sold out!

PGConf.ASIA

Work on the program committee of PGConf.ASIA has also been going on over the late summer, and is mostly done! The schedule is not quite ready yet, but expected out shortly. You can look forward to a very interesting lineup of speakers, so if you are in the Asian region, I strongly recommend keeping an eye out for when the registration opens, and join us in Tokyo!

FOSDEM PGDay

As has been announced, PostgreSQL Europe will once again run a FOSDEM PGDay next to the big FOSDEM conference in Brussels in February next year. We hope to also run our regular booth and developer room during FOSDEM, but those are not confirmed yet (more info to come). The Friday event, however, is fully confirmed. Of course not open for registration yet, but we'll get there.

Nordic PGDay

Nordic PGDay has been confirmed for March 19th next year. The format will be similar to previous years, and we will soon announce the location. For now, mark your calendars to make sure you don't double book! And rest assured, the conference will take place somewhere in the Nordics!

Usergroups and PGDays

Then there are a number of smaller events of course. Next week, I will speak at the Prague PostgreSQL Meetup. We should be kicking off the Stockholm usergroup. PDXPUG runs a PGDay in Portland in September (which I unfortunately won't be able to attend). In general, it seems like usergroups are starting to get going again after the summer break, so check with your local group(s) what's happening!

This post will show you how to get started with using Terraform, an open-source tool that lets you build your infrastructure using code.