Обсуждение: run COPY as user other than postgres
I would like to run the COPY command as a user other than "postgres". I find it a bit of a pain (or at least requiring
anextra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simple example
wherethe location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if I could run the
COPYTO as that user. Any ideas?
COPY (
SELECT * FROM
some_table
WHERE
2012 = EXTRACT (YEAR FROM some_column) --AND value IS NOT NULL
)
TO '/some/path/to/file/file.csv' WITH CSV HEADER;
On Tue, Apr 04/23/13, 2013 at 03:11:21PM -0500, Kirk Wythers wrote: > I would like to run the COPY command as a user other than "postgres". > I find it a bit of a pain (or at least requiring an extra step or two) > to have the postgres user own the files that I am creating with COPY > TO. Here is a simple example where the location > '/some/path/to/file/file.csv' is owned by another user and it would be > very spiffy if I could run the COPY TO as that user. Any ideas? > You should use \copy if you're using psql. That will run on the client side, as the user running psql. -Ryan Kelly
On Tue, Apr 23, 2013 at 1:11 PM, Kirk Wythers <wythe001@umn.edu> wrote: > I would like to run the COPY command as a user other than "postgres". I find it a bit of a pain (or at least requiringan extra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simpleexample where the location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if Icould run the COPY TO as that user. Any ideas? sudo chown anotheruser:postgres /some/path/to/file/file.csv sudo chmod 664 /some/path/to/file/file.csv This will set the file's group to postgres and allow it's members to write to the file. > > > > COPY ( > SELECT * FROM > some_table > WHERE > 2012 = EXTRACT (YEAR FROM some_column) --AND value IS NOT NULL > ) > TO '/some/path/to/file/file.csv' WITH CSV HEADER; > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Kind regards, Sergey Konoplev Database and Software Consultant Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
On 2013-04-23, Kirk Wythers <wythe001@umn.edu> wrote: > I would like to run the COPY command as a user other than "postgres". I find it a bit of a pain (or at least requiringan extra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simpleexample where the location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if Icould run the COPY TO as that user. Any ideas? > > > > COPY ( > SELECT * FROM > some_table > WHERE > 2012 = EXTRACT (YEAR FROM some_column) --AND value IS NOT NULL > ) > TO '/some/path/to/file/file.csv' WITH CSV HEADER; Can you use the \copy macro in psql? -- ⚂⚃ 100% natural
> On 2013-04-23, Kirk Wythers <wythe001@umn.edu> wrote: > > I would like to run the COPY command as a user other than "postgres". I find it a bit of a pain (or at least requiringan extra step or two) to have the postgres user own the files that I am creating with COPY TO. Here is a simpleexample where the location '/some/path/to/file/file.csv' is owned by another user and it would be very spiffy if Icould run the COPY TO as that user. Any ideas? Write your own client that uses the copy interface to load a file from wherever and send it to the server. Or just use the one built in to psql, as Jasen suggested. -- Bill Moran <wmoran@potentialtech.com>
On Apr 24, 2013, at 6:14 AM, Bill Moran <wmoran@potentialtech.com> wrote: >>> > > Write your own client that uses the copy interface to > load a file from wherever and send it to the server. > > Or just use the one built in to psql, as Jasen suggested. > I am using "copy to" to write data from the db out to csv files. The psql suggestion of using \copy worked like a charm.