Hacker News new | past | comments | ask | show | jobs | submit login
CVE-2019-9193: Not a Security Vulnerability (postgresql.org)
165 points by proboscis 8 days ago | hide | past | web | favorite | 77 comments





I kinda went back and forth on this. The initial comments here made me think that this was basically the reporter saying "person with su can do su things".

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!!!"


allegedly, the reporters only edited their article after publication and widespread criticism.

Oh, if that's the case, then the reporters were just wrong about how the software worked. If it only required the read access, then that would be a legitimate vulnerability.

So, is there a difference between and actual vulnerability in the code, and a vulnerability due to unintended misconfiguration? That's what this sounds like (or am I wrong?), And if so, plenty of software can be misconfigured in an unsecured state.

The point is that, if an admin misconfigures the software, then the vulnerability is on them, and the software itself doesn't need to change (minus arguments of usability but that's a whole other can of worms).

How do you configure execute rights securely? I’ve skimmed over the docs. Execute right allows user to execute programs under postgres Unix account, so getting control of postgres database account should be possible. Basically execute right = superuser. Why even have a separate right for that? Just assign superuser role.

Because enough users asked for them - there was a long debate about that. It's less to prevent those users from intentionally gaining superuser, and more to make it a bit harder to unintentionally have trusted users shoot themselves into the foot. The docs say at https://www.postgresql.org/docs/current/default-roles.html#D...

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


Thanks, that makes sense. I still think that enough people don't read documentation carefully and might unintentionally introduce vulnerability into their database systems, but probably they would find enough ways to shoot themselves in the foot anyway.

No, this feature is like ssh, but without any encryption and authentication.

What? You have to be authenticated as a superuser or other role with a very specific privilege.

"It rather involved being on the other side of this airtight hatchway: Writing to the application directory" (2012)

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.


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



Re line breaks: That's Microsoft being poetic :)

Direct link to “CVE-2019-9193: Not a Security Vulnerability” https://www.postgresql.org/about/news/1935/

Which is also currently a banner at postgresql.org titled “4th April 2019: CVE-2019-9193: Not a Security Vulnerability”


Per the links in the NVC CVE page, the reporter seems to have retracted it too.

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 is not a bad feature. It is in fact an amazing and wonderful thing that makes ETL with Postgres much more powerful. Almost all language runtimes can spawn subprocesses. What is your rational for declaring it "bad" with no explanation?

There is no rational, people who simply don't understand or use a certain feature are more likely to write it off as a bad or useless feature. Pay them no mind.

COPY TO/FROM PROGRAM has been valuable for our data warehousing operations.


Sigh. We literally went through all this with SQL Server's xp_cmdshell feature already. All the same flame wars apply, I don't have the energy to argue it all again.

The community consensus was the MS should make this thing disabled by default, and they did. Postgres should too.


xp_cmdshell can be re-enabled, from SQL, with the same kind of privileged account postgres requires to run COPY...PROGRAM:

https://docs.microsoft.com/en-us/sql/database-engine/configu...


And it is disabled by default from my understanding.

I'm unclear on what having the feature "disabled by default" would really help. There are two primary deployment use-cases of a DBMS:

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


There is a misunderstanding here: the 'COPY FROM ... PROGRAM' command, according to both the vulnerability and the postgres article, only allows running commands as the OS user running the server process, NOT a superuser (also, Postgres won't start if you try to start it as root).

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


No, I'm not misunderstanding. When I said "equates to superuser privileges", I meant the privileges that the database superuser has. We're speaking in the context of DBMS permissions here—in DBMS docs, "user" always means "database user" (and "superuser" always means "database superuser"); and if you want to mean "OS user", you have to explicitly spell that out (as I did each time I meant that.)

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.


Well, I was right, there was a misunderstanding - on my part, obviously.

Basically, you were discussing a mechanism for allowing non-admin DB users to safely run COPY FROM... PROGRAM?


There is ssh which is secure and encrypted. There is no need to invent new unencrypted and insecure remote shell protocol thus increasing attack surface without getting any noticeable benefits.

How about not giving people superuser privileges instead of asking for useful features to be dropped?

It’s not a remote shell protocol, though… it runs local programs.

Yeah, and it's super-useful in some cases. Say, passing data directly to gzip (or reading it from it).


After reading the discussion, that seems like the entirely right call.

The security researchers did not seem too concerned with the feedback they got from the community prior to releasing this CVE.


Yeah, I discovered a flaw in SSH the other day, if my account on the remote server Is listed in the sudoers file I can escalate to root priviliges, as soon as I create a snazzy logo I'm going to get myself a 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.


A couple of years ago someone made a website about the "Grinch" vulnerability, saying it was going to be bigger than shellshock. The "vulnerability" was that members of the "wheel" admin group could use sudo to run commands as root without a password. Needless to say, RedHat replied that it was intended behavior, but not until the researcher got their 15 minutes of fame on a bunch of tech websites.

https://access.redhat.com/articles/1298913


security research is starting to drift into bizarro land

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.


Perhaps I'm naive, but I think it is surprising anyone wants to be taken seriously as a researcher in any field and proceeds to publish information that in on its face and with no effort provably false.

Throw enough at the wall, some of it might stick.

People who have no reputation tend to worry less about their reputation.


I can't reasonably say that doesn't happen. Obviously it does. It may even be a useful tactic in the very short term. It however does not appear to be a very useful decision for a long term career or hobby.

Security vulture, nice. I'll steal that one! Am a security dude myself but I hadn't heard that one before; it sounds quite apt.

starting? Raymond Chen has been blogging about bizarre vulnerability reports for almost 13 years now.

I assume you mean this Raymond Chen of Microsoft[0]

[0] https://devblogs.microsoft.com/oldnewthing/author/oldnewthin...


> asking for bug bounties

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.


The researcher should watch this video https://www.youtube.com/watch?v=866olNIzbrk

Thank you! Didn't know about that silly Python non-CVE, a great example of the same shallow thinking that created this bogus CVE.

Sounds the same as xp_cmdshell scenarios in past. Although worth pointing out Microsoft there did lock down things further to even disable the feature and it has to be turned on explicitly.

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 I understand it correctly, the feature is controlled via a user permission which is not granted by default.

If that is correct, then it's really a non issue I'd say.

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.


It's not about banning but secure by default. A feature that a minority of users needs, shouldn't be accessible by default - it's common security principle.

Someone above mentions that the feature/config is actually off by default!


Thank goodness it's not accessible by default.

Quoting Andreas Freund from the mailing list:

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


If you make a proxy account, AS AN ADMIN. I have personally walked dozens of DBAs through making said proxy account as it was non-trivial to accomplish.

Please stop repeating this FUD.


You cannot run COPY...PROGRAM without a permission that can only be granted to you by a superuser and which is not granted by default. I'm failing to see the distinction.

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.


sudo exists, even though an admin could just log in as root all the time, because it's safer to keep your gun unloaded when you aren't planning to use it immediately. Keeping your gun loaded isn't a security flaw, but it is dangerous.

sudo requires being granted permission. If you have it, you can run code as root. It is not granted by default.

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.


The quote's from me, and even in the quoted part explicitly says "if you have a privileged account". And an admin user you just need to enable it, and it works without even the proxy user configured.

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


The FUD is someone saying Microsoft didnt lock down the xp_cmdshell functionality (which is the post I replied to.)

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.


Well, the comment was in reply to somebody asking for the postgres feature to be locked down similar to MSSQL's - and I think the "FUD" spreading commenter just was trying to explain that it's already similarly locked down. Sure, it could have been a bit more explicit about that, but attacking that as FUD seems mighty strong.

Is this the same as somebody executes sudo bash and observes that he got root prompt?


This is the original blog post by the author who filed the cve. https://www.trustwave.com/en-us/resources/blogs/spiderlabs-b...

While the behavior described might be by design, I'm skeptical of any database query that involves accessing user-specified local files or executing system commands. (MySQL also has the infamous LOAD DATA INFILE query.) They look like band-aids designed for people who can't be bothered to import/export their data using standard shell commands, scripts, pipes, and database-specific dump/restore tools. These kinds of features should be disabled by default.

Sure, but that's a different discussion. No matter how potentially dangerous some software behaviour is, if it's both intentional, and well-documented as being dangerous and intentionally so, there is no CVE.

As mentioned in the article, this feature is disabled by default for non-superusers.

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 not a "query" and can't be executed by normal users.

It's a command, and it's closer to other commands that do similar things, like loading a plugin (i.e. a ".so" file).


> They look like band-aids designed for people who can't be bothered

Also those schmucks who like data to be loaded way way faster.


What is way way faster here? Are you only comparing COPY versus individual row INSERT statements?

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.


You're missing the point.

    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).
Of course, you might write a script that reads the local data and loads it through a regular client, i.e. something like

    COPY t FROM PROGRAM 'gunzip -c /path/to/compressed/data.gz';
might be rewritten like

    gunzip -c /path/to/compressed/data.gz | psql db -c 'copy t from stdin';
but well, that requires some access to the server and ability to execute commands on it (although not as a postgres superuser).

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


I'm not missing the point. I was responding to a comment about performance. In my experience, COPY ... STDIN/STDOUT are just as fast the other COPY, given the same filesystems. I think many people are unnecessarily conflating bulk COPY advantages with superuser rights here.

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.


MySQL's ‘load data infile’ is much faster than anything going through the client, precisely because it avoids layers of processing. (At least it was way faster back when I was using it.) Dunno about this business in Postgre.

If this is CVE, what is Docker then? :)

Anyone with privileges to run docker image is basically root on your host.


This is why you should never add your user to the docker group. Just use `sudo docker` or put your docker commands in a script that can only be edited by root and execute the script via sudo with NOPASSWD.

Much better: replace Docker with https://podman.io/, which runs with user privileges, no root.

TL;DR Don't grant root to strangers, because they might do something rude with your db.

I think this is an argument for not giving root/superuser all possible permissions by default. It's OK that granting the `pg_execute_server_program` permission gives access to this feature, but it should still be something you have to opt in to, instead of making database superuser equivalent to the host user that the database runs as.

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.


It's an argument for sure. But there's a significant maintenance cost to every security boundary you impose, especially when it's as counter-intuitive as "root may not do suchandsuch".

Running a program remotely from a database client is an awful idea. DB client is not a secure remote shell; if you need a shell access, use ssh. Postgres is making a mistake, developers will run postgres as root (because it is the easiest way to solve problem with permissions), they will write apps that connect to postgres server as root and run SQL queries made from user input.

You can't run Postgres as root without patching it first. It really wants to run as a low privilege user.



Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: