As Joe just announced, all ftp services at ftp.postgresql.org has been shut down.

That of course doesn't mean we're not serving files anymore. All the same things as before area still available through https. This change also has an effect on any user still accessing the repositories (yum and apt) using ftp.

There are multiple reasons for doing this. One is that ftp is an old protocol and in a lot of ways a pain to deal with when it comes to firewalling (both on the client and server side).

The bigger one is the general move towards encrypted internet. We stopped serving plaintext http some time ago for postgresql.org, moving everything to https. Closing down ftp and moving that over to https as well is another step of that plan.

There are still some other plaintext services around, and our plan is to get rid of all of them replacing them with secure equivalents.

I recently purchased a new laptop, an HP EliteBook 820 G4. When ordering, I was given the choice of two different LTE WWAN modems: the HP lt4120 and the HP lt4132. The former is a rebranded Foxconn T77W595, while the latter is a rebranded Huawei ME906s-158.

Both modems cost about the same and there were reports of people getting them both working under Linux, so it didn’t seem to matter much which of them I chose. I eventually decided on the lt4132, the primary reason being that its specifications clearly state that it supports IPv6. This made the lt4132 seem like the safe choice, as I was unable to easily confirm that the lt4120 supported IPv6.

I was wrong. I should have opted for the lt4120. Read on for the details.

Linux support

The lt4132 did not work out of the box with my preferred Linux distribution Fedora 26; ModemManager didn’t recognise it as a supported modem.

The modem was visible in the output from usb-devices, however:

T:  Bus=01 Lev=01 Prnt=01 Port=02 Cnt=02 Dev#=  3 Spd=480 MxCh= 0
D:  Ver= 2.00 Cls=00(>ifc ) Sub=00 Prot=ff MxPS=64 #Cfgs=  3
P:  Vendor=03f0 ProdID=a31d Rev=01.02
S:  Manufacturer=HP
S:  Product=HP lt4132 LTE/HSPA+ 4G Module
S:  SerialNumber=0123456789ABCDEF
C:  #Ifs= 7 Cfg#= 2 Atr=a0 MxPwr=2mA
I:  If#= 0 Alt= 0 #EPs= 1 Cls=02(commc) Sub=06 Prot=00 Driver=cdc_ether
I:  If#= 1 Alt= 0 #EPs= 2 Cls=0a(data ) Sub=06 Prot=00 Driver=cdc_ether
I:  If#= 2 Alt= 0 #EPs= 3 Cls=ff(vend.) Sub=06 Prot=10 Driver=(none)
I:  If#= 3 Alt= 0 #EPs= 2 Cls=ff(vend.) Sub=06 Prot=13 Driver=(none)
I:  If#= 4 Alt= 0 #EPs= 2 Cls=ff(vend.) Sub=06 Prot=12 Driver=(none)
I:  If#= 5 Alt= 0 #EPs= 2 Cls=ff(vend.) Sub=06 Prot=14 Driver=(none)
I:  If#= 6 Alt= 0 #EPs= 2 Cls=ff(vend.) Sub=06 Prot=1b Driver=(none)

The problem here is Cfg#= 2, indicating that the modem is in configuration 2. The Linux kernel selects configuration 2 by default, but that does not work with ModemManager. Configuration 3 ( MBIM mode) is a much better choice.

Changing to configuration 3 is easy enough. Note that it is essential to first deconfigure the device by selecting configuration 0 and wait a few milliseconds. Going directly from 2 to 3 does not work. Thus:

$ echo 0 > /sys/bus/usb/devices/1-3/bConfigurationValue
$ sleep 1
$ echo 3 > /sys/bus/usb/devices/1-3/bConfigurationValue

