Re: Howto import regularly some CSV files with variing names?

Rowan Collins <rowan.collins@xxxxxxxxx> · Tue, 24 Sep 2013 20:25:24 +0100



    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]