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 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?
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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?
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>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general