The 1-3 part might not be correct for your system. If it’s not, grep lt4132 /sys/bus/usb/devices/*/product will probably tell you what the correct sysfs device path is.

This made ModemManager recognise the modem. At this point I could run nmcli con add type gsm ifname '*' apn telenor.smart to make NetworkManager successfully establish a mobile data connection. Well, ostensibly - it still didn’t quite work. All the data traffic was being blackholed. This was solved by enabling the ndp_to_end USB quirk, like so:

$ echo Y > /sys/class/net/wwp0s20f0u3c3/cdc_ncm/ndp_to_end

In the future, the lt4132 will be better supported out of the box. It will not be necessary to manually deal with these settings; the upcoming version of usb_modeswitch will automatically select USB configuration 3, and a patch I wrote to automatically enable the ndp_to_end USB quirk will be part of the Linux kernel starting with version 4.13.

In the interim, however, it is easy enough to automate the application of these tweaks by using udev rules. Simply create a file called, e.g., /etc/udev/rules.d/hp-lt4132.rules and add the following three lines to it:

ACTION=="add|change", SUBSYSTEM=="usb", ATTR{idVendor}=="03f0", ATTR{idProduct}=="a31d", ATTR{bConfigurationValue}!="3", ATTR{bConfigurationValue}:="0"
ACTION=="add|change", SUBSYSTEM=="usb", ATTR{idVendor}=="03f0", ATTR{idProduct}=="a31d", ATTR{bConfigurationValue}!="3", RUN+="/bin/sh -c 'sleep 1; echo 3 > %S%p/bConfigurationValue'"
ACTION=="add|change", SUBSYSTEM=="net", ATTRS{idVendor}=="03f0", ATTRS{idProduct}=="a31d", ATTR{cdc_ncm/ndp_to_end}=="N", ATTR{cdc_ncm/ndp_to_end}:="Y"

That’s all it takes. (You will probably want to change the vendor/product IDs 03f0/a31d if you don’t have the same HP-branded flavour of the Huawei ME906s-158 I do, though.)

Lack of IPv6 support

The Huawei ME906s-158 product page clearly specifies that the modem supports IPv6. Turns out, this is a lie - or at best, extremely misleading.

When I attempted to establish an IPv6 mobile data connection, it would just fail:

$ mmcli -m 0 --simple-connect=apn=telenor.smart,ip-type=ipv6
error: couldn't connect the modem: 'GDBus.Error:org.freedesktop.libmbim.Error.Status.NoDeviceSupport: NoDeviceSupport'

Requesting dual-stack with ip-type=ipv4v6 instead would ostensibly succeed, but it would only yield an IPv4-only connection.

I also tried the modem under Windows 10. It was only able to get IPv4 connectivity there too, so it seems clear that this is a firmware issue. For the record, I’m on the latest firmware available fom HP, version 11.617.13.00.00.

In order to figure out what was going on, I used the option serial port driver to interact with the modem’s AT command interface:

$ modprobe option
$ echo 03f0 a31d > /sys/bus/usb-serial/drivers/option1/new_id
$ cat /dev/ttyUSB0 &

The first thing to check is the output of the AT+CGDCONT=? command, a 3GPP-standardised command which returns the supported data types:

$ echo $'AT+CGDCONT=?\r' > /dev/ttyUSB0
+CGDCONT: (0-11),"IP",,,(0-2),(0-3),(0,1),(0,1),(0-2),(0,1)

OK

This is a smoking gun: there should have been two more lines returned here, one with "IPV6" and another with "IPV4V6" in the second comma-separated field. This output is essentially the modem stating «I only support IPv4».

Huawei has published an extensive document that details all the various AT commands supported by the modem. One of these is AT^IPV6CAP, a Huawei proprietary command to «Query IPv6 Capability» (see page 281). The possible return codes and their meanings are documented as follows:

1 IPv4 only
2 IPv6 only
7 IPv4 only, IPv6 only and IPv4v6

So let’s see what it says:

$ echo $'AT^IPV6CAP?\r' > /dev/ttyUSB0
^IPV6CAP: 1

OK

This appears to confirm what the AT+CGDCONT=? command already told us, the modem is IPv4-only. However, the AT^IPV6CAP=? command did give me a little bit of hope:

$ echo $'AT^IPV6CAP=?\r' > /dev/ttyUSB0
^IPV6CAP: (1,2,7)

OK

I interpret this to mean that the modem actually does contain support for IPv6 and IPv4v6; only that it is currently in an IPv4-only operational mode. The question then becomes: how to change the operational mode to 7? I have no idea, unfortunately. It’s not AT^IPV6CAP=7, for what it’s worth.

I eventually had to give up on making IPv6 work with this modem. Perhaps it will be fixed in a future firmware update, but until then my recommendation is clear: stay away from the Huawei ME906s-158 / HP lt4132 LTE modem!

Support tickets were opened with both HP and Huawei support about the issue, by the way. Despite several rounds of escalating their respective cases, none of them were able to figure out a solution. HP support eventually gave up on solving the case and shipped me a replacement HP lt4120 free of charge instead. That did the trick; it turns out the lt4120 supports IPv6 perfectly. I’ll have to commend HP for good customer support here; they obviously considered the lack of IPv6 support to be a real defect and did what was necessary to fix the problem in the most efficient manner.

When I created this blog a couple of years ago, I was disappointed to find out that the GitHub Pages service (GHP) did not support IPv6. This was due to the fact that GHP’s CDN provider Fastly didn’t support IPv6.

To work around this problem I ended up inserting a dual-stacked HTTP proxy service in front of my (IPv4-only) GHP-hosted blog. While it was hardly ideal, it did the trick.

The other day I was pleasantly surprised to find out that I no longer need this hack: Fastly and GHP now do support IPv6! IPv6 appears to have been enabled for https://toreanderson.github.io and all other GHP sites without requiring explicit opt-in. Perfect!

When did it happen? Well, it seems Fastly announced IPv6 availability on the 31st of March (after having had it in limited beta since last summer). Assuming Twitter is a reliable indicator, IPv6 was enabled for GHP specifically sometime between the 10th of April and the 28th of May.

I was quite critical of Fastly in those old posts, so it’s only fair that I congratulate them now. Well done, Fastly - I’m very happy to see you get on the IPv6 bandwagon!

When you create a table in PostgreSQL, it gets assigned default permissions and a default owner. We can alter the default privileges using the very useful ALTER DEFAULT PRIVILEGES command (a PostgreSQL extension to the standard). However, there isn't much we can do about the owner, which will get set to the role that is currently active. That is, it's the main login role, or another role if the user has run the SET ROLE command before creating the table.

A fairly common scenario that is not well handled here is when a number of end-users are expected to cooperate on the tables in a schema all the way, including being able to create and drop them. And this is a scenario that is not very well handled by the built-in role support, due to the ownership handling. Fortunately, this is something where we can once again use an event trigger to make the system do what we need.

I’ve been using vim as my editor of choice ever since I started learning Linux, and something that has been bothering me for a while is how vim handles pasting.

Say I want to paste a large bit of code into a terminal running vim. Before I do this I have to type:

:set paste

When everything is pasted, I turn it off with:

:set paste!

or:

:set nopaste

The command :set paste prevents vim from auto-indenting the code I’ve just pasted.

Luckily, as it most often goes, there is a solution. Why I haven’t bothered to actually find the answer till recently is a whole other matter.

As it turns out, my terminal of choice (which currently is rxvt-unicode) supports something called bracketed paste mode.

In short, when bracketed paste mode is set, pasted text is bracketed with control sequences so that the program can differentiate between pasted text and typed-in text.

Let’s stay that I copied the text:

Hello World!

from another program. When I paste it into my terminal, if it supports bracketed paste mode, it actually sends the text:

\e[200~Hello World!\e[201~

Now the thing is to let vim know how to watch out for these control sequences, and tell it what to do. Paste the following code into your .vimrc:

let &t_SI .= "\<Esc>[?2004h"
let &t_EI .= "\<Esc>[?2004l"

inoremap <special> <expr> <Esc>[200~ XTermPasteBegin()

function! XTermPasteBegin()
set pastetoggle=<Esc>[201~
set paste
return ""
endfunction

And that should reduce your use of :set paste quite a bit!

I’ve often used Python’s SimpleHTTPServer to simply share a directory with someone over a network, it being either local or the Internet. In case you don’t know how it works, it’s simple. To start a HTTP server, at your current location, type:

python -m SimpleHTTPServer

and the result:

jorge@applepie:~ $ python -m SimpleHTTPServer 8080
Serving HTTP on 0.0.0.0 port 8080 ...

It listens on all IPv4 interfaces, and binds to the port you specify, which in my case is 8080. The person on the other side will then be able to access the files in the directory from the outside by going to http://server1.example.com:8080, provided that your machine has the hostname server1.example.com, and that you have the port 8080 forwarded to the IP of server1.

But what if you want to provide a secure connection, say over SSL? SimpleHTTPServer has no built in way of doing this.

But behold ssl, Python’s built in SSL-module!

To create a secure connection for your SimpleHTTPServer, first create a self signed certificate by running the following command (if you don’t have a proper SSL-certificate, that is):

openssl req -x509 -newkey rsa:4096 -keyout server1.example.com.key -out server1.example.com.key -days 365 -nodes

Now create a script named shttps.py that contains the following code:

#!/usr/bin/env python

import BaseHTTPServer, SimpleHTTPServer
import ssl

## Variables you can modify

bind_to_address = ''
server_port = 8080
ssl_key_file = "/etc/ssl/certs/localcerts/server1.example.com.key"
ssl_certificate_file = "/etc/ssl/certs/localcerts/server1.example.com.pem"


## Don't modify anything below

httpd = BaseHTTPServer.HTTPServer((bind_to_address, server_port), SimpleHTTPServer.SimpleHTTPRequestHandler)
httpd.socket = ssl.wrap_socket (httpd.socket, server_side=True,
                                keyfile=ssl_key_file,
                                certfile=ssl_certificate_file)
httpd.serve_forever()

The only thing that needs further explanation is the variable bind_to_address. Fill this in with the text localhost if you want it to only listen to 127.0.0.1. Leave it blank to have it listen to all IPv4 interfaces (0.0.0.0).

Now that the certificate and key is all in place, and the script has been created, make it executable with:

chmod +x shttps.py

Go to the folder you’d like to share the contents of, and run the script:

jorge@applepie:~ $ ls
foo/ shttps.py
jorge@applepie:~ $ cd foo
jorge@applepie:~/foo $ ls
hello.txt world.txt
jorge@applepie:~/foo $ ../shttps.py

The result when you visit https://server1.example.com:8080?Because there is no third party verification it’s listed as insecure, but it should do the trick well enough for sharing files with others.

If you however do want a free SSL certificate for a more permanent setup, I suggest LetsEncrypt! Check out https://letsencrypt.org/getting-started/ for more information.

The Raspberry Pi 3 is the third generation Raspberry Pi, on this i will be installing Mulesoft enterprise runtime standalone with latest Java 8 running inside a Docker container. The Instance will register itself with Anypoint platform ...

In a previous post I discussed a way to find out which transaction dropped a table by examining the transaction log, in order to set a restore point to right before the table was dropped.

But what if we have the luxury of planning ahead (right? Well, let's call it the second time it happens?). Shouldn't we be able to log which transaction dropped a table, and use that? Of course we should.

The first thing one tries is then of course something like this in postgresql.conf:

log_statement='ddl'
log_line_prefix = '%t [%u@%d] <%x> '

to include the transaction id of the table. Unfortunately:

2017-02-12 12:16:39 CET [mha@postgres] <0> LOG:  statement: drop table testtable;

The 0 as a transaction id indicates that this command was run in a virtual transaction, and did not have a real transaction id. The reason for this is that the statement logging happens before the statement has actually acquired a transaction. For example, if I instead drop two tables, and do so in a transaction:

postgres=# BEGIN;
BEGIN
postgres=# DROP TABLE test1;
DROP TABLE
postgres=# DROP TABLE test2;
DROP TABLE
postgres=# COMMIT;
COMMIT

I get this interesting output:

2017-02-12 12:17:43 CET [mha@postgres] <0> LOG:  statement: DROP TABLE test1;
2017-02-12 12:17:45 CET [mha@postgres] <156960> LOG:  statement: DROP TABLE test2;

Which shows two different transaction ids (one real and one not) for statements in the same transaction. That's obviously not true - they were both dropped by transaction 156960. The transaction id just wasn't available at the time of logging.

So what can we do about that? Event triggers to the rescue!

In this example we will use Jasypt in mule to encrypt clear text passwords in property files. But you could use Jasypt to encrypt all sorts of things e.g. ...

2016 turned out to be a turbulent but positive year for IPv6 here in Norway. As the graph below shows, in the beginning of 2016 about 7.5% of Norwegian end users were IPv6 capable. One year later, this number had increased to almost 10%.

Earlier this week I visited the United Kingdom to attend the excellent UKNOF36 meeting.

As I usually do when when going abroad, I spent some time testing to what extent IPv6 works while roaming in the various PLMNs I have access to.

The previous posts in this series are:

• IPv6 roaming in Sweden
• IPv6 roaming in Belgium and Romania

Those posts contain some more technical background about the testing methodology, so I suggest you skim through them in order to better interpret the test results in this post.

Test results

O2 - MCCMNC 23410

I was not able to get 4G coverage with any of my SIM cards in this network, which probably means that neither Tele 2 nor Telenor have a 4G roaming agreement with O2.

While in 2G and 3G coverage Tele 2 and Telenor’s HLR/HSS blacklisting trick comes into play. (See the IPv6 roaming in Belgium and Romania post for an explanation of what that trick is.)

Vodafone - MCCMNC 23415

In 2G and 3G coverage this looks like the standard HLR/HSS blacklisting trick. However the 4G behaviour is very unusual (as the blacklisting trick is specific to 2G and 3G).

IPv6-only PDP contexts work fine with my Telenor SIM card, but not with my Tele 2 one. My phone logs this latter failure as being due to an unknown/invalid cause code so I have no idea about what’s going on here.

Dual stack IPV4V6 PDP contexts do not work in 4G coverage with any of my SIM cards, and any attempt to use them results in IPv4-only connectivity. As this is not caused by Telenor and Tele 2’s blacklisting trick, the logical conclusion is that Vodafone is deliberately blocking dual stack PDP contexts from being used in their end.

I also saw very similar IPv6-hostile behaviour in Vodafone Romania’s network. I wonder if that is a coincidence or not.

3 - MCCMNC 23420

It appears Telenor doesn’t have a roaming agreement with this operator (my phone reported no access to network). With my Tele 2 SIM card I could not get neither 2G or 4G coverage, only 3G. In 3G coverage Tele 2’s HLR/HSS blacklisting trick comes into play.

EE - MCCMNC 23430

This looks pretty much as expected for an operator where the HLR/HSS blacklisting trick is being used to block IPv6 in 2G and 3G coverage. However, it’s the first time I’ve seen this result in 3GPP cause code 27 (missing or unknown APN). Usually I see code 33 (requested service option not subscribed). Not sure if this difference is significant somehow, but the outcome is the same in any case.

When in 4G coverage, both IPv6-only and dual stack PDP contexts worked just fine.

That said, I did have trouble getting dual stack to work in EE 4G coverage when I used another one of phones. Unfortunately I did not have time to investigate that further during my brief visit. Next time, perhaps.

Just the other day I sat at a customer, and they wanted a web application to present data, from there Mule integration application. The data should be presented to ...

For a while now, I've been annoyed with how difficult it is to set up good backups in PostgreSQL. The difficulty of doing this "right" has pushed people to use things like pg_dump for backups, which is not really a great option once your database reaches any non-toy size. And when visiting customers over the years I've seen a large number of home-written scripts to do PITR backups, most of them broken, and most of that breakage because the APIs provided were too difficult to use.

Over some time, I've worked on a number of ways to improve this situation, alone or with others. The bigger steps are:

• 9.1 introduced pg_basebackup, making it easier to take base backups using the replication protocol
• 9.2 introduced transaction log streaming to pg_basebackup
• 9.6 introduced a new version of the pg_start_backup/pg_stop_backup APIs that are needed to do more advanced base backups, in particular using third party backup tools.

For 10.0, there are a couple of new things that have been done in the past couple of weeks:

While debugging a problem with OCSP, I had to sit down and understand what it really does and why. So What is OCSP, and why do we use it?

Read the rest of this entry

While debugging a problem with OCSP, I had to sit down and understand what it really does and why. So What is OCSP, and why do we use it?

I briefly visited Belgium and Romania last month. Using SIM cards from Tele 2 Sweden and Telenor Norway, both of which support the dual-stack IPV4V6 and IPv6-only IPV6 PDP context types, I spent some time testing whether or not I was able to get working IPv6 Internet connectivity while roaming in the various available PLMNs.

In many cases, IPv6-only connection attempts failed completely. Furthermore, dual stack connection attempts more often than not resulted in an IPv4-only Internet connection. Full test results below.

These frequent failures are however not as dramatic as they might sound. Both Tele 2 and Telenor are using a blacklisting trick that blocks their subscribers from using IPv6 when roaming in certain operators (whose IPv6 capabilities hasn’t yet been verified). See RFC 7445 section 3 and section 6 for technical details on how this trick works. When roaming in an operator blacklisted in this manner, IPv6-only connection attempts made in 2G/3G coverage will fail with 3GPP cause code 33 (requested service option not subscribed), while dual stack connection attempts will result in IPv4-only Internet connectivity.

The good news is that when my devices were set up to request dual-stacked IPV4V6 PDP contexts, they would in every single case get at least the same level of Internet connectivity as they would when requesting an IPv4-only IP PDP context; having the devices request dual-stacked connectivity had no downside whatsoever.

I’ve also performed the same kind of IPv6 roaming testing in Sweden a while back.

Test results

Belgian PLMNs

Proximus - MCCMNC 20601

I was not able to test in 4G/LTE coverage, as it appears that neither Tele 2 nor Telenor have a 4G/LTE roaming agreement with Proximus.

Tele 2 is applying the blacklisting trick described above. Telenor, on the other hand, does not and IPv6 works perfectly.

Orange - MCCMNC 20610

In the Orange PLMN I got identical behaviour with both my SIM cards. It appears both Tele 2 and Telenor are blacklisting, and there is no 4G/LTE roaming agreement.

As with Tele 2/Proximus, dual stack «works» in the sense that I get IPv4-only Internet connectivity.

BASE - MCCMNC 20620

BASE demonstrates how the blacklisting trick only works on 2G/3G and not on 4G. Both Tele 2 and Telenor are blacklisting here, but nevertheless dual stack and IPv6-only works perfectly if the data PDP context is established while in 4G coverage.

Romanian PLMNs

Vodafone - MCCMNC 22601

Vodafone is an interesting case. The fact that IPv6 and dual stack fails on 2G and 3G with 3GPP cause code 32 (service option not supported) instead of 33, and the fact that it also fails in the same way on 4G/LTE, indicate that this is not caused by the blacklisting trick. Instead, it would appear that Vodafone is deliberately blocking visitors from using of IPv6 on their network.

For operators such as Tele 2 and Telenor, this is not such a big deal, as dual-stack still «works» by falling back on IPv4-only connectivity. Operators using 464XLAT, on the other hand, will likely find Vodafone’s behaviour hugely problematic.

Telekom - MCCMNCs 22603 (2G) and 22606 (3G)

Identical results as with Orange in Belgium. I was unable to get 4G/LTE coverage, and both Tele 2 and Telenor applies blacklisting on 2G/3G.

Digi.Mobil - MCCMNC 22605

Neither Tele 2 nor Telenor seems to have any form of roaming agreement with this operator, at least I was completely unable to register in their network, and thus unable to perform any IPv6 testing.

Orange - MCCMNC 22610

Tele 2 does not appear to have a 4G/LTE roaming agreement with Orange, and the blacklisting trick is being used on 2G/3G.

With Telenor, on the other hand, there’s no blacklisting and both IPv6-only and dual stack works perfectly on all technologies.

As we say welcome to a new year, we have a couple of updates to the finances and payment handling in PostgreSQL Europe, that will affect our members and attendees of our events.

First of all, PostgreSQL Europe has unfortunately been forced to VAT register. This means that most of our invoices (details below) will now include VAT.

Second, we have enabled a new payment provider for those of you that can't or prefer not to use credit cards but that still allows for fast payments.

A few weeks back, I noticed the following tweet from Michael Paquier:

And my first thought was "that can't be right" (spoiler: Turns out it wasn't. But almost.)

The second thought was "hmm, I wonder how that has actually changed over time". And of course, with today being a day off and generally "slow pace" (ahem), what better way than to analyze the data that we have. The PostgreSQL mailinglist archives are all stored in a PostgreSQL database of course, so running the analytics is a quick job.

One common complaint about systemd is that it does «too much», where the threshold for the appropriate amount of action is left unspecified. Some of the stuff it can do is hold your hand and offer some comfort functions.

With the end of the advent, this years sysadvent calendar event is now over.

If you missed it, the articles are still ...

We hope you have enjoyed the articles in our second SysAdvent season!

This is the last post in this years sysadvent. If you want to read more, we have other blog entries at our main site, our techblog, our employees have personal blogs that are aggregated at