Re: pg_execute_from_file review - Mailing list pgsql-hackers

From Itagaki Takahiro
Subject Re: pg_execute_from_file review
Date
Msg-id AANLkTimj4ozp1Vgv8ggQxaLfgB9cNaG5M+aMgZ14a2-J@mail.gmail.com
Whole thread Raw
In response to Re: pg_execute_from_file review  (Dimitri Fontaine <dimitri@2ndQuadrant.fr>)
Responses Re: pg_execute_from_file review
List pgsql-hackers
On Tue, Nov 30, 2010 at 05:03, Dimitri Fontaine <dimitri@2ndquadrant.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


pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: DELETE with LIMIT (or my first hack)
Next
From: Robert Haas
Date:
Subject: Re: DELETE with LIMIT (or my first hack)