From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [ANNOUNCE] Advisory on possibly insecure security definer functions |
Date: | 2007-02-17 18:31:19 |
Message-ID: | 20070217183119.GA20760@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
On Sat, Feb 17, 2007 at 03:15:25PM +0100, Karsten Hilbert wrote:
> On Sat, Feb 17, 2007 at 01:26:34PM +0900, Tatsuo Ishii wrote:
> > But if we insert a set schema search_path command in an SQL function,
> > the caller will be affected by it. Doing reset search_path before
> > returning to caller might solve some of problems, but it will not
> > recover caller's special search_path. How do you solve the problem?
>
> Schema-qualifying object accesses would be tedious,
> omission-prone but not liable to the above problem.
If you schema-qualify objects instead of setting search_path then
don't forget about operators. A query like
SELECT col
FROM schemaname.tablename
WHERE othercol = schemaname.funcname(someval)
is vulnerable because the caller might have defined an = operator
for the appropriate data types and set search_path to find it before
the one in pg_catalog. To be safe you'd need to use
SELECT col
FROM schemaname.tablename
WHERE othercol operator(pg_catalog.=) schemaname.funcname(someval)
which is harder to read and, as Karsten mentioned, prone to omission.
Also, this query might still be vulnerable if funcname() isn't
carefully written.
A PL/pgSQL function could save and restore the caller's search_path
with something like
oldpath := pg_catalog.current_setting('search_path');
PERFORM pg_catalog.set_config('search_path', oldpath, false);
If the function raises an exception then search_path wouldn't be
reset unless you catch exceptions and reset the path in the
exception-handling code.
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Tester | 2007-02-17 18:44:20 | Re: Problem with index not always being used |
Previous Message | Tom Lane | 2007-02-17 18:23:38 | Re: Re: [GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions |
From | Date | Subject | |
---|---|---|---|
Next Message | Warren Turkal | 2007-02-17 18:40:44 | Re: RFC: Temporal Extensions for PostgreSQL |
Previous Message | Tom Lane | 2007-02-17 18:28:22 | Re: pg_restore fails with a custom backup file |