Opened 4 months ago

Last modified 3 months ago

#5125 new defect

Creating both topology and tiger extension in same session contend search_path

Reported by: strk Owned by: strk
Priority: medium Milestone: PostGIS 3.3.0
Component: build/upgrade/install Version: master
Keywords: Cc:

Description

See https://github.com/postgis/docker-postgis/issues/288

The problem is that ALTER DATABASE does not change the reset_val in pg_settings, which is used by extensions to fetch the "current" value of search_path:

test_issue288=# create extension postgis_tiger_geocoder;
CREATE EXTENSION
test_issue288=# select setting, boot_val, reset_val from pg_catalog.pg_settings WHERE name = 'search_path';
-[ RECORD 1 ]---------------------
setting   | "$user", public, tiger
boot_val  | "$user", public
reset_val | "$user", public

You can see the setting field is updated, but not the reset_val, which is instead only updated on next reconnection.

Change History (13)

comment:1 by strk, 4 months ago

Maybe we should completely STOP changing search_path and let users deal with that. What do you think Regina ? It might also help spot security issues in functions

comment:2 by strk, 4 months ago

Thanks to Myon on IRC I now know we could use this:

select setconfig[1] 
from pg_catalog.pg_db_role_setting 
where 
 setdatabase = (select oid from pg_database where datname = current_database() )
 and setrole = 0;

The above query will give the last value set via ALTER DATABASE w/out the need of reconnecting.

comment:3 by Sandro Santilli <strk@…>, 4 months ago

In 3dea38a/git:

Do not undo search_path changes done by pgis exts in single session

References #5125 in master branch (3.3.0dev)

comment:4 by strk, 4 months ago

I've implemented the above idea and pushed to master branch. Do you think it should be backported ?

comment:5 by robe, 4 months ago

sure

comment:6 by ImreSamu, 4 months ago

I am testing the master branch with my local updated docker

  • postgis/postgis:14-master
    • ENV POSTGIS_GIT_HASH 6a6cc54bdf1315da8e8c8401575c179aa8727daf
  • with the "old" and the new patched "initdb-postgis.sh"

And I receive this log with CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;

postgres=# CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
ERROR:  column "c" does not exist
LINE 10:  SELECT regexp_replace(c, '^search_path=', '')
                                ^
QUERY:  WITH settings AS (
		SELECT unnest(setconfig) config
		FROM pg_catalog.pg_db_role_setting
		WHERE setdatabase = (
			SELECT oid
			FROM pg_catalog.pg_database
			WHERE datname = current_database()
		) and setrole = 0
	)
	SELECT regexp_replace(c, '^search_path=', '')
	FROM settings WHERE c like 'search_path=%'
CONTEXT:  PL/pgSQL function postgis_extension_addtosearchpath(character varying) line 7 at SQL statement
postgres=# 

I have received the same docker-postgis CI/CD error log "Build docker image for 14-master variant default" ( with the new "initdb-postgis.sh" ) :

https://github.com/postgis/docker-postgis/runs/5883910585?check_suite_focus=true

And replicated with local docker environment: (with the new patched "initdb-postgis.sh")

git clone https://github.com/postgis/docker-postgis.git
cd docker-postgis
make update
make build-14-master
make test-14-master

comment:7 by robe, 4 months ago

Broke winnie too noted - here - #5126

comment:8 by Regina Obe <lr@…>, 4 months ago

In cbf5283/git:

Fix search path function. References #5125 and #5126 for PostGIS 3.3.0

comment:9 by ImreSamu, 4 months ago

confirmed;

The latest cbf5283/git patch fixed the postgis/postgis:14-master test.

Thanks!

comment:10 by strk, 4 months ago

Sorry for the problem, concerning that CI did not catch this. I guess adding run_test.pl support for tiger could help with CI. Time to backport ?

comment:13 by Regina Obe <lr@…>, 3 months ago

In 4df09520/git:

Change signature of AddToSearchPath, reference #5150 for PostGIS 3.2.2
Fix search path function, references #5125 for PostGIS 3.2.2

comment:14 by Regina Obe <lr@…>, 3 months ago

In b175c26/git:

Numerous fixes to helper functions.
References #5150
References #5125
References #5155 for PostGIS 3.1.6

Note: See TracTickets for help on using tickets.