Re: COPY to question

On Tue, Jan 17, 2017 at 11:23 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> Running -9.6.1. I have a database created and owned by me, but cannot
> copy
> a table to my home directory. Postgres tells me it cannot write to that
> directory. The only way to copy tables to files is by doing so as the
> superuser (postgres).
>

​When you ask the server to access the filesystem (e.g., via SQL COPY) it
does so on your behalf but using its own operating system user​. It makes
no attempt to match the role that you are logged in as with a corresponding
operating system user.

As Tom noted if you want to do things as "you" and not "the server" you
need to perform them within a client (psql being the main one). In psql
you can get COPY functionality via the \copy meta-command. The server
sends its output to the client which then redirects it to some path on the
local machine. If you run psql on the server you can access a home
directory on the server.

>
> Why is this

​COPY naming a file or command is only allowed to database superusers,
since it allows reading or writing any file that the server has privileges
to access.​

See above for why its the sever's privileges that matter.

David J.