4 ms·

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 comm

by kijin 8y ago

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.

TheRealPomax 8y ago
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.
stingraycharles 8y ago
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.
jeffdavis 8y ago
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).
aasasd 8y ago
> 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.
- saltcured 8y ago
  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.
  - aasasd 8y ago
    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.
  - pgaddict 8y ago
    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).
    - saltcured 8y ago
      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.

by kijin 8y ago

TheRealPomax 8y ago

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.

stingraycharles 8y ago

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.

jeffdavis 8y ago

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

aasasd 8y ago

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

saltcured 8y ago
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.
- aasasd 8y ago
  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.
- pgaddict 8y ago
  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).
  - saltcured 8y ago
    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.