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 (CVE-2019-9193). The origin appears to be a blogpost from Trustwave.
So is this actually a vulnerability? (Hint: it's not) Let's see:
To quote the post:
Since version 9.3, new functionality for ‘COPY TO/FROM PROGRAM’ was implemented. This allows
the database superuser, and any user in the ‘pg_read_server_files’ group to run arbitrary
operating system commands. This effectively means there is no separation of privilege
between a database superuser user and the user running the database on the operating system.
And then to quote the PostgreSQL documentation:
COPY with a file name instructs the PostgreSQL server to directly read from or write
to a file. The file must be accessible by the PostgreSQL user (the user ID the server
runs as) and the name must be specified from the viewpoint of the server. When PROGRAM
is specified, the server executes the given command and reads from the standard output
of the program, or writes to the standard input of the program. The command must be
specified from the viewpoint of the server, and be executable by the PostgreSQL user.
COPY naming a file or command is only allowed to database superusers or users who are
granted one of the default roles pg_read_server_files, pg_write_server_files, or
pg_execute_server_program, since it allows reading or writing any file or running
a program that the server has privileges to access.
and
Executing a command with PROGRAM might be restricted by the operating system's access
control mechanisms, such as SELinux.
So what we have here is simply PostgreSQL acting exactly as intended, and as documented. The "vulnerability" is the same as the fact that if you log in as root on your typical Unix system, you can edit and create file and run commands as... root.
While COPY FROM PROGRAM
has made it a bit easier, it is by no means the only way to execute commands as the server user, if superuser permissions have been granted. Given that the superuser can read and write arbitrary files on the server (as the postgres user), it can fairly easily be escalated into running commands even without COPY FROM PROGRAM
.
(The post is also factually incorrect in that members of pg_read_server_files
can run commands. That is incorrect: it requires either the pg_execute_server_program
role or superuser)
While this is not a vulnerability in PostgreSQL, it is certainly a vulnerability present in a non-trivial number of installations of PostgreSQL deployments. The solution to that vulnerability is to not grant superuser permissions to user, and a common setup is to only allow the postgres OS user itself to act as superuser, in which case there is no escalation at all. Unfortunately, the "vulnerability" blog post doesn't mention anything at all about this.
It seems PostgreSQL needs some better documentation about this for new users, possibly along the CREATE ROLE
page which does not explicitly mention this. But if superuser is granted to a user, that remains the equivalent of granting the OS user permissions.
We can certainly discuss if it's user friendly to allow such functionality, but the system is operating exactly as intended, and it's not a vulnerability.
If you have any application that uses superuser, you should fix that right away. And if you care about your security at all, you should definitely make sure to use pg_hba.conf
to ensure that no superuser can log in to the system remotely.
As a footnote, the PostgreSQL Security Team was contacted by Trustwave, warning us about the upcoming blogpost. We explained in our response that this is not considered a vulnerability, since it's the product working as intended. We do not know why they proceeded to register a CVE entry regardless of that.
I actually think pg_rewind should work fine without superuser, as long as you grant the specific functions. I haven't tested that though.
However, clearly master and standby servers are part of the same security domain, if they are part of a HA group (a reporting standby might be something else of course). So as long as they are properly restricted in pg_hba.conf (as they should always be!) I don't think it's really that much of a problem.
This vulnerability they are concerned about is laughable.. I see little to no difference compared to untrusted language such a plpython...
One can be far more destructive with python than trying to construct shell commands that postgres will have pass to the shell to execute.
To use this loop hole 1: have to access to an account with permissions to utilize the command. 2: postgresql OS account has to have high level of authority to do anything destructive. This train of thought should also apply to MSSQL as they added sp_execute_external_script() a few years ago... Which only has one protection to my understanding EXECUTE ANY EXTERNAL SCRIPT. In most installs MSSQL operates in a very high level of authority
This is the general problem with any application that allows execution of arbitrary code.
There is one improvement that could be made that is when a command is executed the Postgres could run the command as a different user
You can't let postgres run external commands as a different user - it requires root privileges. And the DB normally doesn't have them or need them. We have too many pseudo-cyber-security experts around fighting for 2min of fame.
I'm not a Linux/Unix expert but on windows you can start a process as different user with the CreateProcessAsUserA function
kinda surprised to hear that Linux/Unix does not have a similar function
The reason i think running shell commands like this as different users is it would limit possible destruction that could be done intentionally or accidentally.
CreateProcessAsUserA() requires several extra privileges on Windows that a normal user wouldn't have. in the Unix world, that means it has to be root to begin with.
There are ways around it. There are even things like pl/container (https://github.com/greenplum-db/plcontainer) which can be used to execute functions in external docker containers...
I hope someone is able to appeal this obviously mistaken assignment of a CVE, and I wonder which CVE authority granted the number without sufficiently reviewing the content.
Copying part of the comment I left over on the original greenwolf blog: "Oracle has this same feature as SQL Server and PostgreSQL; the DBMS_SCHEDULER plsql package and the in-database JAVA both provide functionality to run programs on the server. The OS_COMMAND package (from the plsqlexecoscomm website) even provides a very convenient wrapper around it. ... I’m curious even on Oracle whether it’s truly possible to prevent a SYSDBA connection from using DBMS_SCHEDULER; I don’t think it’s possible there. I’m not as familiar with SQL Server; even if xp_cmdshell is disabled, can an administrative user with a network connection execute sp_configure to enable it?"
Looks just like they needed yet another "critical vulnerability disclosure[s]"... Actually reading manuals seems to have become extraordinarily unfashionable :-)
The WMF vulnerability was documented and working as designed. It was still patched, fixed, and removed.
A security hole is a security hole, even if you put a lampshade on it in the documentation and say, “this is an intentional security hole.”
It absolutely is. But this is not a security hole.
You don't see "root" removed from Linux distributions either, do you? And yet it's documented that you can do bad things with it.
+1 on your last comment.
I would add something important here as well. The entire PostgreSQL security model assumes that superusers can access the operating system underneath. This is true not only with COPY (including just from files), and it is true with untrusted languages.
If I am superuser I can add pl/perlu as a language, for example, and proceed to access the entire Perl installation of the server, or pl/python which is always untrusted and do the same with python. The limit of course is that this is only as the Postgres user, which must not be root. So there is defence in depth. But it is impossible to see how things could be sandboxed while ensuring the extensibility of the database. PL/PerlU cannot be guaranteed safe, but it can be extremely useful but it has the same "problem" and if we "solve" that "problem" the cost is turning PostgreSQL into something other than an extensible development platform.
New comments can no longer be posted on this entry.
"And if you care about your security at all, you should definitely make sure to use pg_hba.conf to ensure that no superuser can log in to the system remotely."
Unfortunately pg_rewind requires (AIUI) remote SUPERUSER access in order to work and is being used (optionally) by HA solutions like Patroni. Getting it to lower its privileges and be used by a non-SUPERUSER would be good thing in my opinion.