public inbox for [email protected]
help / color / mirror / Atom feedLinux file permission for COPY TO SQL command
7+ messages / 4 participants
[nested] [flat]
* Linux file permission for COPY TO SQL command
@ 2025-10-28 19:39 Krzysztof <[email protected]>
2025-10-28 23:03 ` Re: Linux file permission for COPY TO SQL command Rob Sargent <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Krzysztof @ 2025-10-28 19:39 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Linux file permission for COPY TO SQL command
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
@ 2025-10-28 23:03 ` Rob Sargent <[email protected]>
2025-10-29 16:12 ` Re: Linux file permission for COPY TO SQL command Roland Müller <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2025-10-28 23:03 UTC (permalink / raw)
To: Krzysztof <[email protected]>; +Cc: [email protected]
> On Oct 28, 2025, at 9:39 AM, Krzysztof <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Linux file permission for COPY TO SQL command
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-28 23:03 ` Re: Linux file permission for COPY TO SQL command Rob Sargent <[email protected]>
@ 2025-10-29 16:12 ` Roland Müller <[email protected]>
2025-10-29 18:44 ` Re: Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Roland Müller @ 2025-10-29 16:12 UTC (permalink / raw)
To: [email protected]
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 <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Linux file permission for COPY TO SQL command
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-28 23:03 ` Re: Linux file permission for COPY TO SQL command Rob Sargent <[email protected]>
2025-10-29 16:12 ` Re: Linux file permission for COPY TO SQL command Roland Müller <[email protected]>
@ 2025-10-29 18:44 ` Krzysztof <[email protected]>
2025-10-29 19:55 ` Re: Linux file permission for COPY TO SQL command Tom Lane <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Krzysztof @ 2025-10-29 18:44 UTC (permalink / raw)
To: [email protected]
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 <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Linux file permission for COPY TO SQL command
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-28 23:03 ` Re: Linux file permission for COPY TO SQL command Rob Sargent <[email protected]>
2025-10-29 16:12 ` Re: Linux file permission for COPY TO SQL command Roland Müller <[email protected]>
2025-10-29 18:44 ` Re: Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
@ 2025-10-29 19:55 ` Tom Lane <[email protected]>
2025-10-29 20:11 ` Re: Linux file permission for COPY TO SQL command Roland Müller <[email protected]>
2025-11-02 21:45 ` Re: Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Tom Lane @ 2025-10-29 19:55 UTC (permalink / raw)
To: Krzysztof <[email protected]>; +Cc: [email protected]
Krzysztof <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Linux file permission for COPY TO SQL command
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-28 23:03 ` Re: Linux file permission for COPY TO SQL command Rob Sargent <[email protected]>
2025-10-29 16:12 ` Re: Linux file permission for COPY TO SQL command Roland Müller <[email protected]>
2025-10-29 18:44 ` Re: Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-29 19:55 ` Re: Linux file permission for COPY TO SQL command Tom Lane <[email protected]>
@ 2025-10-29 20:11 ` Roland Müller <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Roland Müller @ 2025-10-29 20:11 UTC (permalink / raw)
To: [email protected]
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 <[email protected]> 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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Linux file permission for COPY TO SQL command
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-28 23:03 ` Re: Linux file permission for COPY TO SQL command Rob Sargent <[email protected]>
2025-10-29 16:12 ` Re: Linux file permission for COPY TO SQL command Roland Müller <[email protected]>
2025-10-29 18:44 ` Re: Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-29 19:55 ` Re: Linux file permission for COPY TO SQL command Tom Lane <[email protected]>
@ 2025-11-02 21:45 ` Krzysztof <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Krzysztof @ 2025-11-02 21:45 UTC (permalink / raw)
To: [email protected]
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
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-11-02 21:45 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-28 19:39 Linux file permission for COPY TO SQL command Krzysztof <[email protected]>
2025-10-28 23:03 ` Rob Sargent <[email protected]>
2025-10-29 16:12 ` Roland Müller <[email protected]>
2025-10-29 18:44 ` Krzysztof <[email protected]>
2025-10-29 19:55 ` Tom Lane <[email protected]>
2025-10-29 20:11 ` Roland Müller <[email protected]>
2025-11-02 21:45 ` Krzysztof <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox