Feature request: include script file into function body

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially Python
and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any file
the backend has access to.

regards, tom lane

Hi Tom,

This seems like a detail that is beside the point I'm making.
But security is important, so let's think about it.

PostgreSQL has an \i command, which loads the text from any readable file
interpretes and executes it as further PostgreSQL commands. I'm proposing
a similar mechanism that would load a file containing script language, and
process it as though it were in the current funcition body.

Isn't the \i command a similar security hole?

If somehow loading script text for a function is substantially different
from loading it by \i, and if there is some problem, it seems to me that
some simple restriction could solve it, such as restricting the directories
from which such files can be read. But I'm just guessing here.

I'll leave it to the security experts explicitly by amending my original
proposal with this:

" -- without doing anything stupid that would open a security hole."

Cheers again!

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially Python
and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any file
the backend has access to.

regards, tom lane

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -

Hello

2011/2/1 Steve White <swhite@aip.de>:

Hi Tom,

This seems like a detail that is beside the point I'm making.
But security is important, so let's think about it.

PostgreSQL has an \i command, which loads the text from any readable file
interpretes and executes it as further PostgreSQL commands.  I'm proposing
a similar mechanism that would load a file containing script language, and
process it as though it were in the current funcition body.

Isn't the \i command a similar security hole?

if you ran psql under "postgres" account, then it is.

I don't think, so your idea is good too. What about caching? Code of
stored procedures stays in session cache. Who will ensure, so your
cache is fresh?

Why you need a direct link to source files?

Regards

Pavel Stehule

