[Bug] create extension gp_debug_numsegments changes current search_path

[tuhaihe]

Cloudberry Database version

Greenplum version 7

What happened

when creating extension gp_debug_numsegments changes current search_path, drop currrent search_path.

What you think should happen instead

Should not drop current search_path.

How to reproduce

gpadmin=# show search_path ;
   search_path
-----------------
 "$user", public
(1 row)

gpadmin=# create schema xmen;
CREATE SCHEMA
gpadmin=# set search_path to xmen;
SET
gpadmin=# show search_path ;
 search_path
-------------
 xmen
(1 row)

gpadmin=# CREATE EXTENSION IF NOT EXISTS gp_debug_numsegments;
CREATE EXTENSION
gpadmin=# show search_path ;
 search_path
-------------
 public
(1 row)

See https://github.com/greenplum-db/gpdb/issues/15912

Operating System

Linux

Anything else

No response

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[Ray-Eldath]

This issue seems have nothing to do with the actual extension being installed. Using gp_sparse_vector can lead to the same issue:

My guess here is that a extension will cause this if they are relocatable (relocatable = true) in their .control file but using SET search_path = xxx in their script (<name>--<version>.sql file). Changes:

Index: src/backend/commands/extension.c
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
diff --git a/src/backend/commands/extension.c b/src/backend/commands/extension.c
--- a/src/backend/commands/extension.c	(revision c9dbca2e5beaaf9854abaebcfd600aa77780f40b)
+++ b/src/backend/commands/extension.c	(date 1690454308496)
@@ -1121,7 +1121,7 @@
 		/*
 		 * Restore the GUC variables we set above.
 		 */
-		AtEOXact_GUC(true, save_nestlevel);
+		AtEOXact_GUC(false, save_nestlevel);
 	}
 	PG_END_TRY();

can surely fix the issue by pretending it's at a transaction abort (instead of a commit) to force prior values restoration, but changes to Postgres kernel are unwanted. It's still unknown whether a extension: i. states itself "relocatable" in control file; ii. set search_path w/o using @extschema@ is truly "relocatable" in any sensible manner.

[Ray-Eldath]

A normal extension who doesn't use SET search_path in its script file should install the objects to the environment search_path in which the CREATE EXTENSION were executed:

But these "malformed" extension are installing objects to wherever they want, regardless of the search_path of the env:

They're also unresponsive to the explicitly-given SCHEMA option:

It could be argued that these extensions are not well written in the first place.

[avamingli]

It could be argued that these extensions are not well written in the first place.

+1, and have a try with other PG/GP 's extension to verify?

[Ray-Eldath]

YES I've tried. both gp_debug_numsegments and gp_sparse_vector show the same "problematic" behavior:

These two extensions use SET search_path = xxx w/o restoring them. It's actually recommended as the best practice to temporarily set search_path to pg_catalog, pg_temp to guard against "search-path-based attacks", and many extensions indeed do the trick:

I suggest the proposed fix to be just adding a "restoration to prior search_path" SQL statement to the problematic extensions, just as all these "normal" extensions. It's worth noting that these two extensions are not designed for ordinary user to use directly, so it's also make sense to not fix this behavior, just leave it as-is :-)

[avamingli]

Do pg extensions change user's search_path?

[Ray-Eldath]

Do pg extensions change user's search_path?

I've write a small script to test all extensions listed in pg_available_extensions. Following is the result:

import psycopg


def search_path(conn, path=''):
    if path:
        return conn.execute(f"SET search_path = {path}")
    else:
        return conn.execute("SHOW search_path").fetchone()


if __name__ == '__main__':
    with psycopg.connect("host=localhost dbname=postgres port=7000", autocommit=True) as conn:
        search_path(conn, 'ray')
        original = search_path(conn)
        assert original == ('ray',)
        for name in conn.execute("SELECT name FROM pg_available_extensions ORDER BY name;").fetchall():
            name = name[0]
            print(name, end=' ')
            try:
                conn.execute(f"CREATE EXTENSION {name} CASCADE")
            except psycopg.errors.DuplicateObject:
                print('Duplicated')
                continue
            except psycopg.errors.DatabaseError as e:
                print('Error', str(e).splitlines()[0])
                continue
            print('Pass' if search_path(conn) == original else 'FAILED')
            conn.execute(f"DROP EXTENSION {name} CASCADE")
            search_path(conn, 'ray')

amcheck Pass
autoinc Pass
bloom Pass
btree_gin Pass
btree_gist Pass
citext Pass
dblink Pass
dict_int Pass
dict_xsyn Pass
file_fdw Pass
fuzzystrmatch Pass
gp_debug_numsegments FAILED
gp_distribution_policy FAILED
gp_exttable_fdw Duplicated
gp_inject_fault Pass
gp_internal_tools FAILED
gp_legacy_string_agg Pass
gp_replica_check Pass
gp_sparse_vector FAILED
hstore Pass
hstore_plpython2u Error could not open extension control file "/home/gpadmin/install/cbdb/share/postgresql/extension/plpython2u.control": No such file or directory
hstore_plpython3u Pass
hstore_plpythonu Error could not open extension control file "/home/gpadmin/install/cbdb/share/postgresql/extension/plpythonu.control": No such file or directory
insert_username Pass
intagg Pass
intarray Pass
isn Pass
jsonb_plpython2u Error could not open extension control file "/home/gpadmin/install/cbdb/share/postgresql/extension/plpython2u.control": No such file or directory
jsonb_plpython3u Pass
jsonb_plpythonu Error could not open extension control file "/home/gpadmin/install/cbdb/share/postgresql/extension/plpythonu.control": No such file or directory
ltree Pass
ltree_plpython2u Error could not open extension control file "/home/gpadmin/install/cbdb/share/postgresql/extension/plpython2u.control": No such file or directory
ltree_plpython3u Pass
ltree_plpythonu Error could not open extension control file "/home/gpadmin/install/cbdb/share/postgresql/extension/plpythonu.control": No such file or directory
moddatetime Pass
old_snapshot Pass
pageinspect Pass
pg_buffercache Pass
pg_freespacemap Pass
pg_prewarm Pass
pg_stat_statements Pass
pg_surgery Pass
pg_trgm Pass
pg_visibility Pass
pgcrypto Pass
pgrowlocks Pass
pgstattuple Pass
plperl Pass
plperlu Pass
plpgsql Duplicated
plpython3u Duplicated
pxf_fdw Pass
refint Pass
seg Error permission denied: "pg_depend" is a system catalog
tablefunc Pass
tcn Pass
tsm_system_rows Pass
tsm_system_time Pass
unaccent Pass

seems gp_sparse_vector, gp_distribution_policy, gp_debug_numsegments and gp_internal_tools has failed on the test. Notably, GP extensions gp_exttable_fdw, gp_replica_check and gp_legacy_string_agg do pass the test.

So the answer is YES. all extensions that are not started with the gp_ prefix preserves the user's search_path whereas some of (not all of) GP extensions do not.