3 ms·

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 S

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

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