4 ms·

> Security: Did you know that with one simple trick you can drop to the OS from PostgreSQL and managed service providers hate that? The trick is COPY table_name

> Security: Did you know that with one simple trick you can drop to the OS from PostgreSQL and managed service providers hate that? The trick is COPY table_name from COMMAND I certainly did not know that.

amluto 2y ago
If anyone actually needs the extra performance from avoiding streaming over the Postgres protocol, this could have been done with some dignity using pipes and splice or using SCM_RIGHTS. The latter technology has been around for a long time.
- kragen 2y ago
  only on localhost
  - theamk 2y ago
    That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc.. If you need to execute commands on database server for admin work, use something designed for this (such as ssh) - this will keep right management and logging simple, only one source of shell command execution. If you need to execute commands periodically, use some sort of task scheduler, running as a dedicated user. To avoid 2nd connection, you may use use postgres-controllable job queues. Either way, limit to allowed commands only, so that even if postgres credentials are leaked no arbitrary commands can be executed. Inboth approaches, this would have allow high speed, localhost-specific transports instead of local shell.. if postgresql would have supported them.
    - kragen 2y ago
      actually i think what i said was wrong, because i guess the shell command generating the data to import runs on the database server, not the client, so there's no reason the database server couldn't be using pipes or file descriptor passing for this already. in fact i'm not clear why amluto thinks it isn't
      - theamk 2y ago
        huh? Postgres already has "COPY FROM STDIN" command, which makes database server use postgres connection for the raw data. However, since it uses the existing connection, it needs to be compatible with Postgres protocol, which means that there is an extra overhead in wrapping streaming data. On the other hand, "COPY FROM COMMAND" has no wrapping overhead, as it opens direct pipe to/from command, so no Postgres protocol parsing is involved - as only short command string is sent via postgres connection, while bulk data goes over dedicated channel. This makes it faster, although I am not sure how much does this actually save. amluto's point was that one can achieve no-wrapping performance of "COPY FROM COMMAND" if one could pass dedicated data descriptor to "COPY FROM STDIN". This could be done using SCM_RIGHTS (a AF_UNIX protocol feature that passes descriptors between processes) or pipes+slice (not 100% sure how those would help). But with SCM_RIGHTS, you'd have your psql client create pipe, exec process, and then pass the descriptor to the sql server. This would have exactly the same speed as "COPY FROM COMMAND" (no overhead, dedicated pipe) but would not mix security contexts and would execute any code under server's username - overall better solution. Your point was "only on localhost", which I interpreted as "this approach would only work if psql runs on localhost (because SCM_RIGHTS only works on localhost); while "COPY FROM COMMAND" could even be executed remotely". This is 100% correct, but as I said, I think an ability to execute commands remotely as a server user is a bad idea and should have never existed.
        kragen 2y ago
        i appreciate you having reconstructed the incorrect thing i said as a perfectly sensible and correct one. i agree that everything you said here about postgres is correct except possibly for 'an ability to execute commands remotely as a server user is a bad idea'. i think you are right about what amluto's point was as well; i had understood them to mean something different
        amluto 2y ago
        It’s not just COPY … TO/FROM PROGRAM. IMO all variants of COPY are a mistake — the server should not be opening files on behalf of the client. What is actually wanted is the rough equivalent of: psql … < file Where < could be > or | or piped the other way, plus all the FORMAT goodies, plus perhaps some optimizations to make it fast, but keeping the fact that the client opens the file or executes the program. If you are trying to write a table to a CSV file remotely and you want it to land in a file on the server, then (a) that’s weird and I struggle to come up with a case in which you actually want that and (b) use SSH or some other bespoke protocol to do what you actually want, rather than having the database server kludge it up for you. On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy. COPY … STDIN/STDOUT is fine, but it takes some potentially awkward code that is, as far as I can tell, not particularly portable between languages or between database protocols.
rs_rs_rs_rs_rs 2y ago
You need to be a super user or a member of the pg_execute_server_program group for this to work.

