让postgresql返回服务端的二进制文件

 

在postgresql的8.2后，加了一个函数pg_read_file,可以读取服务器上的文件返回给客户端。

 

该文件返回类型为TEXT类型，所以只能读取文本文件，对于二进制文件或者字符集与数据库默认字符集不兼容的文本文件，所读的数据将被截断。

 

如何能读二进制文件，返回bytea类型呢？经源代码分析，该函数的内置函数其实是可以读取二进制的，只是接口被转化为文本类型，而在PG内部TEXT型和BYTEA类型的指针结构是一样的。

 

所以可以自定义函数

CREATE OR REPLACE FUNCTION "public"."pg_read_file_binary" (text, bigint, bigint) RETURNS bytea AS
'pg_read_file'
LANGUAGE 'internal' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

 

该函数就可以返回二进制函数了,也可以用CONVERT转化为自己需要的文本类型.

在具体应用中，如在数据主目录下建立一个graph目录，可以通过SQL语句下载该目录下的图形文件。

 

安全问题

本函数必须为超级用户（或授权超级角色的用户）才能使用。且只能读取数据主目录下的文件。

 

让普通用户拥有超级权限，是危险的。同时让用户不加选择的可以读取数据目录下的任何文件，也是危险的。

 

最好的方法是，让普通用户只读取某个特定目录下的文件。所以再建立一个函数

CREATE OR REPLACE FUNCTION "public"."pg_read_file_binary_s" (S_FILE text, I_SEEK bigint, I_LENGTH bigint) RETURNS bytea AS

$BODY$

DECLARE

    I BIGINT; 

BEGIN

    IF I_LENGTH=0 then  -- 获取文件的长度

       I=(pg_stat_file(S_FILE)).size;

    else

       i=i_length;

    endif

    IF substr(S_FILE,0,5)<>'graph'

       raise exception '只能读取graph目录';

    end if;

    return pg_read_file_binary(s_file,i_seek,i);
END

$BODY$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

 

 

注意用超级用户建立该函数,并且将security设为definer.

 

则普通用户只用在调用该函数时会拥有超级权限,且该函数中已经进行了些限制.