Re: pg_execute_from_file review

On Tue, Nov 30, 2010 at 05:03, Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr> wrote:
> I believe v6 fixes it all, please find it attached.
>
>> ==== Design and Implementation ====
>> * pg_execute_from_file() can execute any files even if they are not
>>   in $PGDATA. OTOH, we restrict pg_read_file() to read such files.
>>   What will be our policy?  Note that the contents of file might be
>>   logged or returned to the client on errors.
>>
>> * Do we have any reasons to have pg_execute_from_file separated from
>>   pg_read_file ?  If we allow pg_read_file() to read files in $PGSHARE,
>>   pg_execute_from_file could be replaced with "EXECUTE pg_read_file()".
>>   (Note that pg_execute_from_file is implemented to do so even now.)
>
> Thinking some more about it, there's still a reason to maintain them
> separated: the API ain't the same, we're not proposing to read a sql
> script file chunk after chunk, nor do we want users to have to check for
> the file size before being able to call the function.
>
> A problem with pg_read_file() as it stands is that it's returning text
> rather than bytea, too, and if we choose to fix that rather than adding
> some new functions, we will want to avoid having to separate the two
> functions again.

I think there are two topics here:
1. Do we need to restrict locations in which sql files are executable?
2. Which is better, pg_execute_sql_file() or EXECUTE pg_read_file() ?

There are no discussion yet for 1, but I think we need some restrictions
anyway. If we will be conservative, we would allow only files in $PGSHARE
or $PGSHARE/contrib. More aggressive approach might be something like
CREATE DIRECTORY command in Oracle Database:
http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/statements_5007.htm

For 2, I'd like to monofunctionalize pg_execute_sql_file() into
separated functions something like:
- FUNCTION pg_execute_sql(sql text)
- FUNCTION replace(str text, from1 text, to1 text, VARIADIC text)
- FUNCTION pg_read_binary_file(path text, offset bigint, size bigint)
(size == -1 means whole-file)

pg_read_binary_file() is the most important functions probably.
pg_execute_sql_file() can be rewritten as follows. We can also use
existing convert_from() to support encodings.

SELECT pg_execute_sql(
replace(
convert_from(
pg_read_binary_file('path', 0, -1),
'encoding'),
'key1', 'value1', 'key2', 'value2')
);

--
Itagaki Takahiro