amluto 2y ago

If anyone actually needs the extra performance from avoiding streaming over the Postgres protocol, this could have been done with some dignity using pipes and splice or using SCM_RIGHTS. The latter technology has been around for a long time.

kragen 2y ago
only on localhost
- theamk 2y ago
  That's kinda the point - there is an argument that one should not be baking arbitrary shell command execution into database server at all. Such execution will lack critical security features - setting right user, process tracking, cleanup, etc.. If you need to execute commands on database server for admin work, use something designed for this (such as ssh) - this will keep right management and logging simple, only one source of shell command execution. If you need to execute commands periodically, use some sort of task scheduler, running as a dedicated user. To avoid 2nd connection, you may use use postgres-controllable job queues. Either way, limit to allowed commands only, so that even if postgres credentials are leaked no arbitrary commands can be executed. Inboth approaches, this would have allow high speed, localhost-specific transports instead of local shell.. if postgresql would have supported them.
  - kragen 2y ago
    actually i think what i said was wrong, because i guess the shell command generating the data to import runs on the database server, not the client, so there's no reason the database server couldn't be using pipes or file descriptor passing for this already. in fact i'm not clear why amluto thinks it isn't
    - theamk 2y ago
      huh? Postgres already has "COPY FROM STDIN" command, which makes database server use postgres connection for the raw data. However, since it uses the existing connection, it needs to be compatible with Postgres protocol, which means that there is an extra overhead in wrapping streaming data. On the other hand, "COPY FROM COMMAND" has no wrapping overhead, as it opens direct pipe to/from command, so no Postgres protocol parsing is involved - as only short command string is sent via postgres connection, while bulk data goes over dedicated channel. This makes it faster, although I am not sure how much does this actually save. amluto's point was that one can achieve no-wrapping performance of "COPY FROM COMMAND" if one could pass dedicated data descriptor to "COPY FROM STDIN". This could be done using SCM_RIGHTS (a AF_UNIX protocol feature that passes descriptors between processes) or pipes+slice (not 100% sure how those would help). But with SCM_RIGHTS, you'd have your psql client create pipe, exec process, and then pass the descriptor to the sql server. This would have exactly the same speed as "COPY FROM COMMAND" (no overhead, dedicated pipe) but would not mix security contexts and would execute any code under server's username - overall better solution. Your point was "only on localhost", which I interpreted as "this approach would only work if psql runs on localhost (because SCM_RIGHTS only works on localhost); while "COPY FROM COMMAND" could even be executed remotely". This is 100% correct, but as I said, I think an ability to execute commands remotely as a server user is a bad idea and should have never existed.
      - kragen 2y ago
        i appreciate you having reconstructed the incorrect thing i said as a perfectly sensible and correct one. i agree that everything you said here about postgres is correct except possibly for 'an ability to execute commands remotely as a server user is a bad idea'. i think you are right about what amluto's point was as well; i had understood them to mean something different
      - amluto 2y ago
        It’s not just COPY … TO/FROM PROGRAM. IMO all variants of COPY are a mistake — the server should not be opening files on behalf of the client. What is actually wanted is the rough equivalent of: psql … < file Where < could be > or | or piped the other way, plus all the FORMAT goodies, plus perhaps some optimizations to make it fast, but keeping the fact that the client opens the file or executes the program. If you are trying to write a table to a CSV file remotely and you want it to land in a file on the server, then (a) that’s weird and I struggle to come up with a case in which you actually want that and (b) use SSH or some other bespoke protocol to do what you actually want, rather than having the database server kludge it up for you. On the occasions where I’ve wanted anything resembling this functionality, I have always actually wanted psql’s \copy. COPY … STDIN/STDOUT is fine, but it takes some potentially awkward code that is, as far as I can tell, not particularly portable between languages or between database protocols.

rs_rs_rs_rs_rs 2y ago

You need to be a super user or a member of the pg_execute_server_program group for this to work.