Thread

1. Linux file permission for COPY TO SQL command

   Krzysztof <kj@limes.com.pl> — 2025-10-28T19:39:17Z

   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
   
   User, group and other are granted full permission to this file. What 
   should I do more to get this working?
   
   -- 
   Pozdrawiam
   Krzysztof Jasieński
   

2. Re: Linux file permission for COPY TO SQL command

   Rob Sargent <robjsargent@gmail.com> — 2025-10-28T23:03:07Z

   
   > 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
   

3. Re: Linux file permission for COPY TO SQL command

   Roland Müller <rolmur@gmail.com> — 2025-10-29T16:12:59Z

   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

4. Re: Linux file permission for COPY TO SQL command

   Krzysztof <kj@limes.com.pl> — 2025-10-29T18:44:01Z

   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

5. Re: Linux file permission for COPY TO SQL command

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-10-29T19:55:53Z

   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
   
   
   
   

6. Re: Linux file permission for COPY TO SQL command

   Roland Müller <rolmur@gmail.com> — 2025-10-29T20:11:19Z

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

7. Re: Linux file permission for COPY TO SQL command

   Krzysztof <kj@limes.com.pl> — 2025-11-02T21:45:51Z

   That helped. Thanks.
   
   Pozdrawiam
   Krzysztof Jasieński
   
   On 10/29/25 8:55 PM, Tom Lane wrote:
   > 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
   >
   >