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.
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
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.