[SCM] PostGIS branch stable-3.2 updated. 3.2.7-62-gd19a022df
git at osgeo.org
git at osgeo.org
Fri Feb 6 00:11:25 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.2 has been updated
via d19a022dfbf9f32ef8095b19dc4036892ecc3683 (commit)
from 1eee9c10d84fcddbe737692f34fa1ab80dc84771 (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 d19a022dfbf9f32ef8095b19dc4036892ecc3683
Author: Regina Obe <lr at pcorp.us>
Date: Fri Feb 6 03:10:41 2026 -0500
TIGER EXTENSION SECURITY fix
- Ensure tables are created by tiger extension and bale if not.
Patch provided by Andrey Borodin (Yandex)
Closes #5998 for PostGIS 3.2.9
diff --git a/NEWS b/NEWS
index 8b5dbf815..e1b54c1e7 100644
--- a/NEWS
+++ b/NEWS
@@ -26,7 +26,9 @@ Proj 4.9+ required.
- #5989, CurvePolygon distance error (Paul Ramsey)
- #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 required
+ by extension are owned by extension
+ authored: Andrey Borodin (Yandex), reported by Sergey Bobrov (Kaspersky)
PostGIS 3.2.8
2024/12/22
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 2343fe15c..01c81f26a 100644
--- a/extras/tiger_geocoder/tiger_loader_2020.sql
+++ b/extras/tiger_geocoder/tiger_loader_2020.sql
@@ -2,7 +2,7 @@
-- PostGIS - Spatial Types for PostgreSQL
-- http://postgis.net
--
--- Copyright (C) 2012-2021 Regina Obe and Leo Hsu
+-- Copyright (C) 2012-2018 Regina Obe and Leo Hsu
-- Paragon Corporation
--
-- This is free software; you can redistribute and/or modify it under
@@ -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';
@@ -275,8 +333,9 @@ COMMENT ON COLUMN loader_lookuptables.website_root_override IS 'Path to use for
INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, single_geom_mode, insert_mode, pre_load_process, post_load_process, columns_exclude )
VALUES(10, 'tract', 'tract', true, false, true,false, 'c',
'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (tract_id) ) INHERITS(tiger.${lookup_name}); " ',
- '${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid TO tract_id;"
- ${psql} -c "SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
+ '${psql} -c "ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid IF EXISTS TO tract_id;
+ ALTER TABLE ${staging_schema}.${state_abbrev}_${table_name} RENAME geoid10 IF EXISTS TO tract_id;
+ SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_gist ON ${data_schema}.${state_abbrev}_${lookup_name} USING gist(the_geom);"
${psql} -c "VACUUM ANALYZE ${data_schema}.${state_abbrev}_${lookup_name};"
${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"', ARRAY['gid']);
@@ -291,7 +350,7 @@ ${psql} -c "CREATE INDEX ${data_schema}_${state_abbrev}_${lookup_name}_the_geom_
${psql} -c "vacuum analyze ${data_schema}.${state_abbrev}_${lookup_name};"', '{gid, uatyp10, uatype, suffix1ce}'::text[]);
INSERT INTO loader_lookuptables(process_order, lookup_name, table_name, load, level_county, level_state, single_geom_mode, insert_mode, pre_load_process, post_load_process, columns_exclude )
-VALUES(11, 'tabblock20', 'tabblock20', false, false, true,false, 'c',
+VALUES(11, 'tabblock20', 'tabblock20', true, false, true,false, 'c',
'${psql} -c "CREATE TABLE ${data_schema}.${state_abbrev}_${lookup_name}(CONSTRAINT pk_${state_abbrev}_${lookup_name} PRIMARY KEY (geoid)) INHERITS(tiger.${lookup_name});" ',
'${psql} -c "SELECT loader_load_staged_data(lower(''${state_abbrev}_${table_name}''), lower(''${state_abbrev}_${lookup_name}'')); "
${psql} -c "ALTER TABLE ${data_schema}.${state_abbrev}_${lookup_name} ADD CONSTRAINT chk_statefp CHECK (statefp = ''${state_fips}'');"
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';
-----------------------------------------------------------------------
Summary of changes:
NEWS | 4 +-
.../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 | 91 ++++++++++++++++++----
extras/tiger_geocoder/tiger_loader_2021.sql | 82 ++++++++++++++++---
12 files changed, 585 insertions(+), 125 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list