結果のファイルをサーバー上またはクライアント上に格納しますか?

サーバ側

簡単に再利用したり自動化したりしたい場合は、PostgreSQLの組み込みのコピーコマンド。例:

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

このアプローチは完全にリモート サーバー上で実行されるため、ローカル PC に書き込むことはできません。また、Postgres ではそのマシンのローカル ファイル システムで不正な処理が行われるのを阻止できないため、Postgres の「スーパーユーザー」(通常は「root」と呼ばれます) として実行する必要があります。

これは実際にはスーパーユーザーとして接続する必要があるという意味ではありません（それを自動化すると、別の種類のセキュリティリスクが発生します）。オプションSECURITY DEFINER​CREATE FUNCTIONスーパーユーザーであるかのように実行される関数を作成します。

重要なのは、関数がセキュリティをバイパスするだけでなく、追加のチェックを実行するために存在していることです。そのため、必要なデータを正確にエクスポートする関数を作成することも、厳格なホワイトリストを満たす限りさまざまなオプションを受け入れることができる関数を作成することもできます。次の 2 つの点を確認する必要があります。

1. ディスク上のどのファイルの読み取り/書き込みをユーザーに許可する必要がありますか? たとえば、これは特定のディレクトリである可能性があり、ファイル名には適切なプレフィックスまたは拡張子が必要になる場合があります。
2. ユーザーはデータベースのどのテーブルGRANTを読み取り/書き込み可能にする必要がありますか? これは通常、データベースの s によって定義されますが、関数は現在スーパーユーザーとして実行されているため、通常は「範囲外」であるテーブルに完全にアクセスできるようになります。おそらく、誰かが関数を呼び出して「users」テーブルの末尾に行を追加できるようにしたくないでしょう...

私は書いたこのアプローチを詳しく説明したブログ記事厳密な条件を満たすファイルやテーブルをエクスポート (またはインポート) する関数の例もいくつか含まれています。

クライアント側

もう 1 つの方法は、クライアント側、つまりアプリケーションまたはスクリプトでファイル処理を実行することです。Postgres サーバーは、どのファイルをコピーするかを知る必要はなく、データを吐き出すだけで、クライアントはそれをどこかに保存します。

この基礎となる構文はコマンドでありCOPY TO STDOUT、pgAdmin などのグラフィカル ツールはそれをわかりやすいダイアログにラップします。

コマンドpsqlライン クライアントには、と呼ばれる特別な「メタコマンド」があります\copy。これは、「実際の」 と同じオプションをすべて受け取りますCOPYが、クライアント内で実行されます。

\copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER

;メタコマンドは SQL コマンドとは異なり、改行で終了するため、終了する がないことに注意してください。

からドキュメント:

COPY を psql の \copy 命令と混同しないでください。\copy は COPY FROM STDIN または COPY TO STDOUT を呼び出し、psql クライアントがアクセスできるファイルにデータを取得/保存します。したがって、\copy を使用する場合、ファイルのアクセス可能性とアクセス権はサーバーではなくクライアントに依存します。

アプリケーションプログラミング言語はデータのプッシュやフェッチをサポートしているかもしれませんが、入出力ストリームを接続する方法がないため、標準SQL文内でCOPY FROM STDIN/を使用することはできません。PHPのPostgreSQLハンドラ（PDOではありません）には、非常に基本的なTO STDOUTpg_copy_fromそしてpg_copy_toPHP 配列との間でコピーする関数。大規模なデータ セットでは効率的ではない可能性があります。