From: "Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com>
To: Fabio Pardi <f.pardi@portavita.eu>, "pgsql-admin@lists.postgresql.org"
	<pgsql-admin@lists.postgresql.org>
Subject: RE: Function search_path
Thread-Topic: Function search_path
Thread-Index: AdO7x5WpnFdOXZ+3TX2aPXuhH1G0VQAmi7sAAEIC53A=
Date: Fri, 16 Mar 2018 21:09:52 +0000
Message-ID: <b0078bdae3e04de1ba3ddaa3527c8922@THALASSA.omni.imsweb.com>
References: <db194d98f61d4aa398c1399a24326e7c@THALASSA.omni.imsweb.com>
 <5f698c34-d01d-3196-1db4-75ba8f6a37d3@portavita.eu>
In-Reply-To: <5f698c34-d01d-3196-1db4-75ba8f6a37d3@portavita.eu>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

I have played around with the postgres memory settings and setting search_p=
ath on a function causes a lot more memory to be used than if no search_pat=
h was set.

Has anyone hit issues with system users not being able to autoanalyze or au=
tovacuum tables that have functional indices because of the search_path cha=
nge?

Thanks,
Manfred

>-----Original Message-----
>From: Fabio Pardi [mailto:f.pardi@portavita.eu]
>Sent: Thursday, March 15, 2018 5:30 AM
>To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; pgsql-admin@lists.po=
stgresql.org
>Subject: Re: Function search_path
>
>Hi Manfred,
>
>
>While at the moment I m not aware of other options (I m right now studying=
 CVE-2018-1058 and the impact on the installations I maintain), I would foc=
us on the 'out of memory' error.
>
>If Postgres is correctly configured, no action you perform on the database=
 should result in 'out of memory' errors.
>
>Is it maybe the case to review your memory settings to avoid such conditio=
ns?
>
>
>Regards,
>
>Fabio
>
>
>On 03/14/2018 08:18 PM, Heinemann, Manfred (IMS) wrote:
>> We recently started upgrading to Postgres 10.3 and have run into trouble=
 with the security change to search_path.
>>
>>
>>
>> The release notes say:
>>
>> In cases where user-provided functions are indirectly executed by these =
programs - for example, user-provided functions in index expressions - the =
tighter |search_path| may result in errors, which will need to be corrected=
 by adjusting those user-provided functions to not assume anything about wh=
at search path they are invoked under. That has always been good practice, =
but now it will be necessary for correct behavior. (CVE-2018-1058)
>>
>>
>>
>> We have this issue with a custom function that calls other custom functi=
ons and that function is used in a functional index. Now autoanalyze and au=
tovacuum fail on tables with those indices.
>>
>>
>>
>> The simplest fix seemed to be to set the functions search_path to '$user=
' but that has caused large updates to the indexed table to run out of memo=
ry.
>>
>>
>>
>> Hardcoding the schema in the function would mean having to have separate=
 functions for each schema affected. It also seems to make the function les=
s memory efficient.
>>
>>
>>
>> Are there other options?
>>
>>
>>
>> Thanks,
>>
>> Manfred
>>
>>
>> ------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------
>>
>> Information in this e-mail may be confidential. It is intended only for =
the addressee(s) identified above. If you are not the addressee(s), or an e=
mployee or agent of the addressee(s), please note that any dissemination, d=
istribution, or copying of this communication is strictly prohibited. If yo=
u have received this e-mail in error, please notify the sender of the error=
.

________________________________

Information in this e-mail may be confidential. It is intended only for the=
 addressee(s) identified above. If you are not the addressee(s), or an empl=
oyee or agent of the addressee(s), please note that any dissemination, dist=
ribution, or copying of this communication is strictly prohibited. If you h=
ave received this e-mail in error, please notify the sender of the error.