Message-ID: <7f2d2a9e-f135-4147-8781-417ad09b7360@gmail.com>
Date: Wed, 29 Oct 2025 22:11:19 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Linux file permission for COPY TO SQL command
To: pgsql-sql@lists.postgresql.org
References: <f1072845-ca23-46de-bbf2-41acbae3a97e@limes.com.pl>
 <8ABD45BB-37B7-4394-AB59-10BC1C25797F@gmail.com>
 <7d2f7e34-c724-4227-a1c2-8d5882a6a3d4@gmail.com>
 <bed776bc-f63d-46f5-8bb0-840493aab3df@limes.com.pl>
 <2892301.1761767753@sss.pgh.pa.us>
Content-Language: en-US
From: =?UTF-8?Q?Roland_M=C3=BCller?= <rolmur@gmail.com>
In-Reply-To: <2892301.1761767753@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7f2d2a9e-f135-4147-8781-417ad09b7360%40gmail.com>
Precedence: bulk

I think the best solution is to use the psql '\copy' that works the same 
way as server-side COPY or COPY TO.

in addition, the output is always stored in the client machine 
regardless whetehr the server is in localhost, remote or inside a container.

postgres=# \copy (SELECT * FROM pg_class) to /home/MYACCOUNT/Desktop/OUT
COPY 420
postgres=# \! ls -ltr /home/MYACCOUNT/Desktop/OUT
-rw-rw-r-- 1 MYACCOUNT MYACCOUNT 49291 Oct 29 22:06 
/home/MYACCOUNT/Desktop/OUT

On 10/29/25 21:55, Tom Lane wrote:
> Krzysztof <kj@limes.com.pl> writes:
>> Actually server and client are installed on the same machine.
>> The file (real file) permissions are as follows:
>> -rwxrwxrwx+ 1 kaj kaj 2 Oct 27 22:36 fd/fdo.txt
>> The directory permissions are as follows:
>> drwxrwxrwx+  4 kaj kaj     4096 Oct 27 22:36 fd
>> The SQL command which tries to write to the file is this:
>> copy (select key from xcg where add=false) to '/home/kaj/fd/fdo.txt';
> The /home and /home/kaj directories would also need to be
> world-searchable for the server to be able to write there.
> (Do not make them world-writable...)
>
> Did you notice the HINT that goes with that error message?
>
> HINT:  COPY TO instructs the PostgreSQL server process to write a file. You may want a client-side facility such as psql's \copy.
>
> 			regards, tom lane
>
>