[SCM] PostGIS branch stable-3.2 updated. 3.2.10-4-g94610531f
git at osgeo.org
git at osgeo.org
Tue Jun 2 16:13:33 PDT 2026
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, stable-3.2 has been updated
via 94610531f03c30c733853704569ba1bd963151d8 (commit)
from 32b6f6810ab57fa27bbe3e3f9d3bcb1938e29b4d (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit 94610531f03c30c733853704569ba1bd963151d8
Author: Paul Ramsey <pramsey at cleverelephant.ca>
Date: Tue Jun 2 16:13:25 2026 -0700
Prefix operators to avoid extension operator injection
diff --git a/extras/tiger_geocoder/geocode/other_helper_functions.sql b/extras/tiger_geocoder/geocode/other_helper_functions.sql
index c7303e7be..c8cf91cec 100644
--- a/extras/tiger_geocoder/geocode/other_helper_functions.sql
+++ b/extras/tiger_geocoder/geocode/other_helper_functions.sql
@@ -63,7 +63,7 @@ SELECT array_to_string(ARRAY(
-- create unique index on faces for tfid seems to perform better --
SELECT 'CREATE UNIQUE INDEX uidx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(' || c.column_name || ');' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE') As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE') As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('tfid') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -74,7 +74,7 @@ UNION ALL
-- basic btree regular indexes
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(' || c.column_name || ');' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE') As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE') As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('countyfp', 'tlid', 'tfidl', 'tfidr', 'tfid', 'zip', 'placefp', 'cousubfp') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -85,7 +85,7 @@ WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data') AND (NOT
UNION ALL
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || '_gist ON ' || c.table_schema || '.' || c.table_name || ' USING gist(' || c.column_name || ');' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE') As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE') As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('the_geom', 'geom') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -96,7 +96,7 @@ WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data')
UNION ALL
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_snd_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(soundex(' || c.column_name || '));' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE') As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE') As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('name', 'place', 'city') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -109,7 +109,7 @@ WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data')
UNION ALL
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_lower_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(lower(' || c.column_name || '));' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE') As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE') As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('name', 'place', 'city') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -121,7 +121,7 @@ WHERE i.tablename IS NULL AND c.table_schema IN('tiger','tiger_data')
UNION ALL
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_least_address' || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(least_hn(fromhn, tohn));' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name LIKE '%addr' AND table_schema IN('tiger','tiger_data')) As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE' AND table_name LIKE '%addr' AND table_schema IN('tiger','tiger_data')) As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('fromhn') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -132,7 +132,7 @@ WHERE i.tablename IS NULL
UNION ALL
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_l' || c.column_name || '_var_ops' || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(lower(' || c.column_name || ') varchar_pattern_ops);' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE' AND (table_name LIKE '%featnames' or table_name LIKE '%place' or table_name LIKE '%zip_lookup_base' or table_name LIKE '%zip_state_loc') AND table_schema IN('tiger','tiger_data')) As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE' AND (table_name LIKE '%featnames' or table_name LIKE '%place' or table_name LIKE '%zip_lookup_base' or table_name LIKE '%zip_state_loc') AND table_schema IN('tiger','tiger_data')) As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('name', 'city', 'place', 'fullname') ) AS c
ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
@@ -154,11 +154,11 @@ WHERE i.tablename IS NULL **/
UNION ALL
SELECT 'CREATE INDEX idx_' || c.table_schema || '_' || c.table_name || '_' || c.column_name || ' ON ' || c.table_schema || '.' || c.table_name || ' USING btree(' || c.column_name || ' );' As index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE' AND table_name LIKE '%edges' AND table_schema IN('tiger','tiger_data')) As t INNER JOIN
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE' AND table_name LIKE '%edges' AND table_schema IN('tiger','tiger_data')) As t INNER JOIN
(SELECT * FROM information_schema.columns WHERE column_name IN('zipl', 'zipr') ) AS c
- ON (t.table_name = c.table_name AND t.table_schema = c.table_schema)
+ ON (t.table_name OPERATOR(pg_catalog.=) c.table_name AND t.table_schema OPERATOR(pg_catalog.=) c.table_schema)
LEFT JOIN pg_catalog.pg_indexes i ON
- (i.tablename = c.table_name AND i.schemaname = c.table_schema
+ (i.tablename OPERATOR(pg_catalog.=) c.table_name AND i.schemaname OPERATOR(pg_catalog.=) c.table_schema
AND indexdef LIKE '%btree%(' || c.column_name || '%)%')
WHERE i.tablename IS NULL
@@ -226,7 +226,7 @@ DROP TABLE dup;
CREATE INDEX idx_' || t.table_schema || '_' || t.table_name || '_tlid ' || ' ON ' || t.table_schema || '.' || t.table_name || ' USING btree(tlid);
' As drop_sql_create_index
FROM (SELECT table_name, table_schema FROM
- information_schema.tables WHERE table_type = 'BASE TABLE' AND (table_name LIKE '%featnames' ) AND table_schema IN('tiger','tiger_data')) As t
+ information_schema.tables WHERE table_type OPERATOR(pg_catalog.=) 'BASE TABLE' AND (table_name LIKE '%featnames' ) AND table_schema IN('tiger','tiger_data')) As t
LEFT JOIN pg_catalog.pg_indexes i ON
(i.tablename = t.table_name AND i.schemaname = t.table_schema
AND indexdef LIKE '%btree%(%tlid%')
diff --git a/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql b/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
index da6fc91a9..9f01969bc 100644
--- a/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
+++ b/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
@@ -8,35 +8,35 @@ $$
DECLARE var_temp text;
BEGIN
var_temp := tiger.SetSearchPathForInstall('tiger'); /** set set search path to have tiger in front **/
- IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_gaz') THEN
+ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema OPERATOR(pg_catalog.=) 'tiger' AND table_name OPERATOR(pg_catalog.=) 'pagc_gaz') THEN
CREATE TABLE pagc_gaz (id serial NOT NULL primary key ,seq integer ,word text, stdword text, token integer,is_custom boolean NOT NULL default true);
GRANT SELECT ON pagc_gaz TO public;
ELSE
-- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
CREATE TABLE IF NOT EXISTS pagc_gaz (id serial NOT NULL primary key ,seq integer ,word text, stdword text, token integer,is_custom boolean NOT NULL default true);
END IF;
- IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_lex') THEN
+ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema OPERATOR(pg_catalog.=) 'tiger' AND table_name OPERATOR(pg_catalog.=) 'pagc_lex') THEN
CREATE TABLE pagc_lex (id serial NOT NULL primary key,seq integer,word text,stdword text,token integer,is_custom boolean NOT NULL default true);
GRANT SELECT ON pagc_lex TO public;
ELSE
-- Same as above.
CREATE TABLE IF NOT EXISTS pagc_lex (id serial NOT NULL primary key,seq integer,word text,stdword text,token integer,is_custom boolean NOT NULL default true);
END IF;
- IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_rules') THEN
+ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema OPERATOR(pg_catalog.=) 'tiger' AND table_name OPERATOR(pg_catalog.=) 'pagc_rules') THEN
CREATE TABLE pagc_rules (id serial NOT NULL primary key,rule text, is_custom boolean DEFAULT true);
GRANT SELECT ON pagc_rules TO public;
ELSE
-- Same as above.
CREATE TABLE IF NOT EXISTS pagc_rules (id serial NOT NULL primary key,rule text, is_custom boolean DEFAULT true);
END IF;
- IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_gaz' AND data_type='text') THEN
+ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema OPERATOR(pg_catalog.=) 'tiger' AND table_name OPERATOR(pg_catalog.=) 'pagc_gaz' AND data_type OPERATOR(pg_catalog.=) 'text') THEN
-- its probably old table structure change type of lex and gaz columns
ALTER TABLE tiger.pagc_lex ALTER COLUMN word TYPE text;
ALTER TABLE tiger.pagc_lex ALTER COLUMN stdword TYPE text;
ALTER TABLE tiger.pagc_gaz ALTER COLUMN word TYPE text;
ALTER TABLE tiger.pagc_gaz ALTER COLUMN stdword TYPE text;
END IF;
- IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'pagc_rules' AND column_name = 'is_custom' ) THEN
+ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema OPERATOR(pg_catalog.=) 'tiger' AND table_name OPERATOR(pg_catalog.=) 'pagc_rules' AND column_name OPERATOR(pg_catalog.=) 'is_custom' ) THEN
-- its probably old table structure add column
ALTER TABLE tiger.pagc_rules ADD COLUMN is_custom boolean NOT NULL DEFAULT false;
END IF;
@@ -8193,4 +8193,4 @@ UPDATE tiger.pagc_rules SET is_custom = false where id < 10000;
-- after insert we need to set back to true so all
-- user inputs are treated as custom
ALTER TABLE tiger.pagc_rules ALTER COLUMN is_custom SET DEFAULT true;
-SELECT pg_catalog.setval('pagc_rules_id_seq', 10000, true);
\ No newline at end of file
+SELECT pg_catalog.setval('pagc_rules_id_seq', 10000, true);
-----------------------------------------------------------------------
Summary of changes:
.../geocode/other_helper_functions.sql | 22 +++++++++++-----------
.../tiger_geocoder/pagc_normalize/pagc_tables.sql | 12 ++++++------
2 files changed, 17 insertions(+), 17 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list