PostgreSQL – Change Permissions of Copy to Exported File

copyexportlinuxpostgresql

I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here.

The original question is: https://stackoverflow.com/questions/25619290/change-permissions-of-postgres-copy-to-exported-file

And the text is below. Thanks.

When running the postgresql [9.3] "COPY TO" functionality, the postgres user creates a file. I have told it to put the file in the tmp directory (although this can be changed if needed).

This is run as part of a cron job, the user is an "export" user set up especially for this task. The end goal is for the exported file to end up in /home/export/created_file.csv.

1) postgres can't stash the exported file straight into the home directory of another user without setting up the folder to be overly permissive.

2) the export user can copy the file from /tmp to it's home directory, but then can not clean up (delete) the postgres owned file in /tmp.

This is a daily process and so old files will need to be deleted in some way. I can add a cron job to clean up the files afterwards but I was hoping for a cleaner solution – either a way to change the permissions on the file that postgres creates, or some way to give the export user the additional permission of being able to delete that file in advance of it's creation.

Giving the export user the same permissions as postgres in general however isn't an option, as it would then have free reign over the database.

Any advice, be it unix based or postgres based would be very welcome, thanks.

Best Answer

You'll want to create a directory without the t (sticky) permissions bit set, and have PostgreSQL copy the file into there. You should also make the directory setgid (g+s), as that makes the group inherited from the directory, not the creating process; see setgid directories.

You could put this directory in /tmp but personally I'd put it some neutral location like /var/lib, or mounted volume somewhere, as /tmp gets cleaned out automatically so you'll need to have the directory re-created if it's missing.

Assuming you do use /tmp:

mkdir -p /tmp/pgcsv
chgrp users /tmp/pgcsv
chmod u=rwX,g=rwsX,o= /tmp/pgcsv

then create the csv in there. It'll be automatically created with group-owner users, which will make it readable by your users (unless PostgreSQL's umask is too restrictive). Because the directory is group-writable by users (or whatever group you use) and does not have the t (sticky) bit set your users can delete files they do not own from it.

The reason this doesn't work when using /tmp/ directly is that the sticky bit is set in /tmp for security, so that user1 can't delete a file created by user2 and replace it with a symlink that user2 then writes to, allowing user1 to steal their data / trick them into overwriting things.

Other options:

Run the cron job as user export, using psql's \copy command, so it writes the data over the client/server socket and the file is created as the export user in the first place. This is by far the simplest option, and only slightly slower than a direct COPY.
Have a cron job running as root invoke the PostgreSQL copy then move the data file and change its ownership once the COPY finishes.
Give the export user the ability to run a very restricted command as root via sudo to move/delete.
Use POSIX ACLs to grant the postgres user special permission to write to /home/export (if POSIX ACLs are enabled on your file system)
Set group-owner of /home/export to postgres and make its permissions g=wx. PostgreSQL can add new entries, and overwrite anything within that it has write permission to, but not list entries. I don't like this option much.

Related Solutions

Postgresql – Index Generation in Postgres

If you create the index before loading the table, the time taken to load the data will be significantly increased.

pre load:

create table my_table1(val integer);
create index my_index1 on my_table1(val);
insert into my_table1(val) select generate_series(1,100000) order by random();
Time: 31755.858 ms

post load:

create table my_table2(val integer);
insert into my_table2(val) select generate_series(1,100000) order by random();
Time: 15344.130 ms
create index my_index2 on my_table2(val);
Time: 4073.686 ms

If you are ok with that, with pg_restore you can:

Load just the schema using --schema-only
Create the index with --index
Load the data using --data-only

Of course "Buy more storage" may well be the best answer here...

Postgres \copy Command – Accessing Temporary Tables

\copy can use a temporary table.

First I tested and confirmed this with version 9.0 at the command line.
Then I created a file with SQL and psql meta command \copy using multiple temporary tables. That worked for me, too.

CREATE TEMP TABLE tmp as SELECT * FROM tbl;
\copy (SELECT * FROM tmp JOIN tbl USING (id)) TO '/var/lib/postgres/test1.csv';

Call:

psql -p5432 mydb -f test.sql

Note the terminating semicolon, which is optional at the end of a file (terminated implicitly), but required after any other SQL statement and also after the last one if executed in psql interactively.

Normally, psql meta-commands cannot be mixed with SQL on the same line in a file executed per psql -f. I quote the manual on psql:

Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.

Different rules apply after \copy, though. Essentially, psql switches back to SQL mode automatically after \copy See:

How to update selected rows with values from a CSV file in Postgres?

But you wrote you had all commands on separate lines. So that cannot be the explanation in your case.

All that aside, have you considered using COPY (the SQL command) instead of \copy (the psql meta-command)?

Of course, the target file would have to be local to the server not the client in this case. And different file privileges apply. The manual:

Files named in a COPY command are read or written directly by the server, not by the client application. Therefore, they must reside on or be accessible to the database server machine, not the client. They must be accessible to and readable or writable by the PostgreSQL user (the user ID the server runs as), not the client.

Best Answer

Related Solutions

Postgresql – Index Generation in Postgres

Postgres \copy Command – Accessing Temporary Tables

Related Question