RE: Function search_path - Mailing list pgsql-admin

I have played around with the postgres memory settings and setting search_path on a function causes a lot more memory
tobe used than if no search_path was set. 

Has anyone hit issues with system users not being able to autoanalyze or autovacuum tables that have functional indices
becauseof the search_path change? 

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.postgresql.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
installationsI maintain), I would focus 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 conditions?
>
>
>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
functionsin index expressions - the tighter |search_path| may result in errors, which will need to be corrected by
adjustingthose user-provided functions to not assume anything about what search path they are invoked under. That has
alwaysbeen 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 functions and that function is used in a
functionalindex. Now autoanalyze and autovacuum 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
indexedtable to run out of memory. 
>>
>>
>>
>> Hardcoding the schema in the function would mean having to have separate functions for each schema affected. It also
seemsto make the function less 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
arenot the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution,
orcopying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error. 

________________________________

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error.