Tuesday, December 15, 2020


There's a new article out in Computer Weekly talking about CVE-2019-9193. The PostgreSQL project has issued a statement saying that this is not a security vulnerability, and PostgreSQL core team member Magnus Hagander also wrote a blog about it, saying the same thing. If you're curious about this issue, I suggest reading not only what Magnus wrote but also the comments section of that blog post, where you can see some of the perspectives that other people have on what Magnus said. But, in this blog post, I'd like to comment a bit on what is said in the Computer Weekly article: is there any truth to the allegations offered there?

I wouldn't call the article a complete fabrication. Certainly, it says things that, as far as I know, are probably true. I actually have no first-hand experience with the PGMiner botnet which the article, and other press coverage, describe, but it certainly seems possible that it could exist, and assuming it does, the claim that it makes use of COPY TO/FROM PROGRAM also seems entirely believable. However, when the article states that the bot exploits CVE-2019-9193 to "compromise database servers and co-opt them into the mining network," at least the first part of that statement is clearly false.

Just for a moment, let's assume that everything else the article says is true, and let's assume that CVE-2019-9193 is a security vulnerability and that everything in the CVE description is perfectly accurate. In that world, the vulnerability reported as CVE-2019-9193 has to do with COPY TO/FROM PROGRAM. However, COPY TO/FROM PROGRAM is a SQL command. You can only execute SQL commands if you have managed to log into the database server. Neither the CVE nor the article allege that CVE-2019-9193 can be exploited without a pre-existing database server connection. So, if you are making use of this purported vulnerability, the server is already compromised to the degree that you can connect to the server and get past the authentication stage. Right from there, we see that the issue can't be whether the server can be compromised in the first place, but whether it can be further compromised once some level of access has been gained. The article itself admits this, quoting the researchers who say that it is not an issue "if the access control and authentication system works well."

OK, so you have to be able to log into the database. But, is that enough? The article seems to say so, because it says, referring to COPY/TO FROM PROGRAM, that that "this functionality is enabled by default." However, I just tried creating an unprivileged user called 'bob' on my local machine, logged in as bob, and gave this a try:

rhaas=> copy x from program '/bin/ls';
ERROR:  must be superuser or a member of the pg_execute_server_program role to COPY to or from an external program
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

To me, that does not look very much like this is enabled by default. If the article, or the CVE, mean to imply that by default the pg_execute_server_program role is granted to all users, then they are peddling falsehoods. The statement is true if it is intended to refer to superusers, or to users who have been explicitly granted the pg_execute_server_program privilege by a superuser. Or, perhaps, the intent was to make a general statement about the behavior of the server, saying that the server permits this kind of thing by default, without really making a statement about which category of users have access to it. Interpreted that way, this statement is not only true, but something of an understatement, since it is not only possible by default for the superuser to use COPY TO/FROM PROGRAM, but there's also no built-in way to turn it off. I think the lack of clarity about what this "by default" statement is intended to mean is extremely unfortunate. It's hard to have a meaningful conversation about something like this unless the terms are clear.

So, what we've established at this point is that any potential security hole here only exists if you can already authenticate to the database either to a super-user account or to an account which has been given the special pg_execute_server_program role, which is not initially granted to anyone. But, what is the purported security hole that exists in that case?  The CVE description says this: "In PostgreSQL 9.3 through 11.2, the "COPY TO/FROM PROGRAM" function allows superusers and users in the 'pg_execute_server_program' group to execute arbitrary code in the context of the database's operating system user. This functionality is enabled by default and can be abused to run arbitrary operating system commands on Windows, Linux, and macOS." Let's consider those two sentences individually. The first sentence is basically accurate, because it is just a description of what the feature does. My only quibbles with it are that pg_execute_server_program doesn't exist before v11, and that versions 11.3, 12.0, etc. behave just like v11.2. The second sentence is also basically accurate, modulo the problematic "by default" language which I discussed above.

However, what's a little perplexing is that these two sentences taken together do little more than describe the documented purpose of the feature. Yes, COPY TO/FROM PROGRAM allows you to run arbitrary programs on the server! That is why it exists! And, yes, if you had the ability to run arbitrary programs, you might choose to run programs that did bad things! So, to me, the only sensible interpretation of CVE-2019-9193 is that the people who filed it believe that this feature should not exist at all. As far as I can see, they do not allege here or elsewhere any defect in the implementation of COPY TO/FROM PROGRAM. Rather, they seem to believe that the existence of the feature is itself a security vulnerability. I don't feel that either the CVE or the press coverage brings this point out very clearly, but there doesn't seem to be any other reasonable interpretation.

So, let's examine the claim that, for security reasons, COPY TO/FROM PROGRAM should not exist. Remember that we're talking primarily about superusers here. Database superusers bypass all permissions checks by design, so anything that is ever permitted to any user should also be permitted to a superuser. The question is then whether even the superuser should be permitted to execute arbitrary programs on the machine where the database software is running. You might think that the existence of pg_execute_server_program matters here, but it doesn't, really. If the superuser should be permitted to do something, then they should also be permitted to delegate the privilege to do that thing to other users, and the name of the privilege makes it pretty clear what is being delegated. Nobody has pg_execute_server_program unless a superuser grants it out. Thus the issue is precisely whether it's OK for the superuser to be able to run programs on the server where the database software is running.

