On 24/09/2013 02:14, Adrian Klaver wrote:
Just a thought:
1) Create a function that encapsulates the above logic where the argument is the file name.
2) Create a shell script. Use substitution to take the filename passed to the shell script to build a string and in the script do
psql -d db_name -U user_name -c 'select the function(filename)'
2a) Use alternate language to do 2).
This has the advantage that you can enforce security inside the function. Note that for very good reasons, Postgres requires server-side COPY commands to be run under a super-user account - but this is exactly where a SECURITY DEFINER function comes in handy. Basically, your psql command would run as a non-privileged user, but the function would run as the superuser that created/owned it. The important part is that you are
asking for the command to run, not
demanding it - the script can and should say no sometimes! (I have a half-written blog-post on this subject kicking around...)
The import would be easy if the files had a constant name but the app creates csv files with names like "ExportYYYYMMDD".
If that's the
current year month and day, it should be easy enough to generate the correct filename, rather than having to search the file system. Or perhaps iterate backwards from today to find the latest (or give up if you got to too old a date).
On the other hand...
This app can't talk to the db but only creates daily a dump in a remote directory that is mountable via samba
Is there a reason for that access constraint? As Steve mentioned, psql has a \copy command which works like the COPY SQL statement, but reads the file from the
client and sends it over a connection, eliminating the need for a superuser account at the database end. If database security is a concern, you could have a login with extremely limitied privileges, and whitelist it explicitly in pg_hba.conf for this purpose.
Regards,
--
Rowan Collins
[IMSoP]
