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.
Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.
This does become more complicated in your schema definition but brings remote tables from host some.other.server
database remote_db
to appear as though they live on localhost
in database local_db
. A simple example on how to hook up the wrappers:
CREATE EXTENSION postgres_fdw;
CREATE SERVER some_remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'some.remote.server', port '5432', dbname 'remote_db');
CREATE USER MAPPING FOR local_user
SERVER some_remote_server
OPTIONS (user 'remote_user', password 'remote_user_password');
CREATE FOREIGN TABLE local_table_name (id int, value int)
SERVER some_remote_server
OPTIONS ( schema_name 'remote_schema_name', table_name 'remote_table_name');
Now locally you can just run
SELECT * from local_table_name
and the query will get executed against the remote host. Needless to say, this requires connectivity between the two servers.
Similarly, if you really have to, you can create a "remote" server against the localhost and point to a different local database for cross database queries. Feels dirty but it's possible.
As @a_horse_with_no_name mentioned though, this is not very efficient. If you find yourself doing this too frequently, you're not getting the most optimal performance and you'd better have very good reasons for keeping your databases separate at that point.
Best Answer
You can use a
WHERE
condition:Here
id
stands for any numerical column. You can also use other data types and split up the data in other ways with an appropriateWHERE
condition.