Обсуждение: temporary functions (and other object types)
Hi, A customer of ours has the need for temporary functions. The use case is writing test cases for their databases: the idea being that their code creates a temp function which then goes away automatically at session end, just like a temp table. It's said that this makes things generally easier for the test harness. Other object types that would also be useful to have as temp-able are types, domains and casts; and perhaps (if someone sees a need) aggregates and operators. Other objects are not necessary, but if someone thinks that some more stuff should be made temp-able, we'd try to go for as general a solution as possible. But these aren't critical; functions are the main pain point. I haven't looked at the code at all to see how this would be implemented; I'm basically asking whether there would be objections to having this feature in core. -- Álvaro Herrera <alvherre@alvh.no-ip.org>
On 5 November 2010 20:36, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
Hi,
A customer of ours has the need for temporary functions. The use case
is writing test cases for their databases: the idea being that their
code creates a temp function which then goes away automatically at
session end, just like a temp table. It's said that this makes things
generally easier for the test harness.
Other object types that would also be useful to have as temp-able are
types, domains and casts; and perhaps (if someone sees a need)
aggregates and operators. Other objects are not necessary, but if
someone thinks that some more stuff should be made temp-able, we'd try
to go for as general a solution as possible. But these aren't critical;
functions are the main pain point.
I haven't looked at the code at all to see how this would be
implemented; I'm basically asking whether there would be objections to
having this feature in core.
Hi,
is that possible to create all that in one transaction? You could then make rollback and all objects will just disappear.
regards
Szymon
On Nov 5, 2010, at 12:36 PM, Alvaro Herrera wrote: > Hi, > > A customer of ours has the need for temporary functions. The use case > is writing test cases for their databases: the idea being that their > code creates a temp function which then goes away automatically at > session end, just like a temp table. It's said that this makes things > generally easier for the test harness. This is one reason why I recommend that pgTAP tests run inside a transaction. > Other object types that would also be useful to have as temp-able are > types, domains and casts; and perhaps (if someone sees a need) > aggregates and operators. Other objects are not necessary, but if > someone thinks that some more stuff should be made temp-able, we'd try > to go for as general a solution as possible. But these aren't critical; > functions are the main pain point. Running the tests inside a transaction gives you this for free, right now -- *and* leaves the database in a known state atthe end (modulo sequences). Can you just use transactions? Best, David PS: What test framework and harness are you using?
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> A customer of ours has the need for temporary functions.
You can do that now:
regression=# create function pg_temp.foo(f1 int) returns int
regression-# as 'select $1+1' language sql;
CREATE FUNCTION
regression=# select pg_temp.foo(1);foo
----- 2
(1 row)
You do have to qualify the name explicitly:
regression=# select foo(1);
ERROR: function foo(integer) does not exist
The latter is an intentional security feature and will not get changed.
The general security risks around this make me hesitant to accept the
idea of an explicit CREATE TEMP FUNCTION syntax: it's never going to
be better than a second-class feature, so it shouldn't have a
first-class syntax.
regards, tom lane
On Fri, Nov 5, 2010 at 4:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Alvaro Herrera <alvherre@alvh.no-ip.org> writes: >> A customer of ours has the need for temporary functions. > > You can do that now: > > regression=# create function pg_temp.foo(f1 int) returns int > regression-# as 'select $1+1' language sql; > CREATE FUNCTION > regression=# select pg_temp.foo(1); > foo > ----- > 2 > (1 row) > > You do have to qualify the name explicitly: > > regression=# select foo(1); > ERROR: function foo(integer) does not exist > > The latter is an intentional security feature and will not get changed. I see that there could be a problem here with SECURITY DEFINER functions, but I'm not clear whether it goes beyond that? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Nov 05, 2010 at 09:01:50PM -0400, Robert Haas wrote: > On Fri, Nov 5, 2010 at 4:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > The latter is an intentional security feature and will not get changed. > > I see that there could be a problem here with SECURITY DEFINER > functions, but I'm not clear whether it goes beyond that? IIRC correctly it's because even unpriveledged users can make things in the pg_temp schema and it's implicitly at the front of the search_path. There was a CVE about this a while back, no? Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Fri, Nov 05, 2010 at 09:01:50PM -0400, Robert Haas wrote:
>> I see that there could be a problem here with SECURITY DEFINER
>> functions, but I'm not clear whether it goes beyond that?
> IIRC correctly it's because even unpriveledged users can make things in
> the pg_temp schema and it's implicitly at the front of the search_path.
> There was a CVE about this a while back, no?
Yeah, we changed that behavior as part of the fix for CVE-2007-2138.
You'd need either SECURITY DEFINER functions or very careless use of
SET ROLE/SET SESSION AUTHORIZATION for the issue to be exploitable.
regards, tom lane
On Sat, Nov 6, 2010 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: >> On Fri, Nov 05, 2010 at 09:01:50PM -0400, Robert Haas wrote: >>> I see that there could be a problem here with SECURITY DEFINER >>> functions, but I'm not clear whether it goes beyond that? > >> IIRC correctly it's because even unpriveledged users can make things in >> the pg_temp schema and it's implicitly at the front of the search_path. >> There was a CVE about this a while back, no? > > Yeah, we changed that behavior as part of the fix for CVE-2007-2138. > You'd need either SECURITY DEFINER functions or very careless use of > SET ROLE/SET SESSION AUTHORIZATION for the issue to be exploitable. Would it be practical to let foo() potentially mean pg_temp.foo() outside of any SECURITY DEFINER context? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Sat, Nov 6, 2010 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, we changed that behavior as part of the fix for CVE-2007-2138.
>> You'd need either SECURITY DEFINER functions or very careless use of
>> SET ROLE/SET SESSION AUTHORIZATION for the issue to be exploitable.
> Would it be practical to let foo() potentially mean pg_temp.foo()
> outside of any SECURITY DEFINER context?
Doesn't seem like a particularly good idea for the search semantics
to be randomly different inside a SECURITY DEFINER function. In fact,
I'll bet you could construct an attack in the reverse direction:
S.D. function thinks it is calling a temp function (using syntax that
works fine when not S.D.), but control gets sent to a non-temp function
belonging to $badguy instead.
regards, tom lane
On Sat, Nov 6, 2010 at 1:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Sat, Nov 6, 2010 at 11:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Yeah, we changed that behavior as part of the fix for CVE-2007-2138. >>> You'd need either SECURITY DEFINER functions or very careless use of >>> SET ROLE/SET SESSION AUTHORIZATION for the issue to be exploitable. > >> Would it be practical to let foo() potentially mean pg_temp.foo() >> outside of any SECURITY DEFINER context? > > Doesn't seem like a particularly good idea for the search semantics > to be randomly different inside a SECURITY DEFINER function. In fact, > I'll bet you could construct an attack in the reverse direction: > S.D. function thinks it is calling a temp function (using syntax that > works fine when not S.D.), but control gets sent to a non-temp function > belonging to $badguy instead. I guess. If you search pg_temp always then it's pretty much impossible to avoid having a security hole, if you use any non-trivial SQL. But if you search pg_temp for non-SD only then you'll only have a security hole if you assume (presumably without testing) that the behavior is the same in that case. If an SD function is calling temporary functions they'd best be ones it created, otherwise your security is pretty much nonexistent anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> I guess. If you search pg_temp always then it's pretty much
> impossible to avoid having a security hole, if you use any non-trivial
> SQL. But if you search pg_temp for non-SD only then you'll only have
> a security hole if you assume (presumably without testing) that the
> behavior is the same in that case. If an SD function is calling
> temporary functions they'd best be ones it created, otherwise your
> security is pretty much nonexistent anyway.
In general I don't see a lot of use for calling temp functions that
you don't know are temp functions. So I see nothing much wrong with
having to use the pg_temp. prefix --- and the possibility of security
issues definitely pushes me over the line to being happy with requiring
that.
regards, tom lane