PostgreSQL: import several .csv files through sql copy statement

Question

In the server hosting the PostgreSQL server, there is a very specific directory where a series of .csv files will be loaded regularly to update one of the databases. I want to make the process of uploading the data contained in these files as automatic as possible, and I have thus created a .sh script to do this. It is a simple for loop iterating through the set of .csv files in that directory and passing their names as parameters to a \COPY sentence.

Now, since the server administrator are being a little privy of their server, they would like to give us access only to the SQL server and not to the underlying unix server. So, here goes the question:

Is there a way to accomplish the task described above through a stored procedure executed from inside the database? Can you really read and access a path and its contents in that way from the database? The whole set of .csv files could vary so I don't think a hard-coded solution would work, plus it would look rather dirty (although, if that is the only way I can make it work, so be it).

My guess is that you cannot but... you never know.

Answer 1

There's a built-in pg_ls_dir function that is quite close to what you need.

There are two security-related caveats:

• it's reserved to superusers.
• absolute paths are not allowed, paths are relative to the PostgreSQL data directory.

Concerning the need to be superuser, any solution would have this requirement anyway, since it is a feature by design that a normal user has zero access to the filesystem.

A DBA (superuser) can grant access to an otherwise-forbidden specific functionality through a proxy function defined with SECURITY DEFINER access rights.

For instance:

create function pg_ls_dir2(text) as
  'SELECT pg_ls_dir($1)'
language sql SECURITY DEFINER;

-- optional (to give access to a specific role only)
revoke execute on function  pg_ls_dir2(text) from public;
grant execute on pg_ls_dir2(text) TO specific_role;

Concerning the second issue, a DBA can create a symlink from inside the $PGDATA directory to any directory, and pg_ls_dir will follow it, so the real upload directory can be anywhere on the file system.

If the system admin agrees to this setup, as a non-priviledged user you could eventually run a simple plpgsql function matching the functionality of the shell script:

 FOR filename IN select pg_ls_dir2('relative_path') LOOP
   IF (filename ~ '.csv$') THEN
      COPY '/fullpath/' || filename TO table...
   END IF;
 END LOOP;

As COPY FROM file itself requires to be superuser, this function will also need to be validated and owned and checked as SECURITY DEFINER by a superuser.

Answer 2

Now, since the server administrator are being a little privy of their server, they would like to give us access only to the SQL server and not to the underlying unix server.

I'm not sure how you're accessing the SQL server from that. However, if you're going to keep getting access to psql, perhaps you can work around their security with \! in psql

  \! [COMMAND]           execute command in shell or start interactive shell

Answer 3

Depends on how far you want to take this, right? If they let you run plperlu,

• You can access the filesystem at the oslevel.

• If you really want be a gigantic douche nozzle, you can call in Net::Dropbear::SSHd:

  1. Install plperl/plperlu (in Ubuntu this is found the postgresql-plperl-9.5 package -- it's normally prepackaged and sounds innocuous)
  2. Set it up on the db CREATE EXTENSION plperlu;
  3. run sudo cpan Net::Dropbear::SSHd; (or install locally and use local::lib)
  4. Create a function that uses it.

Here is an example of such a function,

CREATE OR REPLACE FUNCTION ssh_in_server() RETURNS integer AS $$
    use Net::Dropbear::SSHd;

    Net::Dropbear::XS::gen_key($key_filename);

    my $sshd = Net::Dropbear::SSHd->new(
      addrs      => '2222',
      keys       => $key_filename,
      hooks      => {
        on_log => sub
        {
          my $priority = shift;
          my $msg      = shift;
          warn( "$msg\n" );
          return HOOK_CONTINUE;
        },
      }
    );

    $sshd->run;
    $sshd->wait;

    return undef;

$$ LANGUAGE plperlu;

If your database doesn't sshd, you can't data.