[SCM] PostGIS branch stable-3.5 updated. 3.5.4-20-g50e75f968
git at osgeo.org
git at osgeo.org
Thu Feb 5 23:50:36 PST 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.5 has been updated
via 50e75f9684758b1e1b9d8810395b1cbef3d18d60 (commit)
from 3ec6fdb951aaec20db3fc35ee274752eeb15d07c (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 50e75f9684758b1e1b9d8810395b1cbef3d18d60
Author: Regina Obe <lr at pcorp.us>
Date: Tue Oct 7 18:27:17 2025 -0400
TIGER EXTENSION SECURITY fix
- Ensure tables are created by tiger extension and bale if not.
Patch provided by Andrey Borodin (Yandex)
- Don't create tiger_data as that gets created on first load of data
and shouldn't be part of the extension
References #5998 for PostGIS 3.5.5
diff --git a/NEWS b/NEWS
index fc3dc793a..e2ad1cad2 100644
--- a/NEWS
+++ b/NEWS
@@ -12,7 +12,8 @@ PostgreSQL 12-18 required. GEOS 3.8+ required. Proj 6.1+ required.
- #6027, Fix RemoveUnusedPrimitives without topology in search_path (Sandro Santilli)
- #6028, crash indexing malformed empty polygon (Paul Ramsey)
- GH-841, small memory leak in address_standardizer (Maxim Korotkov)
-
+ - #5998, [tiger_geocoder] [security] CVE-2022-2625, make sure tables requires
+ by extension are owned by extension
PostGIS 3.5.4
2025/10/16
diff --git a/extras/tiger_geocoder/geocode_settings.sql b/extras/tiger_geocoder/geocode_settings.sql
index e126f825b..e5c09b8c2 100644
--- a/extras/tiger_geocoder/geocode_settings.sql
+++ b/extras/tiger_geocoder/geocode_settings.sql
@@ -22,10 +22,16 @@ BEGIN
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'geocode_settings') THEN
CREATE TABLE geocode_settings(name text primary key, setting text, unit text, category text, short_desc text);
GRANT SELECT ON geocode_settings 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 geocode_settings(name text primary key, setting text, unit text, category text, short_desc text);
END IF;
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = 'geocode_settings_default') THEN
CREATE TABLE geocode_settings_default(name text primary key, setting text, unit text, category text, short_desc text);
GRANT SELECT ON geocode_settings_default TO public;
+ ELSE
+ -- Same as above.
+ CREATE TABLE IF NOT EXISTS geocode_settings_default(name text primary key, setting text, unit text, category text, short_desc text);
END IF;
--recreate defaults
TRUNCATE TABLE geocode_settings_default;
diff --git a/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql b/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
index ff78fc8f8..da6fc91a9 100644
--- a/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
+++ b/extras/tiger_geocoder/pagc_normalize/pagc_tables.sql
@@ -11,14 +11,23 @@ BEGIN
IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_schema = 'tiger' AND table_name = '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
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
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
-- its probably old table structure change type of lex and gaz columns
diff --git a/extras/tiger_geocoder/tiger_loader_2012.sql b/extras/tiger_geocoder/tiger_loader_2012.sql
index c4d6da1b7..00593ecf5 100644
--- a/extras/tiger_geocoder/tiger_loader_2012.sql
+++ b/extras/tiger_geocoder/tiger_loader_2012.sql
@@ -57,18 +57,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -125,9 +121,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -138,8 +132,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -149,7 +142,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2013.sql b/extras/tiger_geocoder/tiger_loader_2013.sql
index d42f60ea2..8b6e0d166 100644
--- a/extras/tiger_geocoder/tiger_loader_2013.sql
+++ b/extras/tiger_geocoder/tiger_loader_2013.sql
@@ -56,18 +56,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -124,9 +120,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -137,8 +131,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -148,7 +141,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2014.sql b/extras/tiger_geocoder/tiger_loader_2014.sql
index bedb84051..8726e5afe 100644
--- a/extras/tiger_geocoder/tiger_loader_2014.sql
+++ b/extras/tiger_geocoder/tiger_loader_2014.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -212,9 +274,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -225,8 +285,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -236,7 +295,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2015.sql b/extras/tiger_geocoder/tiger_loader_2015.sql
index fe0bdf62f..3311be2e7 100644
--- a/extras/tiger_geocoder/tiger_loader_2015.sql
+++ b/extras/tiger_geocoder/tiger_loader_2015.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -212,9 +274,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -225,8 +285,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -236,7 +295,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2016.sql b/extras/tiger_geocoder/tiger_loader_2016.sql
index 0cb93e442..39dd37f0a 100644
--- a/extras/tiger_geocoder/tiger_loader_2016.sql
+++ b/extras/tiger_geocoder/tiger_loader_2016.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2017.sql b/extras/tiger_geocoder/tiger_loader_2017.sql
index 9a7c6a726..5644a4d53 100644
--- a/extras/tiger_geocoder/tiger_loader_2017.sql
+++ b/extras/tiger_geocoder/tiger_loader_2017.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2018.sql b/extras/tiger_geocoder/tiger_loader_2018.sql
index 61e68797a..33697e785 100644
--- a/extras/tiger_geocoder/tiger_loader_2018.sql
+++ b/extras/tiger_geocoder/tiger_loader_2018.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2019.sql b/extras/tiger_geocoder/tiger_loader_2019.sql
index 12a395b60..9485011cf 100644
--- a/extras/tiger_geocoder/tiger_loader_2019.sql
+++ b/extras/tiger_geocoder/tiger_loader_2019.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -144,18 +210,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -211,9 +273,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -224,8 +284,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -235,7 +294,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2020.sql b/extras/tiger_geocoder/tiger_loader_2020.sql
index 6afce151a..01c81f26a 100644
--- a/extras/tiger_geocoder/tiger_loader_2020.sql
+++ b/extras/tiger_geocoder/tiger_loader_2020.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -165,18 +231,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -232,9 +294,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -245,8 +305,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -256,7 +315,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2021.sql b/extras/tiger_geocoder/tiger_loader_2021.sql
index 0cdd7ee5a..675bff9cd 100644
--- a/extras/tiger_geocoder/tiger_loader_2021.sql
+++ b/extras/tiger_geocoder/tiger_loader_2021.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -172,18 +238,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -239,9 +301,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -252,8 +312,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -263,7 +322,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2022.sql b/extras/tiger_geocoder/tiger_loader_2022.sql
index af4b9ee25..991ce2255 100644
--- a/extras/tiger_geocoder/tiger_loader_2022.sql
+++ b/extras/tiger_geocoder/tiger_loader_2022.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -175,18 +241,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -242,9 +304,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -255,8 +315,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -266,7 +325,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2023.sql b/extras/tiger_geocoder/tiger_loader_2023.sql
index 691176236..65c497ce2 100644
--- a/extras/tiger_geocoder/tiger_loader_2023.sql
+++ b/extras/tiger_geocoder/tiger_loader_2023.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -175,18 +241,14 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE SCHEMA IF NOT EXISTS tiger_data;
END
$$ LANGUAGE 'plpgsql';
@@ -242,9 +304,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -255,8 +315,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -266,7 +325,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
diff --git a/extras/tiger_geocoder/tiger_loader_2024.sql b/extras/tiger_geocoder/tiger_loader_2024.sql
index 962d50f58..5e7454ffd 100644
--- a/extras/tiger_geocoder/tiger_loader_2024.sql
+++ b/extras/tiger_geocoder/tiger_loader_2024.sql
@@ -89,6 +89,72 @@ IF NOT EXISTS(SELECT table_name FROM information_schema.columns WHERE table_sche
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
);
COMMENT ON TABLE tiger.bg IS 'block groups';
+ELSE
+ -- Insist on invoking Postgres logic of owning table by extension. This prevent attacks like CVE-2022-2625.
+ CREATE TABLE IF NOT EXISTS tiger.tract
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ tract_id varchar(11) PRIMARY KEY,
+ name varchar(7),
+ namelsad varchar(20),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tiger.tabblock
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blockce varchar(4),
+ tabblock_id varchar(16) PRIMARY KEY,
+ name varchar(20),
+ mtfcc varchar(5),
+ ur varchar(1),
+ uace varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
+
+ CREATE TABLE IF NOT EXISTS tiger.bg
+ (
+ gid serial NOT NULL,
+ statefp varchar(2),
+ countyfp varchar(3),
+ tractce varchar(6),
+ blkgrpce varchar(1),
+ bg_id varchar(12) PRIMARY KEY,
+ namelsad varchar(13),
+ mtfcc varchar(5),
+ funcstat varchar(1),
+ aland double precision,
+ awater double precision,
+ intptlat varchar(11),
+ intptlon varchar(12),
+ the_geom geometry,
+ CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
+ CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
+ CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
+ );
END IF;
IF EXISTS(SELECT * FROM information_schema.columns WHERE table_schema = 'tiger' AND column_name = 'tabblock_id' AND table_name = 'tabblock' AND character_maximum_length < 16) THEN -- size of name and tabblock_id fields need to be increased
@@ -175,18 +241,7 @@ $$
DO
$$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_platform' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
- END IF;
-END
-$$ LANGUAGE 'plpgsql';
-
-DO
-$$
-BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE schema_name = 'tiger_data') THEN
- CREATE SCHEMA tiger_data;
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_platform(os varchar(50) PRIMARY KEY, declare_sect text, pgbin text, wget text, unzip_command text, psql text, path_sep text, loader text, environ_set_command text, county_process_command text);
END
$$ LANGUAGE 'plpgsql';
@@ -242,9 +297,7 @@ done');
-- variables table
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_variables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
- END IF;
+ CREATE TABLE IF NOT EXISTS loader_variables(tiger_year varchar(4) PRIMARY KEY, website_root text, staging_fold text, data_schema text, staging_schema text);
END
$$ LANGUAGE 'plpgsql';
@@ -255,8 +308,7 @@ GRANT SELECT ON TABLE loader_variables TO public;
DO $$
BEGIN
- IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name = 'loader_lookuptables' AND table_schema = 'tiger') THEN
- CREATE TABLE loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
+ CREATE TABLE IF NOT EXISTS loader_lookuptables(process_order integer NOT NULL DEFAULT 1000,
lookup_name text primary key,
table_name text, single_mode boolean NOT NULL DEFAULT true,
load boolean NOT NULL DEFAULT true,
@@ -266,7 +318,6 @@ BEGIN
post_load_process text, single_geom_mode boolean DEFAULT false,
insert_mode char(1) NOT NULL DEFAULT 'c',
pre_load_process text,columns_exclude text[], website_root_override text);
- END IF;
END
$$ LANGUAGE 'plpgsql';
-----------------------------------------------------------------------
Summary of changes:
NEWS | 3 +-
extras/tiger_geocoder/geocode_settings.sql | 6 ++
.../tiger_geocoder/pagc_normalize/pagc_tables.sql | 9 +++
extras/tiger_geocoder/tiger_loader_2012.sql | 16 +---
extras/tiger_geocoder/tiger_loader_2013.sql | 16 +---
extras/tiger_geocoder/tiger_loader_2014.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2015.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2016.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2017.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2018.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2019.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2020.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2021.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2022.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2023.sql | 82 +++++++++++++++++---
extras/tiger_geocoder/tiger_loader_2024.sql | 87 +++++++++++++++++-----
16 files changed, 794 insertions(+), 163 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list