[Please don't top-post. Rearranged for clarity.]

Steve White <swhite@aip.de> wrote:

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially
Python and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any
file the backend has access to.

Isn't the \i command a similar security hole?

That is run by a client program on a client machine. If that is
what you had in mind, a modification to the CREATE FUNCTION syntax
is probably not the way to go. Just to throw a hypothetical out
there, were you looking to effectively do a \i inside the string
literal which is the function body, picking up a *client-side* file?

That has its own problems, of course, but I'm just trying to get us
onto the same page.

-Kevin

Hi Pavel,

On 1.02.11, Pavel Stehule wrote:

Hello

2011/2/1 Steve White <swhite@aip.de>:

Hi Tom,

This seems like a detail that is beside the point I'm making.
But security is important, so let's think about it.

PostgreSQL has an \i command, which loads the text from any readable file
interpretes and executes it as further PostgreSQL commands. �I'm proposing
a similar mechanism that would load a file containing script language, and
process it as though it were in the current funcition body.

Isn't the \i command a similar security hole?

if you ran psql under "postgres" account, then it is.

I don't think, so your idea is good too. What about caching? Code of
stored procedures stays in session cache. Who will ensure, so your
cache is fresh?

Another good point that is beside the point I was making.

But OK we can discuss that too. I would think, it should work exactly as
if the text had been textually included, the first time the function is
compiled, exactly as the inline text is handled now.

Why you need a direct link to source files?

There are several reasons, a couple of which are mentioned in the
discussion in the pgsql-general list.
http://archives.postgresql.org/pgsql-general/2011-01/msg00870.php

Cheers!

Regards

Pavel Stehule

If somehow loading script text for a function is substantially different
from loading it by \i, and if there is some problem, it seems to me that
some simple restriction could solve it, such as restricting the directories
from which such files can be read. �But I'm just guessing here.

I'll leave it to the security experts explicitly by amending my original
proposal with this:

� � � �" -- without doing anything stupid that would open a security hole."

Cheers again!

On �1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially Python
and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any file
the backend has access to.

� � � � � � � � � � � regards, tom lane

--
| - �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �-
| Steve White � � � � � � � � � � � � � � � � � � � � � � +49(331)7499-202
| E-Science � � � � � � � � � � � � � � � � � � � �Zi. 27 �Villa Turbulenz
| - �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �-
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �- �-

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -

Hi Kevin,

On 1.02.11, Kevin Grittner wrote:

[Please don't top-post. Rearranged for clarity.]

As you like.

Steve White <swhite@aip.de> wrote:

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially
Python and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any
file the backend has access to.

Isn't the \i command a similar security hole?

That is run by a client program on a client machine.

Sorry I don't understand this remark.

Are you saying that \i is disabled to user postgres?
Just tried: it isn't.
Are you saying that as a normal user I can use \i to load a file that I
don't normally have access to?
Just tried: nope -- permission denied.

What scenario do you have in mind?

If that is what you had in mind, a modification to the CREATE FUNCTION syntax
is probably not the way to go. Just to throw a hypothetical out
there, were you looking to effectively do a \i inside the string
literal which is the function body, picking up a *client-side* file?

That has its own problems, of course,

If I understand what you're proposing: write a PostgreSQL function
with LANGUAGE PLPYTHONU, and inside the function body load the file
with \i. Is that it?

I already tried it, and it doesn't work for the obvious reason:
The function body is interpreted as being of the other language, in
which "\i" is a syntax error. Some other attempts are also mentioned
in the pgsql-general posting
http://archives.postgresql.org/pgsql-general/2011-01/msg00870.php

I'm not married to the syntax I suggested. The functionality I want is
to separate the function body code from the SQL code.

... but I'm just trying to get us onto the same page.

By all means.

It is clear we aren't on the same page: I'm not grasping the objections,
and you probably haven't been doing the coding that makes this mixing of
languages in one file such a nuisance.

Let's keep bashing it around.

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -

Steve White wrote:

Hi Kevin,

On 1.02.11, Kevin Grittner wrote:

[Please don't top-post. Rearranged for clarity.]

As you like.

Steve White <swhite@aip.de> wrote:

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially
Python and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any
file the backend has access to.

Isn't the \i command a similar security hole?

That is run by a client program on a client machine.

Sorry I don't understand this remark.

Are you saying that \i is disabled to user postgres?
Just tried: it isn't.
Are you saying that as a normal user I can use \i to load a file that I
don't normally have access to?
Just tried: nope -- permission denied.

What scenario do you have in mind?

\i is a psql client command, not something the backend runs.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Hi again, all,

OK I think I now know what the misunderstanding is.

[Please don't top-post. Rearranged for clarity.]

Steve White <swhite@aip.de> wrote:

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially
Python and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any
file the backend has access to.

Isn't the \i command a similar security hole?

That is run by a client program on a client machine. If that is
what you had in mind, a modification to the CREATE FUNCTION syntax
is probably not the way to go. Just to throw a hypothetical out
there, were you looking to effectively do a \i inside the string
literal which is the function body, picking up a *client-side* file?

That has its own problems, of course, but I'm just trying to get us
onto the same page.

-Kevin

I guess the "FROM filename" syntax wasn't a great choice, as it suggests
something completely different from what I was otherwise describing.
(In my own defense: I repeatedly qualified the syntax as a suggestion.)

I *DO NOT MEAN* that a query should run about grabbing files off the
server, or wherever.

I meant something like the replacement that happens with the \i command
in loading SQL, and under similar circumstances, except that somehow
non-SQL code is loadad in a function body.

Again, this would greatly facilitate programming mixed-language
programming.

Thanks!

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -

Steve White <swhite@aip.de> wrote:

On 1.02.11, Kevin Grittner wrote:

Steve White <swhite@aip.de> wrote:

Isn't the \i command a similar security hole?

That is run by a client program on a client machine.

Sorry I don't understand this remark.

The CREATE FUNCTION statement is parsed and executed on the
*server*, so implementing the feature as you describe it would
involved reading files on the database server machine. With the
security of the OS user which owns the database's data directory.
Outside of development, the client is generally (in my experience,
anyway) on another machine, or at least not running under the user
ID with rights to the PostgreSQL data directory.

It seems to me that something which let you pull the body of a
script file into the statement on the *client* side of the
connection, before sending the CREATE FUNCTION statement to the
server would not only be a lot more secure (you could only read
files that the OS would let your login read anyway), but would be
more *useful*.

Are you saying that \i is disabled to user postgres?

I'm saying it runs on the client side of the connection to the
database, running with the rights of whatever user executed psql.

If I understand what you're proposing: write a PostgreSQL function
with LANGUAGE PLPYTHONU, and inside the function body load the
file with \i. Is that it?

Well, \i inside the string literal obviously won't work. I'm saying
some logical equivalent with new syntax. Something which pulls the
file into the client software. I don't have any particularly clever
suggestions to offer for syntax.

-Kevin

On 01/02/2011 6:50 PM, Steve White wrote:

Hi again, all,

OK I think I now know what the misunderstanding is.

[Please don't top-post. Rearranged for clarity.]

Steve White<swhite@aip.de> wrote:

On 1.02.11, Tom Lane wrote:

Steve White<swhite@aip.de> writes:

It would be really nice to have a way to load script (especially
Python and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any
file the backend has access to.

Isn't the \i command a similar security hole?

That is run by a client program on a client machine. If that is
what you had in mind, a modification to the CREATE FUNCTION syntax
is probably not the way to go. Just to throw a hypothetical out
there, were you looking to effectively do a \i inside the string
literal which is the function body, picking up a *client-side* file?

That has its own problems, of course, but I'm just trying to get us
onto the same page.

-Kevin

I guess the "FROM filename" syntax wasn't a great choice, as it suggests
something completely different from what I was otherwise describing.
(In my own defense: I repeatedly qualified the syntax as a suggestion.)

I *DO NOT MEAN* that a query should run about grabbing files off the
server, or wherever.

I meant something like the replacement that happens with the \i command
in loading SQL, and under similar circumstances, except that somehow
non-SQL code is loadad in a function body.

But functions *run* on the server, in the postgres server backend, so it
would have to grab files from the server, which is where the security
issue comes in.

The \i command *runs* on the client under your own account and reads
text into the *client*, not the server. The two things are completely
different and run in completely different places.

Cheers,
Gary.

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

It seems to me that something which let you pull the body of a
script file into the statement on the *client* side of the
connection, before sending the CREATE FUNCTION statement to the
server would not only be a lot more secure (you could only read
files that the OS would let your login read anyway), but would be
more *useful*.

The usual procedure is to put the whole CREATE FUNCTION statement
into a file, which you can then send with \i. I'm not quite seeing
the point of having just the body in a file? It's not like the body
is typically useful to run as standalone code.

regards, tom lane

Hi again,

Now that I know what got you all riled, I can propose something that
might be more satisfactory. See below.

On 1.02.11, Steve White wrote:

Hi again, all,

OK I think I now know what the misunderstanding is.

[Please don't top-post. Rearranged for clarity.]

Steve White <swhite@aip.de> wrote:

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

It would be really nice to have a way to load script (especially
Python and Perl) from a separate file into a function body.

This seems like a security hole, ie, you could use it to read any
file the backend has access to.

Isn't the \i command a similar security hole?

That is run by a client program on a client machine. If that is
what you had in mind, a modification to the CREATE FUNCTION syntax
is probably not the way to go. Just to throw a hypothetical out
there, were you looking to effectively do a \i inside the string
literal which is the function body, picking up a *client-side* file?

That has its own problems, of course, but I'm just trying to get us
onto the same page.

-Kevin

I guess the "FROM filename" syntax wasn't a great choice, as it suggests
something completely different from what I was otherwise describing.
(In my own defense: I repeatedly qualified the syntax as a suggestion.)

I *DO NOT MEAN* that a query should run about grabbing files off the
server, or wherever.

I meant something like the replacement that happens with the \i command
in loading SQL, and under similar circumstances, except that somehow
non-SQL code is loadad in a function body.

Again, this would greatly facilitate programming mixed-language
programming.

Try this instead:

================================================
CREATE OR REPLACE FUNCTION
myfunc( ... )
RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU;
================================================

This would work something like this:
The script interpeter would scan the body code for comments that
start exactly with PGSQL_IMPORT. Whereever they are found, it would
attempt to open and include the text (failing appropriately if the
file can't be read).

Of course, this is language-dependent, but for any given lanugage,
something like that will work.

What do you think?

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -

Steve White <swhite@aip.de> wrote:

I *DO NOT MEAN* that a query should run about grabbing files off
the server, or wherever.

I meant something like the replacement that happens with the \i
command in loading SQL, and under similar circumstances, except
that somehow non-SQL code is loadad in a function body.

Maybe some option for the \i command? \iq (for input quoted) with
automatic $$ quoting around what is read?

That way you could do something like:

CREATE FUNCTION yadda_yadda() returns text language plpythonu as
\iq yadda_yadda.py
;

-Kevin

Tom Lane <tgl@sss.pgh.pa.us> wrote:

The usual procedure is to put the whole CREATE FUNCTION statement
into a file, which you can then send with \i. I'm not quite
seeing the point of having just the body in a file? It's not like
the body is typically useful to run as standalone code.

The OP mentioned text highlighting, which has bothered me at times
-- the whole body of the function is highlighted as a string
literal, at least in Kate, which I use heavily. (Don't laugh *too*
hard.)

I haven't ever wanted to test script functions outside of PostgreSQL
before bringing them in, but it seems at least within the realm of
possibility that someone might have functional code with doesn't
directly access the database they would like to share with outside
processes or test outside before bringing in.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

The usual procedure is to put the whole CREATE FUNCTION statement
into a file, which you can then send with \i. I'm not quite
seeing the point of having just the body in a file? It's not like
the body is typically useful to run as standalone code.

The OP mentioned text highlighting, which has bothered me at times

Ah, that's a fair point, particular with smarter editors.

Seems like what you want here is a variant of \i that pulls in the
file, escapes it as a string literal, and appends that to the
query buffer. Then you write something like

create function ... as
\istring myfunction.pl
;

A backslash command defined that way might have other applications than
CREATE FUNCTION, too.

regards, tom lane

Steve White <swhite@aip.de> writes:

Try this instead:

================================================
CREATE OR REPLACE FUNCTION
myfunc( ... )
RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU;
================================================

I think having psql decide that string literals mean something other
than their face value is Right Out --- it would bite you on the rear
just when you least expect it. And the notion of the behavior being
language-dependent is right out to the fourth power. But see my
alternative proposal to Kevin just now.

regards, tom lane

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Maybe some option for the \i command? \iq (for input quoted) with
automatic $$ quoting around what is read?

That way you could do something like:

CREATE FUNCTION yadda_yadda() returns text language plpythonu as
\iq yadda_yadda.py
;

Just got this --- looks like we independently arrived at the same
conclusion.

regards, tom lane

Hi guys,

On 1.02.11, Tom Lane wrote:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Maybe some option for the \i command? \iq (for input quoted) with
automatic $$ quoting around what is read?

That way you could do something like:

CREATE FUNCTION yadda_yadda() returns text language plpythonu as
\iq yadda_yadda.py
;

Yes this will work for me.

Just got this --- looks like we independently arrived at the same
conclusion.

regards, tom lane

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -

Hi Tom,

I already agreed to Kevin's proposed syntax, and it is better than
my suggestion, but mine isn't quite as crazy as you make out.

On 1.02.11, Tom Lane wrote:

Steve White <swhite@aip.de> writes:

Try this instead:

================================================
CREATE OR REPLACE FUNCTION
myfunc( ... )
RETURNS VOID AS '#PGSQL_IMPORT filename' LANGUAGE PLPYTHONU;
================================================

I think having psql decide that string literals mean something other
than their face value is Right Out --- it would bite you on the rear
just when you least expect it.

Interesting idea... but why would psql make this decision?
Did somebody suggest that off-line?

I said that the script interpreter might do this...

But the other syntax is better anyway.

And the notion of the behavior being
language-dependent is right out to the fourth power. But see my
alternative proposal to Kevin just now.

--
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Steve White +49(331)7499-202
| E-Science Zi. 27 Villa Turbulenz
| - - - - - - - - - - - - - - - - - - - - - - - - -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| - - - - - - - - - - - - - - - - - - - - - - - - -