Re: [GENERAL] COPY to question
On Tue, Jan 17, 2017 at 10:23 AM, Rich Shepard 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). > > Why is this, and can I change something so I, as a user, can copy tables > directly to ~/? > > To add to the other answers, more info is available at https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY and https://wiki.postgresql.org/wiki/COPY Note that you can invoke SQL COPY to STDOUT as in: COPY (some arbitrary query) TO STDOUT; You would either pipe/redirect the output of psql as desired or use the "\o" within psql to reroute the output to a file or pipe to a program, for example, output to a CSV using a pipe as the delimiter and double-quote as the quote character but change all "ma" to "pa" and put into myoutput.txt \o | sed s/ma/pa/g > myoutput.txt copy (some query) to stdout csv header delimiter '|' quote '"'; \o Cheers, Steve
Re: [GENERAL] COPY to question
On Tue, Jan 17, 2017 at 11:23 AM, Rich Shepard 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.
Re: [GENERAL] COPY to question
> On Jan 17, 2017, at 10:23 AM, Rich Shepard 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). > > Why is this, and can I change something so I, as a user, can copy tables > directly to ~/? You can use "\copy" from psql to do the same thing as the SQL copy command, but writing files as the user running psql, rather than the postgresql superuser role. That's probably what you need. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY to question
2017-01-17 19:23 GMT+01:00 Rich Shepard : > 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). > > Why is this, and can I change something so I, as a user, can copy tables > directly to ~/? You cannot to use server side COPY for writing directly to client side directory. If you use psql console, and there is client side backslash COPY statement. There you can write anywhere on client side, where you have a access. \COPY table TO ~/xxx.dta is valid there. Regards Pavel > > > Rich > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] COPY to question [ANSWERED]
On Tue, 17 Jan 2017, Tom Lane wrote: Use psql's \copy instead. Thanks, Tom. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY to question
Rich Shepard writes: >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). >Why is this, and can I change something so I, as a user, can copy tables > directly to ~/? Use psql's \copy instead. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY to question
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). Why is this, and can I change something so I, as a user, can copy tables directly to ~/? Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
