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

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]