Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEBAe-006lke-GI for pgsql-sql@arkaria.postgresql.org; Wed, 29 Oct 2025 18:45:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vEB9e-0030kG-V3 for pgsql-sql@arkaria.postgresql.org; Wed, 29 Oct 2025 18:44:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vEB9e-0030k7-Gc for pgsql-sql@lists.postgresql.org; Wed, 29 Oct 2025 18:44:17 +0000 Received: from deimos.limes.com.pl ([193.110.228.48]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vEB9b-004vpf-0E for pgsql-sql@lists.postgresql.org; Wed, 29 Oct 2025 18:44:17 +0000 Received: from localhost (localhost [127.0.0.1]) by deimos.limes.com.pl (Postfix) with ESMTP id 038322512080 for ; Wed, 29 Oct 2025 19:44:09 +0100 (CET) X-Virus-Scanned: Debian amavisd-new at deimos.limes.com.pl X-Spam-Flag: NO X-Spam-Score: -3.026 X-Spam-Level: X-Spam-Status: No, score=-3.026 tagged_above=-9999 required=6.11 tests=[ALL_TRUSTED=-1, AWL=-0.117, BAYES_00=-1.9, HTML_MESSAGE=0.001, T_SCC_BODY_TEXT_LINE=-0.01] autolearn=ham autolearn_force=no Received: from deimos.limes.com.pl ([127.0.0.1]) by localhost (deimos.limes.com.pl [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id tQAJs9qpeVUe for ; Wed, 29 Oct 2025 19:44:06 +0100 (CET) Received: from [192.168.51.220] (host141-89-206-34.limes.com.pl [89.206.34.141]) by deimos.limes.com.pl (Postfix) with ESMTPA id A3798251207E for ; Wed, 29 Oct 2025 19:44:06 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=limes.com.pl; s=mail; t=1761763446; bh=57XEr5rL4vBZuIs8eguVQr1kv8BqKrdLcKtWxReFdtM=; h=Date:Subject:To:References:From:In-Reply-To; b=gq8FZaFTRBrfuWTugJfkqW/ltPPJzrSBTqso4nGS/hb9Zhlbjh1hDjmHG9q8ZTS6W yI6y6XUB33RyDOiESyYn1RYkCzeGUoYgd7uFt0zbs81UZhTFOhM/fuW7c2/oLU2dB7 6sWOzjkyTOT3Sv9afGh9IA0cZothLwjnUD3R3Bfbghe4eABrXoPfzbLn+UEepT+NfX Y3OVP3EPY/1ZvzmP1baz2XR1wa0+sX3/28R1PmYVq3UAtV1gr7u/+4mv0wgzZvA1Tb c2cIcwLawbokKQFpBXxqAwWbdN7sbeV+xSTGzR5FxMbVSVnMCNl5yd+/b0iN/R77Nk TFgEcu4KMFrAw== Content-Type: multipart/alternative; boundary="------------VgAGNcq3ZdUGb0ZWOYDeVKxO" Message-ID: Date: Wed, 29 Oct 2025 19:44:01 +0100 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: <8ABD45BB-37B7-4394-AB59-10BC1C25797F@gmail.com> <7d2f7e34-c724-4227-a1c2-8d5882a6a3d4@gmail.com> Content-Language: en-US From: Krzysztof In-Reply-To: <7d2f7e34-c724-4227-a1c2-8d5882a6a3d4@gmail.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------VgAGNcq3ZdUGb0ZWOYDeVKxO Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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'; Pozdrawiam Krzysztof Jasieński On 10/29/25 5:12 PM, Roland Müller wrote: > > Hello, > > > COPY TO  stores the output into a file on the server rather than in > your local where client is running. > > > Spaces in the file name are actually no issue: > > $ psql -h 172.17.0.2 -U postgres -c "COPY (SELECT * FROM pg_class) TO > '/var/tmp/pg_class.       OUT  x'" > Password for user postgres: > COPY 420 > > ... but the output file is on the server - in this example inside a > docker container > > postgres@7680e256387f:~$ ls -ltr /var/tmp > total 208 > ... > -rw-r--r-- 1 postgres postgres 49291 Oct 29 16:08 'pg_class.    OUT  x' > > > Copying to client side can be done with \copy () to > ''; > > > BR/Pozdrawiam, > > Roland > > > On 10/29/25 01:03, Rob Sargent wrote: >> >> >>> On Oct 28, 2025, at 9:39 AM, Krzysztof wrote: >>> >>>  >>> >>> I try to use COPY TO command to copy results of a SELECT but I get >>> this message: >>> >>> ERROR:  could not open file "a file" for writing: Permission denied >>> >> >> Is there really a space in the actual file name. That might get >> tricky. You’re suggesting the file already exists: is that true? >> Directory permissions? >>> >>> User, group and other are granted full permission to this file. What >>> should I do more to get this working? >>> >>> -- >>> Pozdrawiam >>> Krzysztof Jasieński --------------VgAGNcq3ZdUGb0ZWOYDeVKxO Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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';

 

Pozdrawiam
Krzysztof Jasieński
On 10/29/25 5:12 PM, Roland Müller wrote:

Hello,


COPY TO  stores the output into a file on the server rather than in your local where client is running.


Spaces in the file name are actually no issue:

$ psql -h 172.17.0.2 -U postgres -c "COPY (SELECT * FROM pg_class) TO '/var/tmp/pg_class.       OUT  x'"
Password for user postgres: 
COPY 420

... but the output file is on the server - in this example inside a docker container

postgres@7680e256387f:~$ ls -ltr /var/tmp
total 208
...
-rw-r--r-- 1 postgres postgres 49291 Oct 29 16:08 'pg_class.       OUT  x'


Copying to client side can be done with \copy (<query or table>) to '<output file>';


BR/Pozdrawiam,

Roland


On 10/29/25 01:03, Rob Sargent wrote:


On Oct 28, 2025, at 9:39 AM, Krzysztof <kj@limes.com.pl> wrote:



I try to use COPY TO command to copy results of a SELECT but I get this message:

ERROR:  could not open file "a file" for writing: Permission denied


Is there really a space in the actual file name. That might get tricky. You’re suggesting the file already exists: is that true? Directory permissions?

User, group and other are granted full permission to this file. What should I do more to get this working?

-- 
Pozdrawiam
Krzysztof Jasieński
--------------VgAGNcq3ZdUGb0ZWOYDeVKxO--