Skip site navigation (1) Skip section navigation (2)

Re: pg_execute_from_file review

From: Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>
To: Dimitri Fontaine <dimitri(at)2ndquadrant(dot)fr>
Cc: Joshua Tolley <eggyknap(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_execute_from_file review
Date: 2010-11-30 03:16:23
Message-ID: AANLkTimj4ozp1Vgv8ggQxaLfgB9cNaG5M+aMgZ14a2-J@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-11-30 03:19:22
Subject: Re: DELETE with LIMIT (or my first hack)
Previous:From: Robert HaasDate: 2010-11-30 03:11:47
Subject: Re: DELETE with LIMIT (or my first hack)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group