At some fundamental level, that's a question of opinion rather than fact. If I happen to control both the operating system account and the superuser account of the database running under that account, then there is no reason to prevent me from doing whatever I want with the database, including running programs on the server. After all, I can do that anyway, and it may be convenient for me to do it via the database. In fact, that potential convenience is exactly why features like COPY TO/FROM PROGRAM exist. However, what if I want to set things up so that Alice controls the operating system account, and Bob is the database superuser? There is no hope of preventing Alice from usurping Bob's identity, because as the operating system user she can run arbitrary code and read and write every file owned by that account, and that's more than enough power to get control of the database. Perhaps, though, it would be possible to prevent Bob from taking over Alice's identity, if features like COPY TO/FROM PROGRAM were not present. If they are present, then Bob is just as capable of taking over Alice's identity as Alice is of taking over Bob's identity.

This is where I have a little bit of sympathy with the argument that the researchers who filed CVE-2019-9193 seem to be making. In a hosted or managed services environment, for example, it is easy to imagine the service provider wanting to give customers superuser access to the database so that the customer can do whatever they want, but not wanting to give shell access to the hosting machines so as to maintain control of the environment. The existence of a feature like COPY TO/FROM PROGRAM means you can't really do that, or at least not without disabling that feature somehow and/or putting some trust in your users not to break your stuff. The problem with using this as a defense of CVE-2019-9193 is that this is only one of many methods that can be used by a database superuser to obtain access to the operating system account. Blocking this particular one would, by itself, only force a bad actor to make use of one of the other methods.

For instance:

[rhaas example]$ ls
[rhaas example]$ psql
psql (14devel)
Type "help" for help.

rhaas=# create extension plperlu;
rhaas=# create or replace function evil() returns void as $$system("touch /Users/rhaas/example/compromise")$$ language plperlu;

rhaas=# select evil();

(1 row)
rhaas=# \q
[rhaas example]$ ls

Whoa! I just broke into the OS account! Is there anything in the documentation warning me that this was possible? It turns out that there is:

"The writer of a PL/PerlU function must take care that the function cannot be used to do anything unwanted, since it will be able to do anything that could be done by a user logged in as the database administrator."

If we wanted to imprison the database superuser firmly inside the database, giving them no chance of assuming the operating system user's privileges, we would need to do a lot more than shut off COPY TO/FROM PROGRAM. We would need to block access to all untrusted procedural languages, since any of them can be used to do something like what I demonstrated above. We would need to block variants of ALTER SYSTEM SET that change PostgreSQL settings such as archive_command and ssl_passphrase_command whose arguments are, as the names imply, shell commands. We would need to block the use of extensions that might let you manipulate the environment in which PostgreSQL is running in a way that could allow usurpation of privileges, such as adminpack, which contains pg_file_write(). We would probably need to put significant restrictions on COPY TO and likely also COPY FROM that don't exist today. We would probably need to prevent the superuser from issuing manual DDL commands against system catalogs, something that is not generally a good idea but which is occasionally recommended in the release notes in connection with certain bug fixes; as it turns out, for reasons that I won't get into in this post, that capability also has security ramifications. I suspect there are other things we would need to do, too, because it's never been the intention of the PostgreSQL project to prevent the database superuser from assuming the operating system user's privileges. It is assumed that these are in effect the same person. That is why, in the PostgreSQL project statement to which I provided a link above, it says this:

"By design, there exists no security boundary between a database superuser and the operating system user the server runs under."

I think that some of the confusion in this area stems from a perception that there is, or should be, a security boundary in that place. I find that confusion pretty understandable. It is probably not obvious to a casual user of PostgreSQL that no security boundary is intended to exist there. It is not obvious when you are logged in as the database superuser and confronted with a SQL prompt that you can leverage that access to run arbitrary programs on the server. It isn't a secret, but it isn't obvious, either. Moreover, you might wish that it were not the case, for reasons like the ones I mentioned above, or others that you may have. But that's how it is, and any change PostgreSQL made to COPY TO/FROM PROGRAM wouldn't make much difference unless a bunch of other things were changed, too.

So where does that leave us in terms of CVE-2019-9193 and the Computer Weekly article to which I provided a link above? There's no doubt that COPY TO/FROM PROGRAM is a feature with security implications, but neither the CVE nor the article alleges that the feature is working in any way other than what the PostgreSQL project intended. The project intended to, and did, put meaningful security restrictions on the use of that feature, in that it can only be used by superusers, or by explicit delegation. It is not available to unprivileged users "by default." Moreover, it does not permit anything that can't also be done in other ways; the method I demonstrated above has worked since 2001, and there are several other methods as well. Whether PostgreSQL has made the right decision about where to put the security boundaries is a debatable point, but CVE-2019-9193 does not advance that discussion, since it focuses on the wisdom of one particular design decision while ignoring the underlying principle that has also informed the design of many other features over the course of at least 2 decades.


  1. I think it's not too hard to avoid PGMiner like attacks in future installations.
    The default pg_hba.conf file must contains a reject line for postgres user for any IP (host all postgres reject) after the local socket line.
    And I think PostgreSQL needs a default non-superuser account (with createdb and createrole permissions) especially for beginners (but I think, it's a good starting point for everyone).
    Nobody should use te postgres user except creating superusers, and nobody should use superusers outside of the hosting machine (or at least the local network) by default.

  2. The postgres default installation (postgres superuser + postgres default database) is nice and easy for beginners but also very insecure. I would also opt to rethink this.