[SCM] PostGIS branch stable-3.3 updated. 3.3.7-71-ge20d4da2f
git at osgeo.org
git at osgeo.org
Fri Feb 6 00:07:57 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.3 has been updated
via e20d4da2f119e193c195afe7087d14772b969b29 (commit)
from 05ba89e6e2a9cc3761ea8b21773a6d9fbc9acdd9 (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 e20d4da2f119e193c195afe7087d14772b969b29
Author: Regina Obe <lr at pcorp.us>
Date: Fri Feb 6 03:05:02 2026 -0500
TIGER EXTENSION SECURITY fix
- Ensure tables are created by tiger extension and bale if not.
Patch provided by Andrey Borodin (Yandex)
References #5998 for PostGIS 3.3.9
diff --git a/NEWS b/NEWS
index b23c7db50..b8e5e4ae8 100644
--- a/NEWS
+++ b/NEWS
@@ -29,7 +29,10 @@ Proj 4.9+ required.
- #6023, Fix robustness issue in ptarray_contains_point (Sandro Santilli)
- #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)
+ - GH-841, small memory leak in address_standardizer (Maxim Korotkov)
+ - #5998, [tiger_geocoder] [security] CVE-2022-2625, make sure tables required
+ by extension are owned by extension
+ authored: Andrey Borodin (Yandex), reported by Sergey Bobrov (Kaspersky)
PostGIS 3.3.8
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';
-----------------------------------------------------------------------
Summary of changes:
NEWS | 5 +-
.../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 ++++++++++++++++++----
13 files changed, 651 insertions(+), 133 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list