pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction search_path
7+ messages / 3 participants
[nested] [flat]
* Function search_path
@ 2018-03-14 19:18 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-03-14 19:18 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 what 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 functions and that function is used in a functional index. 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 indexed table to run out of memory.
Hardcoding the schema in the function would mean having to have separate functions for each schema affected. It also seems to 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 are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function search_path
@ 2018-03-15 09:30 Fabio Pardi <f.pardi@portavita.eu>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Fabio Pardi @ 2018-03-15 09:30 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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 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 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 what 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 functions and that function is used in a functional index. 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 indexed table to run out of memory.
>
>
>
> Hardcoding the schema in the function would mean having to have separate functions for each schema affected. It also seems to 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 are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Function search_path
@ 2018-03-16 21:09 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
parent: Fabio Pardi <f.pardi@portavita.eu>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-03-16 21:09 UTC (permalink / raw)
To: Fabio Pardi <f.pardi@portavita.eu>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
I have played around with the postgres memory settings and setting search_path on a function causes a lot more memory to be 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 because of 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 installations I 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 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 what 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 functions and that function is used in a functional index. 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 indexed table to run out of memory.
>>
>>
>>
>> Hardcoding the schema in the function would mean having to have separate functions for each schema affected. It also seems to 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 are not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you 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 employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function search_path
@ 2018-03-16 21:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-03-16 21:45 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; +Cc: Fabio Pardi <f.pardi@portavita.eu>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
> I have played around with the postgres memory settings and setting search_path on a function causes a lot more memory to be used than if no search_path was set.
That's a pretty broad claim with a pretty small amount of evidence
offered.
I can certainly believe that attaching a SET clause (whether for
search_path or any other GUC variable) would have an efficiency impact;
one non-obvious example is that it prevents inlining if the function is
a SQL function. But I don't immediately see a reason for major memory
consumption from that. I suspect what you're seeing is specific to a
particular use-case. If you were to provide a concrete example, we
could look into what's happening.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Function search_path
@ 2018-03-22 19:30 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-03-22 19:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
>> I have played around with the postgres memory settings and setting search_path on a function causes a lot more memory to be used than if no search_path was set.
>
>That's a pretty broad claim with a pretty small amount of evidence offered.
>
>I can certainly believe that attaching a SET clause (whether for search_path or any other GUC variable) would have an efficiency impact; one non-obvious example is that it prevents inlining if the function is a SQL function. But I don't immediately see a reason for major memory consumption from that. I suspect what you're seeing is specific to a particular use-case. If you were to provide a concrete example, we could look into what's happening.
>
>regards, tom lane
Here is an example where I can show significant extra memory consumption when setting search_path on a function:
CREATE TABLE test_search_path(date_last_modified timestamp, last_name varchar);
--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNAME'
INSERT INTO test_search_path(date_last_modified, last_name) VALUES(clock_timestamp(), 'SURNAME');
CREATE OR REPLACE FUNCTION clean_name_upper(original_name varchar)
RETURNS varchar
LANGUAGE plpgsql
AS $$
BEGIN
RETURN trim(upper(original_name));
END;
$$ IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION split_name_on_hyphen(original_name character varying)
RETURNS text[]
LANGUAGE plpgsql
AS $$
BEGIN
RETURN string_to_array(clean_name_upper(original_name), '-');
END;
$$ IMMUTABLE STRICT SET search_path = '$user';
CREATE INDEX idx_test_search_path_clean_name_upper_last_name ON test_search_path (clean_name_upper(last_name));
CREATE INDEX idx_test_search_path_split_name_on_hyphen_last_name ON test_search_path USING gin(split_name_on_hyphen(last_name));
UPDATE test_search_path SET date_last_modified = (date_last_modified - interval '7 days');
This only seems to be an issue when indices exist for both functions and when the inner called function does not have search_path set.
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 employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Function search_path
@ 2018-03-25 17:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2018-03-25 17:13 UTC (permalink / raw)
To: Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
"Heinemann, Manfred (IMS)" <HeinemannM@imsweb.com> writes:
> Here is an example where I can show significant extra memory consumption when setting search_path on a function:
I got around to testing this example today, and I don't see what you're
seeing --- the memory consumption seems stable, and about the same with
or without the "SET search_path" clause.
You didn't specify exactly how to do this bit:
--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNAME'
so I did it like this:
INSERT INTO test_search_path(date_last_modified, last_name)
select clock_timestamp(), (random()*1000)::int::text
from generate_series(1,1000000);
The example doesn't seem like it'd be terribly data-dependent, but
maybe that's wrong?
Also, I assume you're running a reasonably up-to-date PG release,
otherwise I'd be wondering about leaks in the GIN index AM; we've
fixed some issues of that sort in GIN bulk updates in the past.
But again, it's not clear what the connection to a function SET
clause would be. So I lack a plausible theory at the moment.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Function search_path
@ 2018-03-29 19:57 Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Heinemann, Manfred (IMS) @ 2018-03-29 19:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
>> Here is an example where I can show significant extra memory consumption when setting search_path on a function:
>
>I got around to testing this example today, and I don't see what you're seeing --- the memory consumption seems stable, and about the same with or without the "SET search_path" clause.
>
>You didn't specify exactly how to do this bit:
>
>--populate 1,000,000 rows with random values from 1,000 surnames for 'SURNAME'
>
>so I did it like this:
>
>INSERT INTO test_search_path(date_last_modified, last_name) select clock_timestamp(), (random()*1000)::int::text from generate_series(1,1000000);
>
>The example doesn't seem like it'd be terribly data-dependent, but maybe that's wrong?
>
>Also, I assume you're running a reasonably up-to-date PG release, otherwise I'd be wondering about leaks in the GIN index AM; we've fixed some issues of that sort in GIN bulk updates in the past.
>But again, it's not clear what the connection to a function SET clause would be. So I lack a plausible theory at the moment.
Tom,
Thanks for your looking at this.
I tested this well on 9.6.7 but I saw the same issue on 10.3.
You are correct the issue is not data dependant and I saw the same issues with the random numeric inserts you used.
On a server with a lot of memory(100GB) and using the postgres default memory settings, the update was able to complete but when watching top there was a clear spike in %MEM for the update when the function search_path was set.
The issue becomes more noticeable as you add more rows, for example at 5,000,000 %MEM went over 35. Without the search_path set %MEM stays real low and the update was a lot quicker.
Here is the more complete test case:
CREATE SCHEMA test_search_path AUTHORIZATION test_search_path;
--then logged in as the test_search_path user
CREATE TABLE test_search_path(date_last_modified timestamp, last_name varchar);
INSERT INTO test_search_path(date_last_modified, last_name) select clock_timestamp(), (random()*1000)::int::text from generate_series(1,1000000);
CREATE OR REPLACE FUNCTION clean_name_upper(original_name varchar)
RETURNS varchar
LANGUAGE plpgsql
AS $$
BEGIN
RETURN trim(upper(original_name));
END;
$$ IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION split_name_on_hyphen(original_name character varying)
RETURNS text[]
LANGUAGE plpgsql
AS $$
BEGIN
RETURN string_to_array(clean_name_upper(original_name), '-');
END;
$$ IMMUTABLE STRICT SET search_path = '$user';
CREATE INDEX idx_test_search_path_clean_name_upper_last_name ON test_search_path (clean_name_upper(last_name));
CREATE INDEX idx_test_search_path_split_name_on_hyphen_last_name ON test_search_path USING gin(split_name_on_hyphen(last_name));
UPDATE test_search_path SET date_last_modified = (date_last_modified - interval '7 days');
There seems to be something about the inner function (clean_name_upper) being used in an index of its own on the same field as the GIN functional index (split_name_on_hyphen) that is causing this.
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 employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-03-29 19:57 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-14 19:18 Function search_path Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-03-15 09:30 ` Fabio Pardi <f.pardi@portavita.eu>
2018-03-16 21:09 ` Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-03-16 21:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-22 19:30 ` Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
2018-03-25 17:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-03-29 19:57 ` Heinemann, Manfred (IMS) <HeinemannM@imsweb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox