Postgresql – Is postgres vacuum secure

You can save on the delete and vacuum if you will use smart partitioning! Make sure your design is compatible with dropping partitions instead of actual DELETE.

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.