Content-Type: multipart/alternative;
 boundary="------------6l00JYm71nhPVmghUfgaBXce"
Message-ID: <7d2f7e34-c724-4227-a1c2-8d5882a6a3d4@gmail.com>
Date: Wed, 29 Oct 2025 18:12:59 +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>
Content-Language: en-US
From: =?UTF-8?Q?Roland_M=C3=BCller?= <rolmur@gmail.com>
In-Reply-To: <8ABD45BB-37B7-4394-AB59-10BC1C25797F@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/7d2f7e34-c724-4227-a1c2-8d5882a6a3d4%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------6l00JYm71nhPVmghUfgaBXce
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------6l00JYm71nhPVmghUfgaBXce
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hello,</p>
    <p><br>
    </p>
    <p>COPY TO  stores the output into a file on the server rather than
      in your local where client is running.</p>
    <p><br>
    </p>
    <p>Spaces in the file name are actually no issue:</p>
    <p>$ psql -h 172.17.0.2 -U postgres -c "COPY (SELECT * FROM
      pg_class) TO '/var/tmp/pg_class.       OUT  x'"<br>
      Password for user postgres: <br>
      COPY 420<br>
    </p>
    <p>... but the output file is on the server - in this example inside
      a docker container</p>
    <p>postgres@7680e256387f:~$ ls -ltr /var/tmp<br>
      total 208<br>
      ...<br>
      -rw-r--r-- 1 postgres postgres 49291 Oct 29 16:08 'pg_class.     
       OUT  x'</p>
    <p><br>
    </p>
    <p>Copying to client side can be done with \copy (&lt;query or
      table&gt;) to '&lt;output file&gt;';</p>
    <p><br>
    </p>
    <p>BR/Pozdrawiam,</p>
    <p>Roland</p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 10/29/25 01:03, Rob Sargent wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:8ABD45BB-37B7-4394-AB59-10BC1C25797F@gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr"><br>
      </div>
      <div dir="ltr"><br>
        <blockquote type="cite">On Oct 28, 2025, at 9:39 AM, Krzysztof
          <a class="moz-txt-link-rfc2396E" href="mailto:kj@limes.com.pl">&lt;kj@limes.com.pl&gt;</a> wrote:<br>
          <br>
        </blockquote>
      </div>
      <blockquote type="cite">
        <div dir="ltr">﻿
          <meta http-equiv="content-type"
            content="text/html; charset=UTF-8">
          <p>I try to use COPY TO command to copy results of a SELECT
            but I get this message:</p>
          <p> <span style="font-family:monospace"><span
                style="color:#000000;background-color:#ffffff;">ERROR:
                 could not open file "a file" for writing: Permission
                denied</span><br>
              <span style="color:#000000;background-color:#ffffff;"></span><br>
            </span></p>
        </div>
      </blockquote>
      <div><br>
      </div>
      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?<br>
      <blockquote type="cite">
        <div dir="ltr">
          <p><span style="font-family:monospace"> User, group and other
              are granted full permission to this file. What should I do
              more to get this working?</span></p>
          <pre class="moz-signature" cols="72">-- 
Pozdrawiam
Krzysztof Jasieński
</pre>
        </div>
      </blockquote>
    </blockquote>
  </body>
</html>

--------------6l00JYm71nhPVmghUfgaBXce--