主页 / dba / 问题 / 157467
Accepted
Feillen
Asked: 2016-12-08 02:06:47 +0800 CST

PostgreSQL：通过sql copy语句导入几个.csv文件

  • 772

在托管 PostgreSQL 服务器的服务器中，有一个非常特定的目录，将定期加载一系列 .csv 文件以更新其中一个数据库。我想让上传这些文件中包含的数据的过程尽可能自动化，因此我创建了一个.sh脚本来执行此操作。这是一个简单的for循环，遍历该目录中的 .csv 文件集，并将它们的名称作为参数传递给\COPY句子。

现在，由于服务器管理员对他们的服务器有一点了解，他们想让我们只访问 SQL 服务器而不是底层的 unix 服务器。所以，问题来了：

有没有办法通过从数据库内部执行的存储过程来完成上述任务？你真的能以这种方式从数据库中读取和访问路径及其内容吗？整个 .csv 文件集可能会有所不同，所以我认为硬编码的解决方案不会起作用，而且它看起来很脏（尽管，如果这是我让它工作的唯一方法，那就这样吧）。

我的猜测是你不能但是......你永远不知道。

postgresql

3 个回答

  1. 现在，由于服务器管理员对他们的服务器有一点了解，他们希望只允许我们访问 SQL 服务器而不是底层的 unix 服务器。

    我不确定您是如何从中访问 SQL 服务器的。但是，如果您要继续访问psql，也许您可​​以解决他们的安全\!问题psql

      \! [COMMAND]           execute command in shell or start interactive shell
    • 2
  2. Best Answer

    pg_ls_dir有一个非常接近您需要的内置功能。

    有两个与安全相关的警告：

    • 它保留给超级用户。
    • 不允许使用绝对路径，路径是相对于 PostgreSQL 数据目录的。

    关于成为超级用户的需要，任何解决方案无论如何都会有这个要求，因为它是一个设计特性，普通用户对文件系统的访问权限为零。

    DBA（超级用户）可以通过定义了访问权限的代理功能授予对其他禁止的特定功能的SECURITY DEFINER访问权限。

    例如：

    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;

    关于第二个问题，DBA 可以从$PGDATA目录内部创建到任何目录的符号链接，并pg_ls_dir跟随它，因此真正的上传目录可以是文件系统上的任何位置。

    如果系统管理员同意此设置，作为非特权用户，您最终可以运行一个与 shell 脚本功能匹配的简单 plpgsql 函数：

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

    由于COPY FROM file它本身需要成为超级用户，因此该功能也需要SECURITY DEFINER由超级用户进行验证、拥有和检查。

    • 2

  3. 这取决于你想走多远，对吧？如果他们让你跑plperlu，

    • 您可以在操作系统级别访问文件系统。

    • 如果你真的想成为一个巨大的冲洗喷嘴，你可以调用Net::Dropbear::SSHd：

      1. 安装 plperl/plperlu（在 Ubuntu 中可以找到这个postgresql-plperl-9.5包——它通常是预先打包的，听起来无害）
      2. 在数据库上设置它CREATE EXTENSION plperlu;
      3. 运行sudo cpan Net::Dropbear::SSHd;（或在本地安装并使用local::lib）
      4. 创建一个使用它的函数。

    这是此类功能的示例，

    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;

    如果你的数据库没有 sshd，你就不能数据。

    • 1

相关问题