CVE-2019-9193: Not a Security Vulnerability
postgresql.org
postgresql.org
Then I looked at the PostgreSQL statement, which said that the report claimed that users with a read-access role could do the su things, and they said that the claim was not true.
And then I looked at the actual report, which stated that you have to have the read-access role, and the execute-access role (or su).
So, what it seems like is that both parties didn't represent the actual situation well, but the root (ha) issue was that it was reported as "IF YOU HAVE READ ACCESS (and execute access) THEN YOU CAN EXECUTE ARBITRARY CODE!!11!!!"
"The pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are intended to allow administrators to have trusted, but non-superuser, roles which are able to access files and run programs on the database server as the user the database runs as. As these roles are able to access any file on the server file system, they bypass all database-level permission checks when accessing files directly and they could be used to gain superuser-level access, therefore care should be taken when granting these roles to users."
It's not uncommon to grant users the right to change their roles into specific ones with more privileges. For business critical installations the DBA's role will e.g. often not have superuser rights itself, but the permission to do 'SET ROLE some_superuser_role; some_dangerous_command; RESET ROLE;'. And then it can be useful to allow a user to go to different rules. E.g. one that look at files for low-level debugging, but not drop tables, to prevent accidents.
Edit: #1 fight formatting, #2 explain SET ROLE practice.
Raymond Chen on "security vulnerabilities" that depend on already having superuser status.
https://devblogs.microsoft.com/oldnewthing/20121207-00/?p=58...
Looks like Microsoft messed up the line breaks on switching to a new blog platform.
One could argue Microsoft has been messing up line breaks since 1981.
Which is also currently a banner at postgresql.org titled “4th April 2019: CVE-2019-9193: Not a Security Vulnerability”
It was a bad report. To be fair, it's also a bad feature. But bad features working as intended shouldn't be "vulnerabilities".
COPY TO/FROM PROGRAM has been valuable for our data warehousing operations.
The community consensus was the MS should make this thing disabled by default, and they did. Postgres should too.
https://docs.microsoft.com/en-us/sql/database-engine/configu...
- on a dev box (or your own workstation)
- in production
In the first case, the same person/people using the DBMS can just `sudo su postgres -` to do whatever Postgres would be doing for them.
In the second case, nobody who's a pure user of the database (i.e. anyone who's not the DBA of the cluster) should have the privileges required to `COPY FROM ... PROGRAM` anyway. (They certainly won't on any managed environment like RDS.)
As Raymond Chen says: if you can `COPY FROM ... PROGRAM`, you're already on the other side of the airtight hatchway.
---
Admittedly, though, it'd be nice if `COPY FROM ... PROGRAM` was made into something that didn't equate to superuser privileges. I think that's totally possible.
The simplest first step would be to ship with Postgres a setuid "shim launcher" binary, owned by the OS user `nobody` in the `postgres` group, with permissions 070 (i.e. only Postgres can run it.) Postgres could then just run all its `COPY FROM ... PROGRAM` command lines by fork+exec'ing this binary. A simple sandbox, but usually pretty effective.
(Being `nobody` isn't a perfect sandbox; you would need to trust someone with the privilege to execute `COPY FROM ... PROGRAM` statements about as much as you'd trust them with their own entirely-unprivileged shell account on the host instance. But—as the type of user who you'd give this privilege [e.g. users who can do ETL things to your DB] would already be able to thrash most of the same resources through Postgres that they could thrash with an unprivileged shell account, I don't see there being any extra risk in granting this privilege to such users, IMHO. They can write GBs of data to /tmp as `nobody`? Well, they can create GBs-wide temporary tables in Postgres. Etc.)
A cleaner, longer-term solution, in my mind, would be to actually get Postgres to take advantage of the way POSIX specifies "one user attempting to run a command as another user" privileges: sudoers(5).
Just a few changes would be necessary:
1. Postgres user roles would need to maintain, as a property, a mapping to an OS user of the host instance (either manually, or as a session property discovered from whatever AAA system the user authenticated through when connecting, e.g. LDAP or PAM.)
2. Postgres would execute `COPY FROM ... PROGRAM` statements by attempting to run the command line under the appropriate mapped OS user for the connected session, using sudo(1).
3. Postgres would expect the sysadmin to place entries in /etc/sudoers.d/, enabling its OS user to sudo(1) as specific other OS users for specific commands. It would gracefully handle failure-to-sudo(1) as a failure of the statement.
4. Installing Postgres would add at least one sudoers(5) entry, allowing the OS `postgres` user to execute any command as the OS `nobody` user. Postgres would target the `nobody` user for all `COPY FROM ... PROGRAM` statements by default†, unless you explicitly executed a `COPY FROM ... PROGRAM (... AS HOST USER)` statement.
It really should be up to OS security policy, not application software, to decide whether one OS user (e.g. `postgres`) is allowed to execute a given command line as another given OS user. sudoers(5) is the POSIX way‡ to declare such OS-level policy. Postgres just needs to take advantage of the OS—to communicate to the OS which OS user its commands should be interpreted as being on behalf of.
† Well, the default OS user would have to be configurable, to allow backward compatibility with existing ETL pipelines that expect `COPY FROM ... PROGRAM` to execute as the OS `postgres` user. Just have a global config param `postgres_copy_subcommand_default_os_user`, default it to "postgres" if it's unset (as it would be in existing installs), and update the postgresql.conf template for new installs to set it to "nobody".
‡ I'm not sure how you'd do this on non-POSIX OSes like Windows. I know Windows has `runas`, but that's equivalent to su(1), not sudo(1); it doesn't have policy-based non-password-prompting elevation capabilities. Anyone know what you'd do here? How does e.g. Docker on Windows run containers with specific user privileges?
The confusion probably comes from the notion of a 'database superuser' - the point is that any DB user with enough privileges to run COPY FROM ... PROGRAM can also do anything else in the DB, so they are already considered a DB superuser.
Quote from the article :
> By design, there exists no security boundary between a database superuser and the operating system user the server runs under. As such, by design the PostgreSQL server is not allowed to run as an operating system superuser (e.g. "root").
But, as well, I think it's important to point out that, on a system that's dedicated to being a database instance, there's really not that much difference between being able to execute arbitrary commands as the OS superuser, and being able to execute arbitrary commands as the OS user that the DBMS runs as. If you can run commands as the OS `postgres` user, you can, for example:
- delete the cluster (even if your DBMS user can't do that)
- read all the Postgres configuration files, including any secrets loaded from such files
- change other [DBMS!] users' passwords
- configure a new authentication method that sends credentials to an arbitrary third-party system (e.g. an LDAP server you control), turning the database instance into a login-credential harvester
Etc.
Yes, the OS `postgres` user isn't the OS superuser; but that is kind of meaningless if the box is essentially just a substrate for running Postgres on.
Basically, you were discussing a mechanism for allowing non-admin DB users to safely run COPY FROM... PROGRAM?
The security researchers did not seem too concerned with the feedback they got from the community prior to releasing this CVE.
Seriously though, security research is starting to drift into bizarro land, security contacts at companies are inundated with port-scans asking for bug bounties because there's an open port and now people are registering CVEs on expected and documented behavior.
Remember that these people are essentially trying to earn a living by finding vulnerabilities, so it's no surprise that they'll try to spin anything as one, regardless of any other considerations.
I've used the term "security vultures" before in reference to such things. It's unfortunate that a lot of companies misunderstand or obey their requests, and in the process useful features are destroyed and software becomes more user-hostile.
People who have no reputation tend to worry less about their reputation.
[0] https://devblogs.microsoft.com/oldnewthing/author/oldnewthin...
Yeah that's just not done. Dutch: kinderen die vragen worden overgeslagen (it rhymes nicely) - kids that ask will be skipped/passed. I.e., if you ask for a reward (or sweets, in a kids' case), you certainly won't get any.
If you reward people that report silly stuff and then ask for money, that would be bizarro land indeed.
That's possibly an improvement Postgress can do to avoid easy pivoting. Its what a less defensive security reply would include, because if hackers use it to pivot it might not look good down the road.
But from a pure argument standpoint Postgres is correct, it's more of a defense in depth strategy.
If you take that to its logical conclusion you end up with completely unusable, user-hostile software that becomes nearly useless.
The fact that everything can be abused, should not be a reason to ban everything either.
Someone above mentions that the feature/config is actually off by default!
""" Btw, the xp_cmdshell thing the author references several times? It can be enabled via tsql if you have a privileged account.
https://docs.microsoft.com/en-us/sql/database-engine/configu...
and it allows to execute shell code (as a specified user) even when not a sysadmin: https://docs.microsoft.com/en-us/sql/relational-databases/sy... """
so no MS didn't really "lock down" the feature.
Please stop repeating this FUD.
Postgres is just a process that happens to do a lot of I/O. It's no different than any other Unix process: it can spawn children, it can dynamically load libraries on request. It protects these powerful tools with permissions, which is the sane and correct approach taken by many, many other languages and frameworks, all just unix processes, all just trying to get shit done.
COPY...PROGRAM requires being granted permission. If you you have it you can run code as the postgres user. It is not granted by default.
Please explain what's FUD about that?
The postgres equivalent this post is about a feature that's only granted to superusers (or in recent version user that have explicitly been granted rights that are equivalent to superuser and documented as such - namely the right to directly write into any database file...).
I agree that the postgres and msft functionality effectively have the same level of permissions required, which is that an admin level user must take specific steps to allow this to occur.
For comparison, in CockroachDB (disclosure: I'm a co-founder of Cockroach Labs), we don't have any features that let you execute server programs, but we do have something analogous to `pg_{read,write}_server_files` via the BACKUP, RESTORE, and IMPORT commands. In order to use these commands with a target on the server's filesystem, though, the database `admin` role isn't enough. The server also needs to be started with the `--external-io-dir` flag (and file operations will be limited to that directory). This gives an extra layer of opt-in before filesystem operations are allowed.
If you’re talking about a more broad “COPY TO/FROM” is evil, then I think this is a far broader discussion, and I encourage you to get a better understanding of some of the use cases.
It's a command, and it's closer to other commands that do similar things, like loading a plugin (i.e. a ".so" file).
Also those schmucks who like data to be loaded way way faster.
I prefer using COPY ... STDIN/STDOUT. AFAICT, you get the same bulk table access performance. But, the external file access is via the client, separating DB rights and filesystem rights. When appropriate, you can also SSH to the server and invoke psql as a regular user there, manipulating files under that user's control on the server filesystem.
The psql command also provides built-in \COPY command to make it easy to combine several such commands into one transaction in a SQL script. It looks a little like the SQL COPY command, but accesses files on the client side and executes equivalent COPY ... STDIN/STDOUT statements on the server.
In Python, you can use the copy_expert() method with psycopg2 connections to access the COPY ... STDIN/STDOUT feature for similar bulk ETL. However, Python will become the performance bottleneck if you try to do any real row-level processing rather than just relaying byte buffers to/from the filesystem.
COPY TO/FROM PROGRAM is useful e.g. when you already have the data locally on the server (same filesystem, ...).
COPY FROM STDIN/STDOUT are useful when the data are on some other system (say, on a different server, etc).
COPY t FROM PROGRAM 'gunzip -c /path/to/compressed/data.gz';
gunzip -c /path/to/compressed/data.gz | psql db -c 'copy t from stdin';
Ultimately it's a tradeoff - don't trust your users? Don't give them superuser access, don't grant them the role. It's possible to restrict that in other ways (e.g. security-definer functions + extra validations).
We use it all the time via psql authenticated with unix domain socket. In most situations where there are users performing ETL and such, I think it is much safer to have them using a non-superuser role and manipulating files in their own user or group space. You can have all the performance of a bulk COPY without the risk of mangling the service-related files under the postgres user. I think it is better to give regular SSH accounts to these users than to give them elevated DB privileges so they can use server-side file or program COPY.
Also, with psql you can do \copy ... program within your SQL script. It has all the expressive power of a server-side copy but actually runs commands under the invoking user instead of the postgres service.
Anyone with privileges to run docker image is basically root on your host.